MySQL 日期時間函式操作與格式化技巧

– 12 分鐘閱讀

在資料庫應用程式的開發過程中，日期時間資料的處理是一個無法迴避的重要議題。幾乎所有的業務系統都需要記錄事件發生的時間、計算時間差異、或是將時間資料轉換成特定的顯示格式。MySQL 作為最廣泛使用的關聯式資料庫之一，提供了豐富且強大的日期時間函式庫，讓開發者能夠在資料庫層級直接完成各種時間相關的運算與轉換。

本文將深入探討 MySQL 中最常用的日期時間函式，從基本的日期加減運算開始，逐步介紹時間元素的提取、日期差異的計算，以及格式轉換的技巧。透過這些函式的靈活組合運用，開發者可以大幅簡化時間資料的處理邏輯，提升應用程式的開發效率和執行效能。

取得當前日期時間

在進行日期時間運算之前，首先需要了解如何取得當前的日期和時間。MySQL 提供了多個函式來取得系統的當前時間，這些函式在記錄資料建立時間、計算時間差異等場景中被廣泛使用。

NOW 函式會回傳當前的日期和時間，格式為 YYYY-MM-DD HH:MM:SS。CURDATE 函式只回傳當前日期，而 CURTIME 函式只回傳當前時間。這三個函式是最基本也最常用的時間取得函式。

-- 取得當前日期時間的各種方式
-- NOW() 回傳完整的日期時間
SELECT NOW() AS current_datetime;
-- 執行結果：2025-11-27 14:30:45

-- CURDATE() 只回傳日期部分
SELECT CURDATE() AS current_date;
-- 執行結果：2025-11-27

-- CURTIME() 只回傳時間部分
SELECT CURTIME() AS current_time;
-- 執行結果：14:30:45

-- 也可以使用 CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP
-- 這些是 SQL 標準語法，功能相同
SELECT
    CURRENT_DATE AS std_date,
    CURRENT_TIME AS std_time,
    CURRENT_TIMESTAMP AS std_timestamp;

值得注意的是，NOW 函式在同一個查詢中會回傳相同的值，即使查詢執行了一段時間。如果需要取得語句執行時的即時時間，可以使用 SYSDATE 函式。

-- NOW() 與 SYSDATE() 的差異
-- 在複雜查詢中，NOW() 回傳查詢開始時的時間
-- SYSDATE() 回傳函式被呼叫時的即時時間
SELECT
    NOW() AS query_start_time,
    SLEEP(2) AS delay,
    NOW() AS now_after_delay,      -- 仍然是查詢開始時間
    SYSDATE() AS sysdate_after_delay;  -- 延遲後的即時時間

日期加減運算

在實務應用中，經常需要對日期進行加減運算，例如計算截止日期、預測未來事件發生時間、或是取得過去某個時間點的資料。MySQL 提供了 DATE_ADD 和 DATE_SUB 函式來完成這些運算。

DATE_ADD 函式用於在指定日期上增加時間間隔，語法為 DATE_ADD(date, INTERVAL value unit)。其中 date 是要操作的日期時間值，value 是要增加的數量，unit 是時間單位。支援的時間單位包括 MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR 等。

-- DATE_ADD 函式的基本使用
-- 假設有一個事件資料表，需要計算各種時間相關的值

-- 在目前日期上增加各種時間間隔
SELECT
    CURDATE() AS today,
    DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week,
    DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AS next_month,
    DATE_ADD(CURDATE(), INTERVAL 1 YEAR) AS next_year,
    DATE_ADD(CURDATE(), INTERVAL 3 QUARTER) AS next_3_quarters;

-- 在日期時間上增加時間間隔
SELECT
    NOW() AS current_datetime,
    DATE_ADD(NOW(), INTERVAL 2 HOUR) AS plus_2_hours,
    DATE_ADD(NOW(), INTERVAL 30 MINUTE) AS plus_30_minutes,
    DATE_ADD(NOW(), INTERVAL 45 SECOND) AS plus_45_seconds;

-- 實務應用：計算訂單的預計送達時間
-- 假設標準配送需要 3 個工作天
SELECT
    order_id,
    order_date,
    DATE_ADD(order_date, INTERVAL 3 DAY) AS estimated_delivery
