萬盛學電腦網

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

MySQL 時間戳與日期時間互轉:UNIX_TIMESTAMP、FROM_UNIXTIME 完全指南

核心函數對照

函數方向語法示例
UNIX_TIMESTAMP()日期時間 → 時間戳(秒)UNIX_TIMESTAMP([date])UNIX_TIMESTAMP('2023-11-19 12:00:00') → 1700366400
FROM_UNIXTIME()時間戳 → 日期時間/字符串FROM_UNIXTIME(unix_ts[, format])FROM_UNIXTIME(1700366400, '%Y-%m-%d %H:%i:%s') → '2023-11-19 12:00:00'

基本用法

-- 當前時間戳
SELECT UNIX_TIMESTAMP();           -- 1700366400
SELECT UNIX_TIMESTAMP(NOW());      -- 同效果

-- 指定日期轉時間戳
SELECT UNIX_TIMESTAMP('2023-11-19 00:00:00');  -- 1700323200

-- 時間戳轉日期時間(默認格式)
SELECT FROM_UNIXTIME(1700366400);  -- 2023-11-19 12:00:00

-- 自定義格式輸出
SELECT FROM_UNIXTIME(1700366400, '%Y年%m月%d日 %H點%i分');
-- 2023年11月19日 12點00分

毫秒級時間戳處理(Java/JS 常見 13 位)

應用層常用毫秒時間戳(13 位),MySQL 函數僅支援秒級(10 位),需先截斷:

-- 錯誤示範:直接傳 13 位返回 NULL
SELECT FROM_UNIXTIME(1700366400123);  -- NULL

-- 正確:除以 1000 取整
SELECT FROM_UNIXTIME(1700366400123 / 1000);  
-- 或
SELECT FROM_UNIXTIME(FLOOR(1700366400123 / 1000));

時區陷阱與最佳實踐

問題

  • UNIX_TIMESTAMP() 返回的是 UTC 時間戳(無時區)。
  • FROM_UNIXTIME() 依賴會話 time_zone 變量轉換為本地時間顯示。
  • 若應用寫入時區 A,讀取時區 B,顯示時間會偏移。

解決方案

  1. 統一存儲 UTC:應用層統一將業務時間轉 UTC 存入 TIMESTAMP 或 BIGINT。
  2. 會話設置時區:連接池初始化執行 SET time_zone = '+08:00'; 或 SET time_zone = 'Asia/Shanghai';。
  3. 避免使用 DATETIME 存儲帶時區信息:DATETIME 無時區感知,TIMESTAMP 自動按會話時區轉換(但 2038 年問題)。
  4. 推薦:BIGINT 存儲毫秒級 UTC 時間戳,應用層負責格式化顯示,數據庫層純數值存儲,零時區困擾。

格式化符號速查(同 DATE_FORMAT)

%Y 4位年  %m 2位月  %d 2位日
%H 24時  %i 分  %s 秒
%W 星期全稱  %a 星期縮寫
%M 月全稱  %b 月縮寫
%j 年內天數  %U/%u 週數
%% 字面%

進階:日期運算結合時間戳

-- 當前時間戳減 1 天
SELECT UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY));

-- 時間戳加 3 小時
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP() + 3*3600);

-- 批量更新:將 datetime_col 轉為時間戳存入 bigint_col
UPDATE tbl SET bigint_col = UNIX_TIMESTAMP(datetime_col);
copyright © 萬盛學電腦網 all rights reserved