MySQL 時間戳轉換完全指南：FROM_UNIXTIME 與 Excel 互轉技巧

前言

時間戳 (Unix Timestamp) 作為系統間通用的時間表示，因其跨平台、無時區歧義、易於排序比較等特性，廣泛應用於資料庫設計。但人類閱讀習慣為「年-月-日 時:分:秒」格式，轉換需求極高。本文詳細說明 MySQL 內建函數 FROM_UNIXTIME 用法、格式化符號對照表，並補充 Excel 雙向轉換公式。

一、MySQL 核心函數：FROM_UNIXTIME

語法結構

FROM_UNIXTIME(unix_timestamp[, format])

• unix_timestamp：10 位秒級或 13 位毫秒級整數 (需除以 1000)。
• format：可選格式化字串，若省略預設輸出 YYYY-MM-DD HH:MM:SS。

常用範例

需求	SQL 範例	輸出示例
標準日期時間	SELECT FROM_UNIXTIME(1609459200);	2021-01-01 00:00:00
僅日期	SELECT FROM_UNIXTIME(1609459200, '%Y-%m-%d');	2021-01-01
中文格式	SELECT FROM_UNIXTIME(1609459200, '%Y年%m月%d日 %H:%i');	2021年01月01日 00:00
12 小時制	SELECT FROM_UNIXTIME(1609459200, '%r');	12:00:00 AM
處理毫秒級	SELECT FROM_UNIXTIME(1609459200123/1000, '%Y-%m-%d %H:%i:%s.%f');	2021-01-01 00:00:00.123000

二、完整格式化符號對照表

符號	含義	範例
%Y	四位數年份	2024
%y	兩位數年份	24
%m	月份 (01-12)	01
%c	月份 (1-12)	1
%b	縮寫月名	Jan
%M	完整月名	January
%d	月內天數 (01-31)	01
%e	月內天數 (1-31)	1
%H	小時 24制 (00-23)	00
%h/%I	小時 12制 (01-12)	12
%i	分鐘 (00-59)	00
%s/%S	秒 (00-59)	00
%f	微秒 (6 位)	123456
%p	AM/PM	AM
%W	完整星期名	Sunday
%a	縮寫星期名	Sun
%w	星期索引 (0=Sun)	0
%j	年內天數 (001-366)	001
%%	字面 % 符號	%

三、資料庫字段批量轉換實戰

場景

表 logs 中 create_time INT(11) 儲存時間戳，需查詢為可讀格式：

SELECT id,
       FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%s') AS create_datetime
FROM logs
WHERE create_time > UNIX_TIMESTAMP('2024-01-01');

建立視圖簡化應用層

CREATE VIEW v_logs_readable AS
SELECT *,
       FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%s') AS create_dt,
       FROM_UNIXTIME(update_time, '%Y-%m-%d %H:%i:%s') AS update_dt
FROM logs;

四、Excel 雙向轉換公式

1. Unix Timestamp → Excel 日期時間

Excel 以 1900-01-00 為基準 (序數 1)，Unix 以 1970-01-01 UTC 為基準，時差 25569 天，另需考慮時區偏移 (UTC+8 = 8/24 天)：

= (A1 / 86400) + 25569 + 8/24
-- 格式化儲存格為「yyyy-mm-dd hh:mm:ss」

> **說明**：原文公式 =(xxxxxxxxxx+8*3600)/86400+70*365+19 近似但不精確 (未考慮閏年)，建議使用標準 25569 天常數。

2. Excel 日期時間 → Unix Timestamp

= (A1 - 25569 - 8/24) * 86400

3. 快捷操作技巧

• 整欄轉換：雙擊填充柄向下複製公式。
• 批量貼上數值：複製結果 → 選擇性貼上 → 數值，移除公式依賴。
• Power Query (資料 → 取得資料) 可視化轉換，適合超大資料集。

五、反向函數：UNIX_TIMESTAMP

將日期時間轉為時間戳，支援時區感知：

SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00');        -- 1704038400 (會話時區)
SELECT UNIX_TIMESTAMP(CONVERT_TZ('2024-01-01 00:00:00', '+08:00', '+00:00')); -- 強制 UTC

六、避坑指南

1. 年份 2038 問題：32 位有符號整數上限 2147483647 (2038-01-19)，新系統請用 BIGINT 或 DATETIME(6)。
2. 時區一致性：資料庫、應用、JVM、容器統一設為 UTC，僅在展示層轉換使用者時區。
3. 毫秒精度：MySQL 5.6.4+ 支援 DATETIME(6)/TIMESTAMP(6) 微秒精度，避免浮點運算丟失精度。
4. FROM_UNIXTIME 受 time_zone 系統變數影響，跨時區查詢建議顯式 CONVERT_TZ。

總結

掌握 FROM_UNIXTIME 格式化符號與 Excel 互轉公式，能顯著提升資料清洗、報表製作、跨系統對帳效率。建議將常用格式封裝為資料庫函數或應用層工具類，統一團隊時間處理規範。