FROM orders
WHERE order_status = 'shipped';

-- 實務應用：計算會員到期日
-- 會員期限為註冊日起一年
SELECT
    member_id,
    member_name,
    registration_date,
    DATE_ADD(registration_date, INTERVAL 1 YEAR) AS expiry_date
FROM members;

DATE_SUB 函式的作用與 DATE_ADD 相反，用於從指定日期減去時間間隔。語法結構完全相同，只是運算方向相反。

-- DATE_SUB 函式的基本使用
-- 從目前日期減去各種時間間隔
SELECT
    CURDATE() AS today,
    DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS last_week,
    DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS last_month,
    DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AS last_year;

-- 實務應用：找出過去 30 天的訂單
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY order_date DESC;

-- 實務應用：計算庫存周轉率
-- 取得過去 90 天的銷售資料
SELECT
    product_id,
    SUM(quantity) AS total_sold
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY product_id;

MySQL 也支援使用加減運算子直接進行日期運算，這種語法更為簡潔。

-- 使用運算子進行日期運算
-- 加號和減號可以直接與 INTERVAL 搭配使用
SELECT
    CURDATE() + INTERVAL 7 DAY AS next_week,
    CURDATE() - INTERVAL 1 MONTH AS last_month,
    NOW() + INTERVAL 2 HOUR AS plus_2_hours,
    NOW() - INTERVAL 30 MINUTE AS minus_30_minutes;

-- 複合時間間隔
-- 可以同時增加多個不同單位的時間
SELECT
    NOW() AS current_time,
    DATE_ADD(NOW(), INTERVAL '1:30' HOUR_MINUTE) AS plus_1h30m,
    DATE_ADD(NOW(), INTERVAL '1 2:30:45' DAY_SECOND) AS plus_complex;

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100

title 日期加減運算流程

start
:輸入原始日期時間;

if (需要增加時間?) then (是)
    :使用 DATE_ADD 函式;
else (否)
    :使用 DATE_SUB 函式;
endif

:指定時間間隔數量;
:選擇時間單位;

note right
    支援的時間單位：
    - MICROSECOND
    - SECOND
    - MINUTE
    - HOUR
    - DAY
    - WEEK
    - MONTH
    - QUARTER
    - YEAR
end note

:計算新的日期時間;
:回傳結果;
stop

@enduml

時間元素提取

在資料分析和報表產生的過程中，經常需要從完整的日期時間中提取特定的元素，例如年份、月份、日期、小時等。MySQL 提供了 EXTRACT 函式和一系列專用函式來完成這項工作。

EXTRACT 函式的語法為 EXTRACT(unit FROM date)，其中 unit 指定要提取的時間單位。這個函式符合 SQL 標準，具有良好的可移植性。

-- EXTRACT 函式的基本使用
SELECT
    NOW() AS current_datetime,
    EXTRACT(YEAR FROM NOW()) AS year,
    EXTRACT(MONTH FROM NOW()) AS month,
    EXTRACT(DAY FROM NOW()) AS day,
    EXTRACT(HOUR FROM NOW()) AS hour,
    EXTRACT(MINUTE FROM NOW()) AS minute,
    EXTRACT(SECOND FROM NOW()) AS second,
    EXTRACT(WEEK FROM NOW()) AS week_of_year,
    EXTRACT(QUARTER FROM NOW()) AS quarter;

-- 實務應用：依月份統計銷售額
SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    SUM(total_amount) AS monthly_sales
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025
GROUP BY
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

除了 EXTRACT 函式之外，MySQL 還提供了一系列專用函式，語法更為簡潔。這些函式包括 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEK、QUARTER 等。

-- 專用提取函式
SELECT
    NOW() AS current_datetime,
    YEAR(NOW()) AS year,
    MONTH(NOW()) AS month,
    DAY(NOW()) AS day,
    HOUR(NOW()) AS hour,
    MINUTE(NOW()) AS minute,
    SECOND(NOW()) AS second,
    WEEK(NOW()) AS week,
    QUARTER(NOW()) AS quarter;

