給定出生日期 @dateofbirth,計算當前年齡:
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(@dateofbirth)), '%Y') + 0;
若 dt1、dt2 格式為 'YYYY-MM-DD HH:MM:SS',秒數差:
UNIX_TIMESTAMP(dt2) - UNIX_TIMESTAMP(dt1)
SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N;
需一張日曆表 calendar,含日期欄位 d 與假期標記 holiday:
SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN Start AND Stop
AND DAYOFWEEK(d) NOT IN (1,7)
AND holiday = 0;
UNIX_TIMESTAMP() 返回從 1970-01-01 到現在的秒數。帶參數時返回該日期時間的秒數。
SELECT id, title, content, addtime
FROM tablename_tbl
WHERE UNIX_TIMESTAMP(addtime) >= UNIX_TIMESTAMP() - 10800
AND UNIX_TIMESTAMP(addtime) <= UNIX_TIMESTAMP();
DATE_FORMAT 或 DATE() 比較日期字符串SELECT * FROM tb
WHERE c > DATE_FORMAT('2007-07-06','%Y%m%d')
AND c <= DATE_FORMAT('2007-07-09','%Y%m%d');
SELECT * FROM tb
WHERE c > DATE('2007-07-07')
AND c < DATE('2007-07-09');
更多日期比較技巧可參考相關文檔。