MySQL 函式 – 日期時間函式教學

簡介

在資料庫的日常開發與維運工作中，日期與時間的處理往往是最常見且最容易出錯的部分。不論是記錄交易時間、計算訂單逾期、產生報表統計，或是做資料分割、分區，都離不開 MySQL 提供的日期時間函式。掌握這些內建函式，能讓我們在 SQL 語句裡直接完成時間的轉換、加減與格式化，減少程式碼複雜度、提升效能，同時避免因時區或格式不一致而產生的錯誤。

本篇文章針對 MySQL 日期時間函式 進行系統性的說明，從基本概念、常用函式到實務應用與最佳實踐，適合 初學者到中級開發者 快速上手並在專案中落地。

---

核心概念

1. 日期時間資料型別概覽

MySQL 內建四種與時間相關的資料型別：

型別	說明	範例值
DATE	只含日期（年‑月‑日）	2024-09-01
TIME	只含時間（時:分:秒）	13:45:30
DATETIME	結合日期與時間（不含時區）	2024-09-01 13:45:30
TIMESTAMP	結合日期與時間，會自動根據伺服器時區轉換	2024-09-01 05:45:30（UTC）

TIP：若需要自動處理時區變換，建議使用 TIMESTAMP；若僅需保存「原始」時間資訊，則使用 DATETIME。

---

2. 常用的日期時間函式

以下列出在日常開發最常用的 5 大函式，並以簡短說明與範例展示其基本用法。

2.1 NOW() / CURRENT_TIMESTAMP

取得目前的日期與時間（DATETIME），等效於 CURRENT_TIMESTAMP。

SELECT NOW();                 -- 2024-09-25 14:32:10
SELECT CURRENT_TIMESTAMP;    -- 同上

2.2 CURDATE() / CURRENT_DATE

取得目前的日期（DATE），不含時間。

SELECT CURDATE();            -- 2024-09-25
SELECT CURRENT_DATE;         -- 同上

2.3 DATE_ADD() / DATE_SUB()

在指定的日期上加或減時間間隔，支援 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 等單位。

-- 加 7 天
SELECT DATE_ADD('2024-09-01', INTERVAL 7 DAY);   -- 2024-09-08

-- 減 3 個月
SELECT DATE_SUB('2024-09-01', INTERVAL 3 MONTH);-- 2024-06-01

2.4 DATEDIFF()

計算兩個 DATE 或 DATETIME 之間相差的天數（只返回整數天）。

SELECT DATEDIFF('2024-09-30', '2024-09-01');    -- 29

2.5 DATE_FORMAT()

依照自訂格式輸出日期時間字串，常用於報表或 UI 顯示。

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s') AS formatted; 
-- 2024/09/25 14:32:10

常見格式代碼

• %Y：四位年份
• %m：兩位月份
• %d：兩位日期
• %H：24 小時制時
• %i：分鐘
• %s：秒

---

3. 程式碼範例

以下提供 5 個實務上常見且具代表性的範例，每個範例皆附上註解說明。

3.1 計算訂單逾期天數

-- 假設有 order_tbl(order_id, order_date, due_date)
SELECT 
    order_id,
    order_date,
    due_date,
    DATEDIFF(CURDATE(), due_date) AS days_overdue
FROM order_tbl
WHERE due_date < CURDATE();   -- 只挑選已逾期的訂單

說明：DATEDIFF 直接算出今天與到期日的天數差，正值代表已逾期。

---

3.2 產生每月第一天與最後一天

-- 取得 2024 年 5 月的月初與月末
SET @year_month = '2024-05';

SELECT 
    DATE_FORMAT(@year_month, '%Y-%m-01') AS month_start,
    LAST_DAY(@year_month) AS month_end;

說明：LAST_DAY() 會回傳給定日期所在月份的最後一天；配合 DATE_FORMAT 可快速得到月初。

---

3.3 以週期性方式產生時間序列（每 15 分鐘）

-- 建立一個臨時表，用於產生 24 小時內每 15 分鐘的時間點
CREATE TEMPORARY TABLE minutes_seq (ts DATETIME);

INSERT INTO minutes_seq (ts)
SELECT 
    DATE_ADD('2024-09-25 00:00:00', INTERVAL (n*15) MINUTE) AS ts
FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
    UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
    UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
    UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
    UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27
    UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31
    UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35
    UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39
    UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43
    UNION ALL SELECT 44 UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47
    UNION ALL SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50 UNION ALL SELECT 51
    UNION ALL SELECT 52 UNION ALL SELECT 53 UNION ALL SELECT 54 UNION ALL SELECT 55
    UNION ALL SELECT 56 UNION ALL SELECT 57 UNION ALL SELECT 58 UNION ALL SELECT 59
) AS numbers;