-- DAYOFWEEK：回傳星期幾（1=Sunday, 7=Saturday）
-- DAYOFMONTH：回傳月份中的第幾天
-- DAYOFYEAR：回傳年度中的第幾天
SELECT
    CURDATE() AS today,
    DAYOFWEEK(CURDATE()) AS day_of_week,
    DAYOFMONTH(CURDATE()) AS day_of_month,
    DAYOFYEAR(CURDATE()) AS day_of_year;

-- WEEKDAY：回傳星期幾（0=Monday, 6=Sunday）
-- 與 DAYOFWEEK 的計數方式不同
SELECT
    CURDATE() AS today,
    WEEKDAY(CURDATE()) AS weekday_num,
    DAYNAME(CURDATE()) AS day_name;

-- MONTHNAME：回傳月份名稱
SELECT
    CURDATE() AS today,
    MONTHNAME(CURDATE()) AS month_name;

DATE 和 TIME 函式可以分別從完整的日期時間中提取日期部分和時間部分，這在需要忽略時間或日期進行比較時非常有用。

-- DATE 和 TIME 函式
SELECT
    NOW() AS full_datetime,
    DATE(NOW()) AS date_only,
    TIME(NOW()) AS time_only;

-- 實務應用：依日期分組統計
-- 使用 DATE() 確保同一天的不同時間被視為同一組
SELECT
    DATE(order_datetime) AS order_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS daily_sales
FROM orders
GROUP BY DATE(order_datetime)
ORDER BY order_date DESC
LIMIT 30;

-- 實務應用：找出特定時段的訂單
-- 找出中午 12 點到下午 2 點的訂單
SELECT
    order_id,
    order_datetime,
    total_amount
FROM orders
WHERE HOUR(order_datetime) BETWEEN 12 AND 13
ORDER BY order_datetime;

日期差異計算

計算兩個日期之間的差異是資料分析中最常見的需求之一。MySQL 提供了 DATEDIFF 和 TIMESTAMPDIFF 函式來滿足不同精度的需求。

DATEDIFF 函式計算兩個日期之間相差的天數，語法為 DATEDIFF(date1, date2)。函式會回傳 date1 減去 date2 的天數，如果 date1 較晚則結果為正數，較早則為負數。

-- DATEDIFF 函式的基本使用
SELECT
    DATEDIFF('2025-12-31', '2025-01-01') AS days_in_year,
    DATEDIFF('2025-01-01', '2025-12-31') AS negative_days,
    DATEDIFF(CURDATE(), '2025-01-01') AS days_from_new_year;

-- DATEDIFF 只計算日期部分，忽略時間
SELECT
    DATEDIFF(
        '2025-01-02 00:00:00',
        '2025-01-01 23:59:59'
    ) AS only_one_second_diff;
-- 執行結果：1（因為日期不同）

-- 實務應用：計算會員註冊天數
SELECT
    member_id,
    member_name,
    registration_date,
    DATEDIFF(CURDATE(), registration_date) AS membership_days
FROM members
ORDER BY membership_days DESC;

-- 實務應用：找出逾期未處理的訂單
SELECT
    order_id,
    order_date,
    DATEDIFF(CURDATE(), order_date) AS days_pending
FROM orders
WHERE order_status = 'pending'
AND DATEDIFF(CURDATE(), order_date) > 7
ORDER BY days_pending DESC;

TIMESTAMPDIFF 函式提供更精確的時間差計算，可以指定回傳的時間單位。語法為 TIMESTAMPDIFF(unit, datetime1, datetime2)，回傳 datetime2 減去 datetime1 的結果。

