[Excel] Convert Date and UNIX time

Posted by – June 2, 2011

這個問題是最近工作上碰到的,把 SQL 裡的 time 欄位輸出,整理成 Excel 給 PM,不過報表裡的時間都是 UNIX time 格式,除非有超強的心算,不然一般人是沒辦法直接看著 UNIX time 換成算時間日期的。

UNIX time 是以 1970 年 1 月 1 日零點 (UTC) 為起點,每過一秒加 1 的時間計算方式,更詳細的介紹請參考維基百科。根據這個公式,本文撰文時刻是 1306971424

Excel 內部的時間計算方式和 UNIX time 有異曲同工之妙,不過起點是 1900 年 1 月 1 日,日期是整數,時間是小數,詳情請參考這篇如何在 Excel 中使用日期與時間。翻遍 Excel 2010 的 Help 也找不到和 UNIX 或 POSIX 相關的 function,看來只能土法轉換了。

轉換公式 =Unixtime / 86400 + 25569

86400 是一天的秒數,UNIX time 1 代表 1 秒,而 Excel 的 1 代表 1 天,因此先除以 86400 換算成天數,再將 1900/1/1 到 1970/1/1 的 25569 天加上去。如此可以取得 Excel 的時間。不過這個算法是 UTC 時間,台灣時間是 UTC +8,因此還要把八小時換算成小數點,即 8/24。

台灣時間轉換公式 =Unixtime / 86400 + 25569 + 8/24

實際操作如下:

1. 輸入欄位和公式

2. 完成後會得到奇怪的數字,這就是 Excel 內部的時間代碼了

3. 按右鍵 -> 儲存格格式

4. 選擇日期

5. 按右下 OK 就大工告成了。

 

Unix time 和一般時間轉換是很常用的東西,幾乎所有用到 Unix time 的軟體、程式語言都有直接轉換的工具,唯獨 Excel 沒有,真是非常怪奇的事情。不過資料是從 MySQL 出來的,當然也可以直接用 MySQL 轉換,指令是 FROM_UNIXTIME(),範例如下:

SELECT FROM_UNIXTIME(time) as time FROM table

但是轉出來的是 UTC 時間,台灣時間要 + 8 * 60 * 60。

SELECT FROM_UNIXTIME(time + 8 * 60 * 60) as time FROM table

轉換後的結果會像這樣,當然另外可以設定格式。

2011-06-02 15:37:04

更多關於 MySQL 時間日期的指令請參考 MySQL Manual: Date and Time Functions

References:

Leave a Reply

Your email address will not be published. Required fields are marked *