Unix 時間戳互轉
| 函數 | 語法 | 說明 | 範例 |
|---|
| UNIX_TIMESTAMP | UNIX_TIMESTAMP() / UNIX_TIMESTAMP(date) | 日期 → 秒數 (1970-01-01 起) | UNIX_TIMESTAMP('2024-01-15 10:30:00') → 1705285800 |
| FROM_UNIXTIME | FROM_UNIXTIME(ts) / FROM_UNIXTIME(ts, format) | 秒數 → 日期字串 | FROM_UNIXTIME(1705285800) → '2024-01-15 10:30:00'
FROM_UNIXTIME(ts, '%Y-%m-%d') → '2024-01-15' |
日期運算 (核心)
-- 加減間隔
DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)
-- 同義詞
ADDDATE(date, INTERVAL expr unit)
SUBDATE(date, INTERVAL expr unit)
-- 範例
SELECT DATE_ADD('2024-01-15', INTERVAL 1 DAY); -- 2024-01-16
SELECT DATE_SUB('2024-01-15', INTERVAL 3 MONTH); -- 2023-10-15
SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR); -- 2 小時後
SELECT DATE_ADD('2024-01-15 10:00', INTERVAL '1:30' HOUR_MINUTE); -- 2024-01-15 11:30
支援的 INTERVAL 單位
| 單位 | expr 格式 | 範例 |
|---|
| SECOND | 秒數 | 30 |
| MINUTE | 分鐘 | 45 |
| HOUR | 小時 | 2 |
| DAY | 天數 | 10 |
| WEEK | 週數 | 3 |
| MONTH | 月數 | 6 |
| QUARTER | 季度 | 2 |
| YEAR | 年數 | 1 |
| MINUTE_SECOND | "分:秒" | "30:15" |
| HOUR_MINUTE | "時:分" | "2:30" |
| DAY_HOUR | "天 時" | "1 12" |
| YEAR_MONTH | "年-月" | "2024-06" |
日期差值與邊界
| 函數 | 語法 | 回傳 | 範例 |
|---|
| DATEDIFF | DATEDIFF(date1, date2) | date1 - date2 的**天數差** (可負) | DATEDIFF('2024-01-16','2024-01-15') → 1 |
| TIMEDIFF | TIMEDIFF(dt1, dt2) | 時間差 **HH:MM:SS** | TIMEDIFF('12:00','10:30') → '01:30:00' |
| TIMESTAMPDIFF | TIMESTAMPDIFF(unit, dt1, dt2) | 指定單位差值 (更精確) | TIMESTAMPDIFF(MONTH, '2023-01-15', '2024-01-15') → 12 |
| LAST_DAY | LAST_DAY(date) | 該月最後一天日期 | LAST_DAY('2024-02-10') → '2024-02-29' |
日期成分提取
YEAR(date), MONTH(date), DAY(date) / DAYOFMONTH(date)
HOUR(time), MINUTE(time), SECOND(time)
DAYOFWEEK(date) -- 1=Sun ... 7=Sat (ODBC 標準)
WEEKDAY(date) -- 0=Mon ... 6=Sun
DAYOFYEAR(date) -- 1~366
WEEK(date[, mode]) -- 週號，mode 決定週起始日
QUARTER(date) -- 1~4
MONTHNAME(date) -- 'January'...
DAYNAME(date) -- 'Monday'...
格式化輸出
DATE_FORMAT(date, format)
-- 常用格式符
%Y 4位年 %m 2位月 %d 2位日
%H 24時 %i 分 %s 秒
%W 週名 %M 月名
%r 12時制時間 (hh:mm:ss AM/PM)
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-01-15 10:30:45
週期運算 (較少用)
PERIOD_ADD(P, N) -- P=YYYYMM 或 YYMM，加 N 月，回傳 YYYYMM
PERIOD_DIFF(P1,P2) -- 月數差
SELECT PERIOD_ADD(202401, 3); -- 202404
SELECT PERIOD_DIFF(202404, 202301); -- 15
實戰常用模式
-- 最近 30 天
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
-- 本月資料
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND created_at < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
-- 今日零點至今
WHERE created_at >= CURDATE()
-- 年齡計算
SELECT FLOOR(DATEDIFF(NOW(), birth_date) / 365.25) AS age FROM users;
-- 下個月第一天
SELECT DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY);
時區處理關鍵
- 伺服器時區：
SHOW VARIABLES LIKE 'time_zone'; (預設 SYSTEM)
- 會話時區：
SET time_zone = '+08:00'; 或 'Asia/Taipei' (需載入時區表)
TIMESTAMP 存儲 UTC，讀取自動轉換會話時區；DATETIME 不轉換- 跨時區應用**統一存儲 UTC**，應用層按用戶時區顯示