MySQL 當天/當週/當月/時間戳轉換 SQL 寫法大全

本文系統整理 MySQL 中按天、週、月維度查詢數據的各種 SQL 寫法，涵蓋當天、當月第一天、上月第一天、本週第一天、上週範圍、時間戳雙向轉換等高頻場景。

基礎環境準備

CREATE TABLE IF NOT EXISTS t (
    id INT,
    addTime DATETIME DEFAULT '0000-00-00 00:00:00'
);

INSERT INTO t VALUES (1, '2012-07-12 21:00:00');
INSERT INTO t VALUES (2, '2012-07-22 21:00:00');

一、當天/當日數據查詢

日期函數差異： NOW() 含時分秒，CURDATE()/CURRENT_DATE() 僅日期。

二、當月相關查詢

-- 1. 今天是當月第幾天
SELECT DAYOFMONTH(NOW());

-- 2. 當月第一天 (00:00:00)
SELECT DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) - 1 DAY);
-- 或
SELECT DATE_FORMAT(NOW(), '%Y-%m-01');

-- 3. 上月第一天
SELECT DATE_SUB(
    DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) - 1 DAY),
    INTERVAL 1 MONTH
);

日期運算核心函數：

• DATE_ADD(date, INTERVAL expr unit)：增加間隔
• DATE_SUB(date, INTERVAL expr unit)：減少間隔
• INTERVAL expr unit：expr 可為負值表示反向

三、當週相關查詢

-- 1. 今天是週幾 (0=週一, 6=週日)
SELECT WEEKDAY(NOW());

-- 2. 本週第一天 (週一 00:00:00)
SELECT DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY);
-- 或
SELECT DATE_ADD(NOW(), INTERVAL -WEEKDAY(NOW()) DAY);
-- 或 (僅日期)
SELECT CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY;

-- 3. 上週第一天到現在的數據
SELECT * FROM t 
WHERE addTime >= DATE_FORMAT(
    DATE_SUB(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK),
    '%Y-%m-%d'
);

四、時間戳雙向轉換

-- 1. 時間戳 → 日期時間
SELECT FROM_UNIXTIME(1336542121);
-- 結果: 2012-05-09 14:22:01

-- 2. 日期時間 → 時間戳
SELECT UNIX_TIMESTAMP('2013-04-08');
-- 結果: 1365350400

-- 3. 帶時區轉換
SELECT FROM_UNIXTIME(1336542121, '%Y-%m-%d %H:%i:%s');
SELECT UNIX_TIMESTAMP('2013-04-08 12:00:00');

五、時間戳欄位查詢實戰 (INT 儲存)

-- 當天
WHERE addTime BETWEEN UNIX_TIMESTAMP(CURDATE()) AND UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY)

-- 當月
WHERE addTime BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01')) 
                  AND UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m-01'))

-- 當週
WHERE addTime BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY))
                  AND UNIX_TIMESTAMP(DATE_ADD(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK))

六、MySQL 8.0 簡化寫法

-- 直接日期運算
WHERE addTime >= CURDATE() - INTERVAL 1 DAY
WHERE addTime >= CURDATE() - INTERVAL 1 WEEK
WHERE addTime >= CURDATE() - INTERVAL 1 MONTH

避坑提醒

1. 時區一致性：應用層、資料庫層、系統層時區必須統一 (建議全鏈路 UTC)
2. 邊界包含關係：BETWEEN 包含兩端，日期範圍查詢建議 [start, end) 半開區間
3. 隱式轉換：字串與日期比較會觸發隱式轉換，建議顯式 DATE()/STR_TO_DATE()
4. NULL 值處理：時間欄位允許 NULL 時，查詢條件需額外 AND addTime IS NOT NULL