-- TIMESTAMPDIFF 函式的基本使用
SELECT
    TIMESTAMPDIFF(SECOND, '2025-01-01 00:00:00', '2025-01-01 01:30:45') AS seconds,
    TIMESTAMPDIFF(MINUTE, '2025-01-01 00:00:00', '2025-01-01 01:30:45') AS minutes,
    TIMESTAMPDIFF(HOUR, '2025-01-01 00:00:00', '2025-01-02 12:00:00') AS hours,
    TIMESTAMPDIFF(DAY, '2025-01-01', '2025-01-31') AS days,
    TIMESTAMPDIFF(WEEK, '2025-01-01', '2025-02-01') AS weeks,
    TIMESTAMPDIFF(MONTH, '2025-01-01', '2025-12-31') AS months,
    TIMESTAMPDIFF(QUARTER, '2025-01-01', '2025-12-31') AS quarters,
    TIMESTAMPDIFF(YEAR, '2000-01-01', '2025-01-01') AS years;

-- 實務應用：計算客服回應時間（以分鐘為單位）
SELECT
    ticket_id,
    created_at,
    first_response_at,
    TIMESTAMPDIFF(MINUTE, created_at, first_response_at) AS response_minutes
FROM support_tickets
WHERE first_response_at IS NOT NULL
ORDER BY response_minutes;

-- 實務應用：計算使用者年齡
SELECT
    user_id,
    user_name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users
ORDER BY age DESC;

-- 實務應用：計算專案執行期間
SELECT
    project_id,
    project_name,
    start_date,
    end_date,
    TIMESTAMPDIFF(DAY, start_date, COALESCE(end_date, CURDATE())) AS duration_days
FROM projects;

@startuml
!define PLANTUML_FORMAT svg
!theme _none_

skinparam dpi auto
skinparam shadowing false
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 16
skinparam minClassWidth 100

title 日期差異計算函式比較

rectangle "DATEDIFF" as datediff {
    rectangle "只計算日期部分" as d1
    rectangle "忽略時間資訊" as d2
    rectangle "回傳天數差異" as d3
}

rectangle "TIMESTAMPDIFF" as timestampdiff {
    rectangle "計算完整時間戳記" as t1
    rectangle "可指定時間單位" as t2
    rectangle "支援精確計算" as t3
}

note bottom of datediff
    語法：DATEDIFF(date1, date2)
    回傳：date1 - date2 的天數
end note

note bottom of timestampdiff
    語法：TIMESTAMPDIFF(unit, dt1, dt2)
    回傳：dt2 - dt1（指定單位）

    支援單位：
    SECOND, MINUTE, HOUR
    DAY, WEEK, MONTH
    QUARTER, YEAR
end note

@enduml

日期時間格式化

DATE_FORMAT 函式是 MySQL 中最強大的日期時間格式化工具，它可以將日期時間值轉換成任意指定的字串格式。語法為 DATE_FORMAT(date, format)，其中 format 使用特殊的格式化符號來定義輸出格式。

常用的格式化符號包括：年份相關的 %Y（四位數年份）和 %y（兩位數年份）；月份相關的 %m（兩位數月份）、%c（不補零的月份）和 %M（月份名稱）；日期相關的 %d（兩位數日期）、%e（不補零的日期）和 %D（帶序數後綴）；時間相關的 %H（24小時制）、%h（12小時制）、%i（分鐘）、%s（秒數）和 %p（AM/PM）；星期相關的 %W（完整名稱）和 %w（數字）。

-- DATE_FORMAT 函式的基本使用
SELECT
    NOW() AS original,
    DATE_FORMAT(NOW(), '%Y-%m-%d') AS iso_date,
    DATE_FORMAT(NOW(), '%Y/%m/%d') AS slash_date,
    DATE_FORMAT(NOW(), '%d-%m-%Y') AS european_date,
    DATE_FORMAT(NOW(), '%m/%d/%Y') AS us_date;

-- 時間格式化
SELECT
    NOW() AS original,
    DATE_FORMAT(NOW(), '%H:%i:%s') AS time_24hr,
    DATE_FORMAT(NOW(), '%h:%i:%s %p') AS time_12hr,
    DATE_FORMAT(NOW(), '%r') AS time_12hr_full,
    DATE_FORMAT(NOW(), '%T') AS time_24hr_full;

-- 中文格式化
SELECT
    NOW() AS original,
    DATE_FORMAT(NOW(), '%Y年%m月%d日') AS chinese_date,
    DATE_FORMAT(NOW(), '%Y年%m月%d日 %H時%i分%s秒') AS chinese_full,
    DATE_FORMAT(NOW(), '%Y年第%u週') AS chinese_week;

