日期時間資料類型回顧

類型	範圍	儲存	格式	用途
DATE	1000-01-01 ~ 9999-12-31	3 bytes	YYYY-MM-DD	純日期（生日、入職日）
TIME	-838:59:59 ~ 838:59:59	3 bytes	HH:MM:SS	時間間隔、每日時刻
DATETIME	1000-01-01 ~ 9999-12-31	8 bytes	YYYY-MM-DD HH:MM:SS	固定時區時間點
TIMESTAMP	1970-01-01 ~ 2038-01-19	4 bytes	YYYY-MM-DD HH:MM:SS	UTC 存儲，自動轉換時區
YEAR	1901 ~ 2155	1 byte	YYYY	年份

取得當前時間函數

函數	回傳類型	特性	說明
NOW() / CURRENT_TIMESTAMP() / LOCALTIMESTAMP()	DATETIME	查詢開始時固定	同一查詢多次呼叫回傳相同值
SYSDATE()	DATETIME	執行瞬間取值	存儲程序/觸發器中每次呼叫不同
CURDATE() / CURRENT_DATE()	DATE	當前日期
CURTIME() / CURRENT_TIME()	TIME	當前時間
UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP()	對應類型	UTC 時間	跨時區應用必備

日期時間運算：DATE_ADD / DATE_SUB / INTERVAL

INTERVAL 單位完整表

單位關鍵字	expr 格式	範例
MICROSECOND	微秒數	INTERVAL 500 MICROSECOND
SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR	整數	INTERVAL 3 DAY
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'	INTERVAL '1.5' SECOND_MICROSECOND
MINUTE_MICROSECOND	'MINUTES.MICROSECONDS'	INTERVAL '1.5' MINUTE_MICROSECOND
MINUTE_SECOND	'MINUTES:SECONDS'	INTERVAL '1:30' MINUTE_SECOND
HOUR_MINUTE	'HOURS:MINUTES'	INTERVAL '2:30' HOUR_MINUTE
HOUR_SECOND	'HOURS:MINUTES:SECONDS'	INTERVAL '1:30:45' HOUR_SECOND
DAY_HOUR	'DAYS HOURS'	INTERVAL '2 5' DAY_HOUR
DAY_MINUTE	'DAYS HOURS:MINUTES'	INTERVAL '2 5:30' DAY_MINUTE
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'	INTERVAL '1 2:30:45' DAY_SECOND
YEAR_MONTH	'YEARS-MONTHS'	INTERVAL '2-6' YEAR_MONTH

實戰範例

-- 基本加減
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);   -- 2024-02-29 (閏年自動調整)
SELECT DATE_SUB('2024-03-31', INTERVAL 1 MONTH);   -- 2024-02-29 (非月末日不調整)
SELECT DATE_ADD('2024-01-01', INTERVAL '1 12' DAY_HOUR); -- 2024-01-02 12:00:00

-- 運算子簡寫
SELECT '2024-01-01' + INTERVAL 1 DAY;    -- 2024-01-02
SELECT '2024-01-01 12:00' - INTERVAL 3 HOUR; -- 2024-01-01 09:00:00

-- ADDDATE/SUBDATE 同義詞
SELECT ADDDATE('2024-01-01', INTERVAL 1 MONTH);
SELECT SUBDATE('2024-01-01', 10);  -- 直接減天數

日期時間擷取與組裝

單一元件擷取

函數	回傳	範例
YEAR(), MONTH(), DAY()/DAYOFMONTH()	數值	MONTH('2024-03-15') → 3
HOUR(), MINUTE(), SECOND(), MICROSECOND()	數值	HOUR('14:30:59') → 14
DAYNAME(), MONTHNAME()	字串	DAYNAME('2024-03-15') → 'Friday'
DAYOFWEEK() (1=Sun), WEEKDAY() (0=Mon)	數值
DAYOFYEAR()	1-366
WEEK(), WEEKOFYEAR()	週數	模式參數控制週起始日/範圍
QUARTER()	1-4

EXTRACT 通用擷取

SELECT EXTRACT(YEAR_MONTH FROM '2024-03-15 10:30:45');  -- 202403
SELECT EXTRACT(DAY_HOUR FROM '2024-03-15 10:30:45');    -- 1510
SELECT EXTRACT(MICROSECOND FROM '2024-03-15 10:30:45.123456'); -- 123456

組裝日期時間

SELECT MAKEDATE(2024, 60);      -- 2024-02-29 (第 60 天)
SELECT MAKETIME(14, 30, 59);    -- 14:30:59
SELECT TIMESTAMP('2024-03-15', '14:30:59'); -- 2024-03-15 14:30:59

格式化與解析

DATE_FORMAT 格式化輸出

