核心函數速查表

函數	用途	範例
NOW()	當前日期時間 (會話時區)	2024-01-15 14:30:00
CURDATE()	當前日期	2024-01-15
DATE_SUB(date, INTERVAL expr unit)	日期減間隔	DATE_SUB(NOW(), INTERVAL 7 DAY)
DATE_ADD(date, INTERVAL expr unit)	日期加間隔	DATE_ADD(CURDATE(), INTERVAL 1 MONTH)
DATE_FORMAT(date, format)	格式化輸出	DATE_FORMAT(NOW(), '%Y-%m')
TO_DAYS(date)	日期轉天數 (從 year 0)	TO_DAYS('2024-01-15')
PERIOD_DIFF(p1, p2)	月份差 (YYYYMM 格式)	PERIOD_DIFF(202401, 202312)=1
UNIX_TIMESTAMP()	秒級時間戳	1705312200
FROM_UNIXTIME(ts, format)	時間戳轉日期	FROM_UNIXTIME(1705312200, '%Y-%m-%d')

高頻相對時間查詢模板 (可直接套用)

今日 / 昨日 / 近 N 天

-- 今日 (含時分秒)
WHERE created_at >= CURDATE() AND created_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)

-- 昨日
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
  AND created_at < CURDATE()

-- 近 7 天 (含今天)
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)

-- 近 30 天
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

本月 / 上月 / 本年

-- 本月
WHERE DATE_FORMAT(created_at, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')

-- 上月
WHERE DATE_FORMAT(created_at, '%Y%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y%m')
-- 或
WHERE PERIOD_DIFF(DATE_FORMAT(CURDATE(), '%Y%m'), DATE_FORMAT(created_at, '%Y%m')) = 1

-- 本年
WHERE YEAR(created_at) = YEAR(CURDATE())

固定時間窗口 (如 2023-10-30 至 2023-10-31)

WHERE created_at >= '2023-10-30' AND created_at < '2023-11-01'
-- 注意：右邊界用 < 翌日 00:00:00，避免 23:59:59 精度問題

分組統計：按日/月/年聚合

-- 按日統計訂單量
SELECT DATE(created_at) AS day, COUNT(*) AS cnt
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY day;

-- 按月統計 (格式化輸出)
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

-- 按年月日時多維 (OLAP 風格)
SELECT 
  YEAR(created_at) AS yr,
  MONTH(created_at) AS mon,
  DAY(created_at) AS day,
  HOUR(created_at) AS hr,
  COUNT(*) AS cnt
FROM events
GROUP BY yr, mon, day, hr
WITH ROLLUP;  -- 產生小計/總計行

時區陷阱與最佳實踐

問題：伺服器時區 ≠ 業務時區

-- 查看當前時區
SHOW VARIABLES LIKE '%time_zone%';
-- System: CST (UTC+8) / Session: UTC

NOW() 返回會話時區時間，UTC_TIMESTAMP() 返回 UTC。若應用層寫入 UTC，查詢用 NOW() 會偏移。

解決方案

1. 統一存儲 UTC：應用層寫入 UTC_TIMESTAMP() 或帶時區的 TIMESTAMP (MySQL 自動轉換)。
2. 查詢時顯式設定會話時區：SET time_zone = '+08:00'; 或連線參數 serverTimezone=Asia/Shanghai (JDBC)。
3. 使用 TIMESTAMP 而非 DATETIME：TIMESTAMP 存儲 UTC，讀取自動按會話時區轉換；DATETIME 存什麼讀什麼，不轉換。

PHP 配合相對時間查詢 (PDO 參數化)

$pdo = new PDO($dsn, $user, $pass);
$stmt = $pdo->prepare("
    SELECT * FROM orders 
    WHERE created_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL :days DAY)
    ORDER BY created_at DESC
");
$stmt->execute(['days' => 30]);
$orders = $stmt->fetchAll(PDO::FETCH_ASSOC);

效能提示：讓範圍查詢走索引

• 對 created_at 建立索引。
• 避免在列上用函數：WHERE DATE(created_at) = CURDATE() ❌ → WHERE created_at >= CURDATE() AND created_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY) ✅。
• 大表分區按 RANGE (YEAR(created_at)) 或 RANGE (TO_DAYS(created_at))，實現分區裁剪。

掌握這套模板，時間維度查詢寫得又快又準。