您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 日期時間函數實戰大全：運算、格式化、轉換與時區處理

編輯：MySQL 資料庫相關知識

MySQL 提供豐富的日期時間函數族群，掌握核心用法能大幅簡化業務邏輯。本文整理 **運算**、**格式化**、**轉換**、**元件擷取**、**Unix 時間戳** 五大類常用函數，並針對「日期加秒數」常見錯誤給出正確寫法。

一、日期運算：DATE_ADD / DATE_SUB / INTERVAL

-- 正確：日期加 62 秒
UPDATE account_tbl 
SET acct_timestamp = begin_timestamp + INTERVAL 62 SECOND,
    acct_timeduration = 62
WHERE acct_timeduration = 10800;

-- 錯誤：begin_timestamp + 62 → 視為數值相加，產生 '0000-00-00 00:00:00'
-- 原因：MySQL 嘗試將 datetime 轉數值再加 62，再轉回 datetime，溢位歸零

-- 完整 INTERVAL 單位支援
DATE_ADD(dt, INTERVAL 1 SECOND/MINUTE/HOUR/DAY/WEEK/MONTH/QUARTER/YEAR)
DATE_ADD(dt, INTERVAL '1:30' MINUTE_SECOND)   -- 1分30秒
DATE_ADD(dt, INTERVAL '2 12:30' DAY_HOUR)    -- 2天12小時30分
DATE_SUB(dt, INTERVAL '1 2:30:10' DAY_SECOND) -- 減 1天2時30分10秒

二、日期格式化：DATE_FORMAT / TIME_FORMAT

DATE_FORMAT('2024-03-15 14:30:05', '%Y-%m-%d %H:%i:%s')  -- 2024-03-15 14:30:05
DATE_FORMAT(dt, '%Y%m%d')        -- 20240315 (適合檔名/分區)
DATE_FORMAT(dt, '%W %M %d %Y')   -- Friday March 15 2024
DATE_FORMAT(dt, '%r')            -- 02:30:05 PM (12小時制)
DATE_FORMAT(dt, '%T')            -- 14:30:05 (24小時制)

-- 常用格式符對照
%Y 4位年  %y 2位年  %m 月(01-12)  %c 月(1-12)  %d 日(01-31)  %e 日(1-31)
%H 時(00-23) %k 時(0-23) %h/%I 時(01-12) %i 分 %s/%S 秒 %p AM/PM
%W 週名 %a 縮寫週名 %M 月名 %b 縮寫月名 %j 年內天數(001-366)
%U 週數(週日首) %u 週數(週一首) %% 字面%

三、元件擷取：YEAR/MONTH/DAY/HOUR/MINUTE/SECOND 等

YEAR(dt), QUARTER(dt), MONTH(dt), DAY(dt), DAYOFMONTH(dt), DAYOFWEEK(dt) (1=週日), WEEKDAY(dt) (0=週一)
HOUR(dt), MINUTE(dt), SECOND(dt)
MICROSECOND(dt) -- 微秒 (6位)
-- 組合擷取
EXTRACT(YEAR_MONTH FROM dt)     -- 202403
EXTRACT(DAY_HOUR FROM dt)       -- 1514
EXTRACT(MINUTE_SECOND FROM dt)  -- 3005

四、時間戳轉換：UNIX_TIMESTAMP / FROM_UNIXTIME

UNIX_TIMESTAMP()                 -- 當前 Unix 秒數
UNIX_TIMESTAMP('2024-03-15 14:30:05') -- 指定時間轉 Unix 秒
FROM_UNIXTIME(1710493805)        -- 秒數轉 datetime
FROM_UNIXTIME(ts, '%Y-%m-%d')    -- 格式化輸出

-- 注意：TIMESTAMP 欄位自動儲存 UTC，讀取時轉換為 session time_zone

五、當前時間獲取

NOW() / SYSDATE() / CURRENT_TIMESTAMP() -- 日期時間 (同一語句內 NOW 固定，SYSDATE 即時)
CURDATE() / CURRENT_DATE()              -- 日期
CURTIME() / CURRENT_TIME()              -- 時間

六、天數轉換：TO_DAYS / FROM_DAYS (不支援 1582 前格里曆)

TO_DAYS('2024-03-15')   -- 738567 (從西元 0 年起算天數)
FROM_DAYS(738567)       -- '2024-03-15'

七、秒數與 TIME 互轉

TIME_TO_SEC('14:30:05')  -- 52205
SEC_TO_TIME(52205)       -- '14:30:05'

八、實戰陷阱與最佳實務

場景	錯誤寫法	正確寫法	原因
日期加秒/分/時	`dt + 62`	`dt + INTERVAL 62 SECOND`	數值運算導致 datetime 轉數值溢位
WHERE 日期範圍	`DATE(create_time) = '2024-03-15'`	`create_time >= '2024-03-15' AND create_time < '2024-03-16'`	函數導致索引失效，範圍查詢可用索引
本月資料	`MONTH(dt)=MONTH(NOW())`	`dt >= DATE_FORMAT(NOW(),'%Y-%m-01') AND dt < DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-01'), INTERVAL 1 MONTH)`	同上，利用索引範圍掃描
時區一致性	應用層存本地時間	資料庫存 UTC (TIMESTAMP/UTC DATETIME)，應用層按用戶時區顯示	避免夏令時/跨時區混亂

九、時區設定

-- 會話級
SET time_zone = '+08:00';  -- 或 'Asia/Taipei' (需載入時區表 mysql_tzinfo_to_sql)
-- 全域級 (my.cnf)
[mysqld]
default-time-zone = '+00:00'  -- 建議伺服器統一 UTC