說明：此技巧利用 DATE_ADD 與「數字列」產生固定間隔的時間點，常用於排程或圖表的時間軸補齊。

---

3.4 轉換時區（UTC ↔ 台北）

-- 假設資料表 event_tbl(event_id, event_time_utc DATETIME)
SELECT 
    event_id,
    event_time_utc,
    CONVERT_TZ(event_time_utc, '+00:00', '+08:00') AS event_time_taipei
FROM event_tbl;

說明：CONVERT_TZ() 需要事先載入時區資訊（mysql_tzinfo_to_sql），才能正確轉換。+08:00 為台北時區。

---

3.5 以「年月」分群統計銷售金額

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(amount) AS total_sales
FROM sales_tbl
GROUP BY month
ORDER BY month DESC;

說明：利用 DATE_FORMAT 把日期「擷取」成 YYYY-MM，再以此欄位做 GROUP BY，即可快速得到月度匯總。

---

常見陷阱與最佳實踐

陷阱	可能的結果	解決方式 / 最佳實踐
使用 DATETIME 存儲 UTC 時間卻忘記時區轉換	報表顯示時差 8 小時（台北）	建議統一使用 TIMESTAMP，或在存取時明確使用 CONVERT_TZ()
DATEDIFF 只回傳天數，忽略時分秒	兩個時間相差 23 小時仍被視為 0 天	若需精確到秒，改用 TIMESTAMPDIFF(SECOND, a, b)
LAST_DAY() 於閏年 2 月	正確回傳 29 日，但若使用 DATE_ADD 加 1 月可能得到 3 月 1 日	使用 DATE_SUB(LAST_DAY(date), INTERVAL DAY(LAST_DAY(date))-1 DAY) 取得月初
時區表未載入 (CONVERT_TZ 回傳 NULL)	時區轉換失敗，導致 NULL 結果	執行 `mysql_tzinfo_to_sql /usr/share/zoneinfo
格式化字串錯誤 (%i 與 %I 混用)	產生不符合預期的時間字串	參照官方文件，僅使用支援的格式代碼，%i 為分鐘，%I 為 12 小時制的時

最佳實踐：

1. 統一時區：在系統層面決定使用 UTC 或本地時區，並在所有 INSERT/UPDATE 前統一轉換。
2. 盡量使用內建函式：避免在程式碼中自行計算日期差，直接利用 DATE_ADD、TIMESTAMPDIFF 等函式，保證正確性與效能。
3. 索引與分區：若大量查詢以日期為條件，建議在 DATE/TIMESTAMP 欄位上建立索引，或以日期分區（PARTITION BY RANGE）提升查詢速度。
4. 避免隱式類型轉換：在比較或運算時，確保欄位與常數的型別一致，例如 WHERE order_date = '2024-09-25'（字串會自動轉為 DATE），但多次運算時建議使用 CAST(... AS DATE) 明確轉型。

---

實際應用場景

場景	需求	典型函式組合
訂單逾期提醒	每天找出逾期 3 天以上的訂單，寄送 Email	CURDATE(), DATEDIFF(), DATE_SUB()
每日活躍使用者統計	計算前一天的活躍使用者數量	DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_FORMAT()
分區表設計	按月份儲存大量日誌資料，提升查詢效能	PARTITION BY RANGE (TO_DAYS(log_time)), LAST_DAY()
跨時區會議排程	把 UTC 時間轉為各參與者的本地時間	CONVERT_TZ()
報表顯示	把時間顯示為「2024/09/25 14:32」的格式	DATE_FORMAT()

實務小技巧：在寫報表 SQL 時，盡量把時間格式化的工作留給資料庫（使用 DATE_FORMAT），而不是在程式碼裡再做一次字串處理，這樣可以減少資料傳輸量與程式碼複雜度。

---

總結

MySQL 提供的日期時間函式非常強大且彈性十足，從取得現在時間、加減時間、計算差距，到格式化與時區轉換，幾乎可以在 SQL 層面完成所有常見的時間運算。掌握以下幾點，就能在開發與維運中游刃有餘：

1. 了解四種時間型別的差異，選對儲存格式。
2. 熟悉 NOW()、CURDATE()、DATE_ADD/DATE_SUB、DATEDIFF、DATE_FORMAT 等核心函式的用法。
3. 避免時區與型別的隱式轉換問題，必要時使用 CONVERT_TZ() 與 CAST() 明確指定。
4. 在大量資料查詢時，善用索引與分區，配合日期條件提升效能。
5. 以實務需求為導向，把時間處理的邏輯放在資料庫層，讓前端或應用程式更專注於業務邏輯。

只要將這些概念與技巧內化，您就能在任何 MySQL 專案中，快速、正確且高效地處理日期與時間，為系統的穩定性與可維護性奠定堅實基礎。祝您寫程式愉快，資料庫永遠「時間」正確！