您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 日期時間函數大全：獲取、提取、計算、轉換、時區一網打盡

編輯：MySQL 資料庫相關知識

一、獲取當前日期時間

1.1 日期+時間：`NOW()`（推薦）

SELECT NOW(); -- 2008-08-08 22:20:46

同義函數：CURRENT_TIMESTAMP()、LOCALTIME()、LOCALTIMESTAMP() 等，統一用 NOW() 簡短易記。

1.2 動態獲取：`SYSDATE()`

NOW() 在語句開始執行時固定值；SYSDATE() 在函數執行瞬間取值：

SELECT NOW(), SLEEP(3), NOW();      -- 兩次 NOW() 相同
SELECT SYSDATE(), SLEEP(3), SYSDATE(); -- 相差 3 秒

一般極少用 SYSDATE()。

1.3 僅日期：`CURDATE()` | 僅時間：`CURTIME()`

SELECT CURDATE(); -- 2008-08-08
SELECT CURTIME(); -- 22:41:30

同義：CURRENT_DATE()、CURRENT_TIME()。

1.4 UTC 時間：`UTC_DATE()`、`UTC_TIME()`、`UTC_TIMESTAMP()`

SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME(), NOW();
-- UTC 比北京時間(東八區)早 8 小時

跨時區業務必用 UTC 存儲，顯示時轉換。

二、日期時間提取函數

2.1 單字段提取


SET @dt = '2008-09-10 07:15:30.123456';
SELECT DATE(@dt);        -- 2008-09-10
SELECT TIME(@dt);        -- 07:15:30.123456
SELECT YEAR(@dt);        -- 2008
SELECT QUARTER(@dt);     -- 3
SELECT MONTH(@dt);       -- 9
SELECT WEEK(@dt);        -- 36
SELECT DAY(@dt);         -- 10
SELECT HOUR(@dt);        -- 7
SELECT MINUTE(@dt);      -- 15
SELECT SECOND(@dt);      -- 30
SELECT MICROSECOND(@dt); -- 123456

2.2 通用 `EXTRACT()` — 功能最全


SELECT EXTRACT(YEAR_MONTH FROM @dt);     -- 200809
SELECT EXTRACT(DAY_HOUR FROM @dt);       -- 1007
SELECT EXTRACT(DAY_MINUTE FROM @dt);     -- 100715
SELECT EXTRACT(DAY_SECOND FROM @dt);     -- 10071530
SELECT EXTRACT(DAY_MICROSECOND FROM @dt);-- 10071530123456
SELECT EXTRACT(HOUR_MINUTE FROM @dt);    -- 715
SELECT EXTRACT(HOUR_SECOND FROM @dt);    -- 71530
SELECT EXTRACT(HOUR_MICROSECOND FROM @dt);-- 71530123456
SELECT EXTRACT(MINUTE_SECOND FROM @dt);  -- 1530
SELECT EXTRACT(MINUTE_MICROSECOND FROM @dt);-- 1530123456
SELECT EXTRACT(SECOND_MICROSECOND FROM @dt);-- 30123456

優勢： 支持組合提取（如 DAY_MICROSECOND 從天到微秒），缺點是輸入較長。

2.3 星期/年間位置：`DAYOFWEEK()`、`DAYOFMONTH()`、`DAYOFYEAR()`


SET @dt = '2008-08-08';
SELECT DAYOFWEEK(@dt);  -- 6 (1=周日)
SELECT DAYOFMONTH(@dt); -- 8
SELECT DAYOFYEAR(@dt);  -- 221

2.4 週相關：`WEEK()`、`WEEKOFYEAR()`、`WEEKDAY()`、`YEARWEEK()`


SELECT WEEK(@dt);          -- 31
SELECT WEEK(@dt, 3);       -- 32 (模式3)
SELECT WEEKOFYEAR(@dt);    -- 32 等同 WEEK(@dt,3)
SELECT WEEKDAY(@dt);       -- 4 (0=周一)
SELECT YEARWEEK(@dt);      -- 200831

2.5 名稱獲取：`DAYNAME()`、`MONTHNAME()`


SELECT DAYNAME(@dt);   -- Friday
SELECT MONTHNAME(@dt); -- August

中文名稱需應用層映射或 ELT() 實現。

2.6 月末日期：`LAST_DAY()`


SELECT LAST_DAY('2008-02-01'); -- 2008-02-29 (閏年)
SELECT LAST_DAY('2008-08-08'); -- 2008-08-31
-- 獲取當月天數：
SELECT DAY(LAST_DAY(NOW()));

三、日期時間計算

3.1 增加間隔：`DATE_ADD()`（推薦統一用此函數）