格式碼	說明	範例輸出
%Y	4 位數年	2024
%y	2 位數年	24
%m	月份 (01-12)	03
%c	月份 (1-12)	3
%d	日期 (01-31)	15
%e	日期 (1-31)	15
%H	24 小時 (00-23)	14
%h/%I	12 小時 (01-12)	02
%i	分鐘 (00-59)	30
%s/%S	秒 (00-59)	59
%W	星期名稱	Friday
%a	星期縮寫	Fri
%M	月份名稱	March
%b	月份縮寫	Mar
%T	24H 時間	14:30:59
%r	12H 時間 AM/PM	02:30:59 PM

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S');  -- 2024-03-15 14:30:59
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %W');    -- 2024年03月15日 Friday

STR_TO_DATE 字串解析

SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y');        -- 2024-03-15
SELECT STR_TO_DATE('2024年03月15日', '%Y年%m月%d日'); -- 2024-03-15
SELECT STR_TO_DATE('03/15/2024 02:30 PM', '%m/%d/%Y %h:%i %p'); -- 2024-03-15 14:30:00

GET_FORMAT 區域化格式

SELECT DATE_FORMAT(NOW(), GET_FORMAT(DATE, 'EUR'));   -- 15.03.2024
SELECT DATE_FORMAT(NOW(), GET_FORMAT(DATETIME, 'ISO')); -- 2024-03-15 14:30:59
SELECT STR_TO_DATE('15.03.2024', GET_FORMAT(DATE, 'EUR'));

時間差計算

函數	說明	範例
DATEDIFF(d1, d2)	日期差天數 (d1 - d2)	DATEDIFF('2024-03-15', '2024-03-10') → 5
TIMEDIFF(t1, t2)	時間差 (t1 - t2)	TIMEDIFF('14:30:00', '12:00:00') → '02:30:00'
TIMESTAMPDIFF(unit, dt1, dt2)	指定單位差值 (dt2 - dt1)	TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-03-15') → 2
TIMESTAMPADD(unit, interval, dt)	加減指定單位	TIMESTAMPADD(HOUR, 3, '2024-03-15 10:00') → '2024-03-15 13:00:00'
TIME_TO_SEC() / SEC_TO_TIME()	時間⇄秒數互轉	TIME_TO_SEC('01:00:00') → 3600

時區轉換

-- CONVERT_TZ 需載入時區表
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

SELECT CONVERT_TZ('2024-03-15 14:30:00', '+00:00', '+08:00'); -- 2024-03-15 22:30:00
SELECT CONVERT_TZ(NOW(), @@session.time_zone, 'America/New_York');

Unix Timestamp 互轉

SELECT UNIX_TIMESTAMP();                    -- 當前 timestamp
SELECT UNIX_TIMESTAMP('2024-03-15 14:30:00'); -- 指定時間轉 timestamp
SELECT FROM_UNIXTIME(1710484200);           -- timestamp 轉 DATETIME
SELECT FROM_UNIXTIME(1710484200, '%Y-%m-%d'); -- 格式化輸出

實戰常用查詢模式

1. 最近 N 天/月資料

-- 最近 30 天（含今日）
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

-- 本月資料
WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND created_at < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)

-- 上月資料
WHERE created_at >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
  AND created_at < DATE_FORMAT(CURDATE(), '%Y-%m-01')

2. 年齡計算

SELECT FLOOR(DATEDIFF(CURDATE(), birth_date) / 365.25) AS age FROM users;

3. 本週/本月第一天

SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AS week_start;
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS month_start;

4. 避開函數索引失效

-- ❌ 無法使用 created_at 索引
WHERE DATE(created_at) = '2024-03-15'

-- ✅ 使用範圍查詢
WHERE created_at >= '2024-03-15' AND created_at < '2024-03-16'

-- ✅ MySQL 8.0 功能性索引
ALTER TABLE logs ADD INDEX idx_created_date ((DATE(created_at)));

PHP 整合範例

<?php
$conn = new mysqli("localhost", "user", "pass", "logs");

$start = $_GET['start'] ?? date('Y-m-01');
$end   = $_GET['end'] ?? date('Y-m-d');

$stmt = $conn->prepare("
    SELECT 
        DATE(created_at) AS log_date,
        COUNT(*) AS total,
        SUM(CASE WHEN level = 'ERROR' THEN 1 ELSE 0 END) AS errors
    FROM app_logs
    WHERE created_at >= ? AND created_at < DATE_ADD(?, INTERVAL 1 DAY)
    GROUP BY DATE(created_at)
    ORDER BY log_date
");
$stmt->bind_param("ss", $start, $end);
$stmt->execute();
$result = $stmt->get_result();

echo "";
while ($row = $result->fetch_assoc()) {
    echo "";
}
echo "
日期
總筆數
錯誤數
{$row['log_date']}
{$row['total']}
{$row['errors']}
";
?>

精通日期時間函數，讓時間相關業務邏輯在資料庫層高效、精準地完成！