-- 完整的格式化範例
SELECT
    NOW() AS original,
    DATE_FORMAT(NOW(), '%W, %M %D, %Y') AS full_date,
    DATE_FORMAT(NOW(), '%a, %b %d, %Y %h:%i %p') AS abbreviated;

-- 實務應用：產生報表標題日期
SELECT
    DATE_FORMAT(CURDATE(), '%Y年%m月 營業報表') AS report_title;

-- 實務應用：格式化訂單列表
SELECT
    order_id,
    DATE_FORMAT(order_datetime, '%Y/%m/%d %H:%i') AS order_time,
    customer_name,
    total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY order_datetime DESC
LIMIT 10;

TIME_FORMAT 函式專門用於格式化時間值，功能與 DATE_FORMAT 類似，但只處理時間部分。

-- TIME_FORMAT 函式
SELECT
    CURTIME() AS original,
    TIME_FORMAT(CURTIME(), '%H:%i:%s') AS standard,
    TIME_FORMAT(CURTIME(), '%h:%i %p') AS am_pm,
    TIME_FORMAT(CURTIME(), '%l:%i %p') AS no_leading_zero,
    TIME_FORMAT(CURTIME(), '%H時%i分%s秒') AS chinese;

字串轉換為日期時間

STR_TO_DATE 函式用於將格式化的字串轉換回日期時間型態，這在匯入外部資料或處理使用者輸入時非常有用。語法為 STR_TO_DATE(str, format)，format 必須準確對應字串的格式。

-- STR_TO_DATE 函式的基本使用
SELECT
    STR_TO_DATE('2025-11-27', '%Y-%m-%d') AS iso_date,
    STR_TO_DATE('27/11/2025', '%d/%m/%Y') AS european_date,
    STR_TO_DATE('11/27/2025', '%m/%d/%Y') AS us_date,
    STR_TO_DATE('2025年11月27日', '%Y年%m月%d日') AS chinese_date;

-- 包含時間的轉換
SELECT
    STR_TO_DATE('2025-11-27 14:30:45', '%Y-%m-%d %H:%i:%s') AS datetime,
    STR_TO_DATE('14:30:45', '%H:%i:%s') AS time_only,
    STR_TO_DATE('2:30 PM', '%h:%i %p') AS time_12hr;

-- 實務應用：匯入 CSV 資料
-- 假設 CSV 檔案中的日期格式為 MM/DD/YYYY
INSERT INTO events (event_name, event_date)
VALUES (
    '產品發表會',
    STR_TO_DATE('12/25/2025', '%m/%d/%Y')
);

-- 實務應用：處理使用者輸入的日期
-- 假設前端傳來的日期格式為 YYYY年MM月DD日
SELECT *
FROM orders
WHERE DATE(order_datetime) = STR_TO_DATE('2025年11月27日', '%Y年%m月%d日');

其他實用的日期時間函式

MySQL 還提供了許多其他實用的日期時間函式，可以滿足各種特殊的需求。

LAST_DAY 函式回傳指定日期所在月份的最後一天，這在計算月底日期或判斷月份天數時非常有用。

-- LAST_DAY 函式
SELECT
    CURDATE() AS today,
    LAST_DAY(CURDATE()) AS last_day_of_month,
    DAY(LAST_DAY(CURDATE())) AS days_in_month;

-- 計算下個月的第一天
SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY) AS first_of_next_month;

-- 實務應用：計算本月剩餘天數
SELECT
    CURDATE() AS today,
    LAST_DAY(CURDATE()) AS month_end,
    DATEDIFF(LAST_DAY(CURDATE()), CURDATE()) AS days_remaining;

MAKEDATE 和 MAKETIME 函式可以根據指定的元素建立日期或時間值。

-- MAKEDATE：根據年份和天數建立日期
-- 語法：MAKEDATE(year, day_of_year)
SELECT
    MAKEDATE(2025, 1) AS first_day,
    MAKEDATE(2025, 100) AS day_100,
    MAKEDATE(2025, 365) AS last_day;

