您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 日期時間函數完全指南：常用操作與實戰範例

編輯：MySQL 綜合教學

前言

日期時間處理是業務開發的高頻需求。MySQL 提供豐富的內建函數，本文系統整理獲取當前時間、日期運算、格式化、轉換、元件提取等核心函數，並附實戰範例。

一、獲取當前日期時間

函數	回傳值範例	說明
`NOW()` / `SYSDATE()` / `CURRENT_TIMESTAMP()`	2024-01-15 14:30:45	當前日期時間（DATETIME）
`CURDATE()` / `CURRENT_DATE()`	2024-01-15	當前日期（DATE）
`CURTIME()` / `CURRENT_TIME()`	14:30:45	當前時間（TIME）
`UNIX_TIMESTAMP()`	1705300245	Unix 時間戳（秒）

二、日期元件提取


SELECT 
  YEAR('2024-01-15')        AS year,        -- 2024
  MONTH('2024-01-15')       AS month,       -- 1
  DAY('2024-01-15')         AS day,         -- 15
  HOUR('14:30:45')          AS hour,        -- 14
  MINUTE('14:30:45')        AS minute,      -- 30
  SECOND('14:30:45')        AS second,      -- 45
  DAYOFWEEK('2024-01-15')   AS dow,         -- 2 (1=週日)
  WEEKDAY('2024-01-15')     AS wd,          -- 0 (0=週一)
  DAYOFYEAR('2024-01-15')   AS doy,         -- 15
  QUARTER('2024-01-15')     AS quarter,     -- 1
  WEEK('2024-01-15', 1)     AS week;        -- 3 (週一為週首)

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

核心語法


DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)
-- 同義詞：ADDDATE / SUBDATE
-- 運算符簡寫：date + INTERVAL expr unit / date - INTERVAL expr unit

常用 INTERVAL 單位

單位	expr 格式	範例
SECOND / MINUTE / HOUR / DAY / MONTH / YEAR	數值	INTERVAL 1 DAY
MINUTE_SECOND	"分:秒"	INTERVAL '30:15' MINUTE_SECOND
HOUR_MINUTE	"時:分"	INTERVAL '2:30' HOUR_MINUTE
DAY_HOUR	"天時"	INTERVAL '1 12' DAY_HOUR
YEAR_MONTH	"年-月"	INTERVAL '1-6' YEAR_MONTH
DAY_SECOND	"天時:分:秒"	INTERVAL '1 12:30:45' DAY_SECOND

實戰範例


-- 本月第一天
SELECT DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE())+1 DAY);
-- 或
SELECT DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY);

-- 本月最後一天
SELECT LAST_DAY(CURDATE());

-- 下個月第一天
SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY);

-- 當月天數
SELECT DATEDIFF(
  DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE())+1 DAY),
  DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY)
);

-- 30 天前
SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 1 年後
SELECT DATE_ADD('2024-01-15', INTERVAL 1 YEAR);

四、日期差值計算


-- 兩日期相差天數 (date1 - date2)
SELECT DATEDIFF('2024-01-15', '2024-01-01');  -- 14

-- 兩時間相差秒數
SELECT TIMESTAMPDIFF(SECOND, '2024-01-15 10:00:00', '2024-01-15 12:00:00'); -- 7200

-- 兩月份相差月數
SELECT PERIOD_DIFF(202401, 202306); -- 7

五、格式化與解析

DATE_FORMAT / TIME_FORMAT


SELECT 
  DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS datetime_std,  -- 2024-01-15 14:30:45
  DATE_FORMAT(NOW(), '%Y年%m月%d日 %H時%i分') AS cn_format,  -- 2024年01月15日 14時30分
  DATE_FORMAT(NOW(), '%W, %M %D, %Y') AS en_format,         -- Monday, January 15th, 2024
  TIME_FORMAT('14:30:45', '%h:%i %p') AS time_12h;          -- 02:30 PM

常用格式符號

符號	含義	範例
%Y	4 位年份	2024
%m	2 位月份	01
%d	2 位日期	15
%H	24制小時	14
%h	12制小時	02
%i	分鐘	30
%s	秒	45
%W	星期全名	Monday
%M	月份全名	January

STR_TO_DATE 字串轉日期


SELECT STR_TO_DATE('2024-01-15 14:30:45', '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('15/01/2024', '%d/%m/%Y');

六、Unix 時間戳轉換


-- 日期時間 -> 時間戳
SELECT UNIX_TIMESTAMP('2024-01-15 14:30:45');  -- 1705300245

-- 時間戳 -> 日期時間
SELECT FROM_UNIXTIME(1705300245);              -- 2024-01-15 14:30:45
SELECT FROM_UNIXTIME(1705300245, '%Y-%m-%d');  -- 2024-01-15

七、秒數與時間互轉


SELECT SEC_TO_TIME(3661);   -- '01:01:01'
SELECT TIME_TO_SEC('01:01:01'); -- 3661

八、實戰場景：招募倒計時查詢


-- 查詢下一個生效的時間控制記錄
SELECT `time` 
FROM app_web_setting_timecontrol 
WHERE `time` >= NOW() 
  AND `time` < DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE())+1 DAY + INTERVAL 1 MONTH)
ORDER BY `time` ASC LIMIT 1;

九、效能提示

避免在 WHERE 條件中對欄位使用函數（如 WHERE DATE(create_time) = CURDATE()），改用範圍查詢：WHERE create_time >= CURDATE() AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY) 以利用索引。
儲存時間戳優先使用 TIMESTAMP (4 bytes, 1970-2038) 或 DATETIME (8 bytes, 1000-9999)，避免用 INT 儲存 Unix 時間戳導致時區混淆。
統一應用層、資料庫層、連線層時區設定（SET time_zone = '+08:00'）。