兩大核心函數：互為逆操作

函數	方向	輸入	輸出	典型用途
FROM_UNIXTIME(unix_ts, format)	時間戳 → 日期字串	整數秒 (BIGINT)	格式化字串	顯示、報表、WHERE 篩選
UNIX_TIMESTAMP(date)	日期 → 時間戳	DATE/DATETIME/TIMESTAMP/字串	整數秒 (BIGINT)	存儲、比較、跨語言交互

FROM_UNIXTIME() 格式化修飾符完整表

修飾符	含義	範例輸出
%Y	4 位年份	2024
%y	2 位年份	24
%m	月份 (01-12)	01
%c	月份 (1-12)	1
%d	日期 (01-31)	15
%e	日期 (1-31)	15
%H	小時 24 制 (00-23)	14
%k	小時 24 制 (0-23)	14
%h/%I	小時 12 制 (01-12)	02
%l	小時 12 制 (1-12)	2
%i	分鐘 (00-59)	30
%S/%s	秒 (00-59)	45
%T	時間 24 制 (HH:MM:SS)	14:30:45
%r	時間 12 制 (HH:MM:SS AM/PM)	02:30:45 PM
%p	AM/PM	PM
%W	星期全名	Monday
%a	星期縮寫	Mon
%w	星期數 (0=Sun..6=Sat)	1
%M	月份全名	January
%b	月份縮寫	Jan
%j	年間天數 (001-366)	015
%U	周數 (00-53, Sun 為週首)	03
%u	周數 (00-53, Mon 為週首)	03
%%	字面 %	%

實戰範例

-- 1. 基礎轉換
SELECT FROM_UNIXTIME(1705312200);                    -- 2024-01-15 14:30:00 (會話時區)
SELECT FROM_UNIXTIME(1705312200, '%Y-%m-%d %H:%i:%S'); -- 2024-01-15 14:30:00
SELECT FROM_UNIXTIME(1705312200, '%Y年%m月%d日 %H時%i分'); -- 2024年01月15日 14時30分

-- 2. 表欄位轉換 (假設 created 存 INT 時間戳)
SELECT id, FROM_UNIXTIME(created, '%Y-%m-%d') AS create_date FROM users;

-- 3. 反向：日期存為時間戳 (節省空間、跨時區無歧義)
UPDATE users SET created_ts = UNIX_TIMESTAMP(created_dt);
-- 或插入時
INSERT INTO logs (ts) VALUES (UNIX_TIMESTAMP());  -- 當前秒級時間戳
INSERT INTO logs (ts) VALUES (UNIX_TIMESTAMP('2024-01-15 14:30:00'));

時區陷阱：最易踩的坑

現象

SET time_zone = '+08:00';
SELECT FROM_UNIXTIME(0);        -- 1970-01-01 08:00:00
SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(0);        -- 1970-01-01 00:00:00

FROM_UNIXTIME() **受會話時區影響**！同一時間戳，不同時區顯示不同。

最佳實踐

1. 存儲層統一 UTC：應用寫入 UNIX_TIMESTAMP() (隱含 UTC) 或 UTC_TIMESTAMP()。
2. 顯示層按需轉換：查詢時 SET time_zone = 'Asia/Shanghai' 或應用層格式化。
3. 避免 DATETIME 存儲本地時間：跨時區/夏令時會產生歧義。優先用 TIMESTAMP (自動 UTC↔會話時區) 或 BIGINT 存時間戳。

進階：毫秒/微秒精度 (MySQL 5.6.4+)

-- 微秒精度時間戳 (需 BIGINT 或 DOUBLE 存儲)
SELECT UNIX_TIMESTAMP(NOW(6));        -- 1705312200.123456
SELECT FROM_UNIXTIME(1705312200.123456, '%Y-%m-%d %H:%i:%s.%f'); -- 2024-01-15 14:30:00.123456

-- DATETIME(6)/TIMESTAMP(6) 列支援微秒
CREATE TABLE events (ts DATETIME(6) NOT NULL);
INSERT INTO events VALUES (NOW(6));

效能與索引建議

• WHERE created_ts >= UNIX_TIMESTAMP('2024-01-01') 可走 created_ts 索引。
• WHERE FROM_UNIXTIME(created_ts) >= '2024-01-01' **無法走索引** (函數包裹列)。
• 大表時間範圍查詢，**必須在時間戳列上直接比較整數**。

PHP / Java / Python 互操作

// PHP
$ts = time();                           // 秒
$ts = microtime(true);                  // 秒.微秒
$dt = date('Y-m-d H:i:s', $ts);         // 格式化
$ts = strtotime('2024-01-15 14:30:00'); // 解析

// Java (Instant/Long)
long ts = Instant.now().getEpochSecond();
String dt = Instant.ofEpochSecond(ts).atZone(ZoneId.of("Asia/Shanghai")).format(DateTimeFormatter.ISO_LOCAL_DATE_TIME);

// Python
import time, datetime
ts = int(time.time())
dt = datetime.datetime.fromtimestamp(ts, tz=datetime.timezone.utc)

掌握時間戳與日期的雙向轉換、格式化、時區處理，讓時間數據在系統間流轉無阻。