-- MAKETIME：建立時間值
-- 語法：MAKETIME(hour, minute, second)
SELECT
    MAKETIME(14, 30, 45) AS custom_time,
    MAKETIME(0, 0, 0) AS midnight,
    MAKETIME(23, 59, 59) AS end_of_day;

SEC_TO_TIME 和 TIME_TO_SEC 函式可以在秒數和時間格式之間進行轉換。

-- 秒數與時間的轉換
SELECT
    SEC_TO_TIME(3661) AS seconds_to_time,  -- 1小時1分1秒
    TIME_TO_SEC('01:01:01') AS time_to_seconds;

-- 實務應用：計算總工時
SELECT
    employee_id,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(clock_out, clock_in)))) AS total_hours
FROM time_records
WHERE work_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY employee_id;

時區處理

在處理跨時區應用時，正確的時區處理至關重要。MySQL 提供了 CONVERT_TZ 函式來進行時區轉換。

-- CONVERT_TZ 函式
-- 語法：CONVERT_TZ(datetime, from_tz, to_tz)
SELECT
    NOW() AS server_time,
    CONVERT_TZ(NOW(), '+00:00', '+08:00') AS taipei_time,
    CONVERT_TZ(NOW(), '+08:00', '+00:00') AS utc_time,
    CONVERT_TZ(NOW(), 'UTC', 'Asia/Taipei') AS named_tz;

-- 實務應用：儲存 UTC 時間，查詢時轉換為本地時間
SELECT
    event_id,
    event_name,
    CONVERT_TZ(event_time_utc, '+00:00', '+08:00') AS event_time_local
FROM events
WHERE event_time_utc >= CONVERT_TZ(NOW(), '+08:00', '+00:00');

實務案例整合

讓我們透過一個完整的實務案例來整合本文介紹的各種日期時間函式。假設我們正在開發一個訂單管理系統，需要產生各種時間相關的報表和統計資料。

