萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 日期與 UNIX 時間戳互轉完整指南

核心函數對照

方向函數語法範例
時間戳 → 日期FROM_UNIXTIME()FROM_UNIXTIME(unix_ts, format)SELECT FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%s');
日期 → 時間戳UNIX_TIMESTAMP()UNIX_TIMESTAMP([date])SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00');

FROM_UNIXTIME 格式化符號完整表

符號含義範例
%Y4位年2024
%m月(01-12)05
%d日(01-31)20
%H時24制(00-23)14
%i分(00-59)30
%s秒(00-59)45
%T時間(HH:MM:SS)14:30:45
%r時間12制(AM/PM)02:30:45 PM
%W星期全寫Monday
%w星期數(0=Sun)1
%j年間天數(001-366)141
%%字面%%

實戰範例

1. 當前時間雙向轉換

SELECT 
  UNIX_TIMESTAMP() AS ts,
  FROM_UNIXTIME(UNIX_TIMESTAMP()) AS dt,
  FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s') AS dt_fmt;

2. 指定日期轉時間戳

SELECT UNIX_TIMESTAMP('2024-05-20 14:30:00');  -- 1716186600

3. 時間戳轉指定格式

SELECT FROM_UNIXTIME(1716186600, '%Y年%m月%d日 %H時%i分'); -- 2024年05月20日 14時30分

4. 查詢當天資料(高效寫法)

-- 避免函數導致索引失效
SELECT * FROM message
WHERE chattime >= UNIX_TIMESTAMP(CURDATE())
  AND chattime < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY);

5. 時區處理

  • UNIX_TIMESTAMP 回傳 **UTC 時間戳**(無時區)。
  • FROM_UNIXTIME 輸出 **Session 時區** 時間(受 time_zone 影響)。
  • 跨時區應用建議:存儲 UTC 時間戳(BIGINT),應用層按用戶時區格式化顯示。

常見坑點

坑點現象解決
32 位系統 2038 年問題有符號 32 位整數上限 2038-01-19使用 BIGINT(64位) 存儲,MySQL 內部已 64 位
時區不一致導致日期偏移Session time_zone 不同統一設定 SET time_zone = '+08:00' 或應用層處理
WHERE 使用 FROM_UNIXTIME 導致全掃函數作用在欄位上改用範圍查詢(見範例 4)
毫秒/微秒精度缺失UNIX_TIMESTAMP 只精確到秒MySQL 5.6.4+ 支援 UNIX_TIMESTAMP(3) 返回毫秒,或存 DATETIME(3)

總結

UNIX_TIMESTAMP / FROM_UNIXTIME 是 MySQL 日期與時間戳互轉的核心組合。**存儲建議用 BIGINT 存 UTC 秒級時間戳**,查詢用範圍條件利用索引,顯示時再 FROM_UNIXTIME 格式化。時區統一在 Session 或應用層處理,避免「夏令時/跨時區」Bug。

copyright © 萬盛學電腦網 all rights reserved