SET @dt = NOW();
SELECT DATE_ADD(@dt, INTERVAL 1 DAY);
SELECT DATE_ADD(@dt, INTERVAL 1 HOUR);
SELECT DATE_ADD(@dt, INTERVAL 1 MINUTE);
SELECT DATE_ADD(@dt, INTERVAL 1 SECOND);
SELECT DATE_ADD(@dt, INTERVAL 1 WEEK);
SELECT DATE_ADD(@dt, INTERVAL 1 MONTH);
SELECT DATE_ADD(@dt, INTERVAL 1 QUARTER);
SELECT DATE_ADD(@dt, INTERVAL 1 YEAR);
SELECT DATE_ADD(@dt, INTERVAL -1 DAY); -- 減去
-- 複合間隔：
SELECT DATE_ADD(@dt, INTERVAL '01:15:30' HOUR_SECOND);   -- +1時15分30秒
SELECT DATE_ADD(@dt, INTERVAL '1 01:15:30' DAY_SECOND);  -- +1天1時15分30秒

ADDDATE()、ADDTIME() 可用 DATE_ADD() 替代。

3.2 減去間隔：`DATE_SUB()`


SELECT DATE_SUB('1998-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
-- 1997-12-30 22:58:59

SUBDATE()、SUBTIME() 同理用 DATE_SUB() 替代。

3.3 期間計算：`PERIOD_ADD()`、`PERIOD_DIFF()`（較少用）


SELECT PERIOD_ADD(200808, 2);      -- 200810
SELECT PERIOD_ADD(20080808, -2);   -- 20080806
SELECT PERIOD_DIFF(200808, 200801); -- 7

3.4 日期/時間差值：`DATEDIFF()`、`TIMEDIFF()`


SELECT DATEDIFF('2008-08-08', '2008-08-01'); -- 7
SELECT DATEDIFF('2008-08-01', '2008-08-08'); -- -7
SELECT TIMEDIFF('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
-- TIMEDIFF 參數類型必須相同

四、日期時間轉換

4.1 時間↔秒：`TIME_TO_SEC()`、`SEC_TO_TIME()`


SELECT TIME_TO_SEC('01:00:05'); -- 3605
SELECT SEC_TO_TIME(3605);       -- '01:00:05'

4.2 日期↔天數：`TO_DAYS()`、`FROM_DAYS()`


SELECT TO_DAYS('2008-08-08');  -- 733627
SELECT FROM_DAYS(733627);      -- '2008-08-08'

4.3 字符串→日期：`STR_TO_DATE(str, format)`


SELECT STR_TO_DATE('08/09/2008', '%m/%d/%Y');        -- 2008-08-09
SELECT STR_TO_DATE('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

格式符參考手冊，極其靈活。

4.4 日期/時間→字符串：`DATE_FORMAT()`、`TIME_FORMAT()`


SELECT DATE_FORMAT('2008-08-08 22:23:00', '%W %M %Y');      -- Friday August 2008
SELECT DATE_FORMAT('2008-08-08 22:23:01', '%Y%m%d%H%i%s');  -- 20080808222301
SELECT TIME_FORMAT('22:23:01', '%H.%i.%s');                 -- 22.23.01

與 STR_TO_DATE 互為逆操作。

4.5 區域化格式：`GET_FORMAT()`（了解即可）


SELECT GET_FORMAT(DATE, 'USA');     -- '%m.%d.%Y'
SELECT GET_FORMAT(DATETIME, 'ISO'); -- '%Y-%m-%d %H:%i:%s'

4.6 組合日期時間：`MAKEDATE()`、`MAKETIME()`


SELECT MAKEDATE(2001, 31);  -- '2001-01-31'
SELECT MAKETIME(12, 15, 30); -- '12:15:30'

五、時間戳函數

5.1 當前時間戳：`CURRENT_TIMESTAMP`

5.2 Unix 時間戳轉換：`UNIX_TIMESTAMP()`、`FROM_UNIXTIME()`


SELECT UNIX_TIMESTAMP();                    -- 1218290027
SELECT UNIX_TIMESTAMP('2008-08-08');        -- 1218124800
SELECT FROM_UNIXTIME(1218290027);           -- '2008-08-09 21:53:47'
SELECT FROM_UNIXTIME(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

5.3 TIMESTAMP 計算：`TIMESTAMP()`、`TIMESTAMPADD()`、`TIMESTAMPDIFF()`


SELECT TIMESTAMP('2008-08-08');                    -- 2008-08-08 00:00:00
SELECT TIMESTAMP('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
SELECT TIMESTAMPADD(DAY, 1, '2008-08-08 08:00:00');  -- 2008-08-09 08:00:00
SELECT TIMESTAMPDIFF(YEAR, '2002-05-01', '2001-01-01'); -- -1
SELECT TIMESTAMPDIFF(DAY, '2002-05-01', '2001-01-01');  -- -485
-- TIMESTAMPDIFF 比 DATEDIFF 強大，支持任意單位

六、時區轉換：`CONVERT_TZ(dt, from_tz, to_tz)`


SELECT CONVERT_TZ('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00
-- 等價於：
SELECT DATE_ADD('2008-08-08 12:00:00', INTERVAL -8 HOUR);
SELECT TIMESTAMPADD(HOUR, -8, '2008-08-08 12:00:00');