-- 訂單資料表結構
CREATE TABLE orders (
    -- 訂單唯一識別碼
    order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -- 客戶識別碼
    customer_id INT UNSIGNED NOT NULL,
    -- 訂單建立時間
    order_datetime DATETIME NOT NULL,
    -- 出貨時間
    shipped_datetime DATETIME,
    -- 送達時間
    delivered_datetime DATETIME,
    -- 訂單金額
    total_amount DECIMAL(10, 2) NOT NULL,
    -- 訂單狀態
    order_status VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 綜合報表查詢：本月訂單分析
SELECT
    -- 訂單基本資訊
    order_id,
    DATE_FORMAT(order_datetime, '%Y/%m/%d %H:%i') AS 訂單時間,

    -- 時間元素提取
    DAYNAME(order_datetime) AS 星期,
    HOUR(order_datetime) AS 下單小時,

    -- 處理時效計算
    CASE
        WHEN shipped_datetime IS NOT NULL THEN
            CONCAT(
                TIMESTAMPDIFF(HOUR, order_datetime, shipped_datetime),
                '小時'
            )
        ELSE '尚未出貨'
    END AS 出貨時效,

    -- 配送時效計算
    CASE
        WHEN delivered_datetime IS NOT NULL THEN
            CONCAT(
                DATEDIFF(delivered_datetime, order_datetime),
                '天'
            )
        ELSE '配送中'
    END AS 配送天數,

    -- 訂單金額
    CONCAT('$', FORMAT(total_amount, 0)) AS 訂單金額,

    -- 訂單狀態
    CASE order_status
        WHEN 'pending' THEN '待處理'
        WHEN 'processing' THEN '處理中'
        WHEN 'shipped' THEN '已出貨'
        WHEN 'delivered' THEN '已送達'
        WHEN 'cancelled' THEN '已取消'
    END AS 訂單狀態

FROM orders
WHERE
    -- 篩選本月訂單
    YEAR(order_datetime) = YEAR(CURDATE())
    AND MONTH(order_datetime) = MONTH(CURDATE())
ORDER BY order_datetime DESC;

-- 每日銷售統計
SELECT
    DATE(order_datetime) AS 日期,
    DATE_FORMAT(order_datetime, '%a') AS 星期,
    COUNT(*) AS 訂單數,
    SUM(total_amount) AS 營業額,
    AVG(total_amount) AS 平均客單價
FROM orders
WHERE order_datetime >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
AND order_status != 'cancelled'
GROUP BY DATE(order_datetime)
ORDER BY 日期 DESC;

-- 尖峰時段分析
SELECT
    HOUR(order_datetime) AS 小時,
    COUNT(*) AS 訂單數,
    SUM(total_amount) AS 營業額
FROM orders
WHERE order_datetime >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND order_status != 'cancelled'
GROUP BY HOUR(order_datetime)
ORDER BY 訂單數 DESC;

-- 計算重要日期
SELECT
    -- 距離年底還有多少天
    DATEDIFF(
        CONCAT(YEAR(CURDATE()), '-12-31'),
        CURDATE()
    ) AS 距離年底天數,

    -- 本月剩餘天數
    DATEDIFF(
        LAST_DAY(CURDATE()),
        CURDATE()
    ) AS 本月剩餘天數,

    -- 下週一的日期
    DATE_ADD(
        CURDATE(),
        INTERVAL (7 - WEEKDAY(CURDATE())) DAY
    ) AS 下週一,

    -- 本季最後一天
    LAST_DAY(
        MAKEDATE(
            YEAR(CURDATE()),
            1
        ) + INTERVAL QUARTER(CURDATE()) * 3 - 1 MONTH
    ) AS 本季最後一天;

效能最佳化建議

在處理大量資料時，日期時間函式的使用方式會直接影響查詢效能。以下是一些重要的效能最佳化建議。

避免在 WHERE 子句中對索引欄位使用函式，因為這會導致索引失效。應該將函式套用在比較值上，而不是欄位上。

-- 效能不佳：對索引欄位使用函式
SELECT * FROM orders
WHERE DATE(order_datetime) = '2025-11-27';

-- 效能較佳：使用範圍比較
SELECT * FROM orders
WHERE order_datetime >= '2025-11-27 00:00:00'
AND order_datetime < '2025-11-28 00:00:00';

-- 效能不佳：使用 YEAR() 和 MONTH() 函式
SELECT * FROM orders
WHERE YEAR(order_datetime) = 2025
AND MONTH(order_datetime) = 11;

-- 效能較佳：使用範圍比較
SELECT * FROM orders
WHERE order_datetime >= '2025-11-01'
AND order_datetime < '2025-12-01';

對於頻繁查詢的日期計算，可以考慮使用產生欄位（Generated Column）來預先計算並儲存結果。

-- 使用產生欄位儲存常用的日期元素
ALTER TABLE orders
ADD COLUMN order_year INT GENERATED ALWAYS AS (YEAR(order_datetime)) STORED,
ADD COLUMN order_month INT GENERATED ALWAYS AS (MONTH(order_datetime)) STORED,
ADD INDEX idx_order_year_month (order_year, order_month);

-- 現在可以直接查詢產生欄位，效能更好
SELECT * FROM orders
WHERE order_year = 2025 AND order_month = 11;

結語

MySQL 的日期時間函式提供了完整且強大的時間處理能力，從基本的加減運算到複雜的格式轉換，都能在資料庫層級直接完成。透過本文的介紹，讀者應該已經對這些函式有了全面的了解，包括 DATE_ADD 和 DATE_SUB 用於日期加減運算、EXTRACT 和各種專用函式用於提取時間元素、DATEDIFF 和 TIMESTAMPDIFF 用於計算日期差異、DATE_FORMAT 和 STR_TO_DATE 用於格式轉換。

在實務應用中，這些函式經常需要組合使用來滿足複雜的業務需求。正確理解每個函式的特性和使用場景，並注意效能最佳化的原則，能夠幫助開發者撰寫出高效能的 SQL 查詢。同時，建立統一的日期時間處理規範，對於團隊協作和系統維護也非常重要。

掌握這些日期時間函式後，開發者可以更有效率地處理各種時間相關的資料需求，從簡單的日期計算到複雜的時間序列分析，都能夠游刃有餘地完成。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。