MySQL日期和時間函數彙總

本文基于MySQL8.0

本文介紹MySQL關于日期和時間操作的函數。

日期和時間函數

函數	描述
ADDDATE()	給日期值添加時間值
ADDTIME()	添加time
CONVERT_TZ()	從一個時區轉換為另一個時區
CURDATE()	傳回目前日期
CURRENT_DATE(), CURRENT_DATE	和CURDATE()同義
CURRENT_TIME(), CURRENT_TIME
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP	和NOW() 同義
CURTIME()	傳回目前時間
DATE()	擷取日期或日期時間表達式的日期部分
DATE_ADD()	将時間值（間隔）添加到日期值
DATE_FORMAT()	按指定格式格式化日期
DATE_SUB()	從日期中減去時間值（間隔）
DATEDIFF()	減去兩個日期
DAY()	和 DAYOFMONTH()同義
DAYNAME()	傳回工作日名稱
DAYOFMONTH()	傳回月份的日期 (0-31)
DAYOFWEEK()	根據參數傳回工作日的排序
DAYOFYEAR()	傳回一年中的某一天 (1-366)
EXTRACT()	擷取日期中的一部分
FROM_DAYS()	将一天的序号轉化為日期
FROM_UNIXTIME()	将Unix時間戳格式化為日期
GET_FORMAT()	傳回日期格式字元串
HOUR()	擷取小時
LAST_DAY	傳回參數月份的最後一天
LOCALTIME(), LOCALTIME	和NOW()同義
LOCALTIMESTAMP, LOCALTIMESTAMP()	和 NOW()同義
MAKEDATE()	從年和日建立日期
MAKETIME()	從時、分、秒建立時間
MICROSECOND()	從參數傳回微秒
MINUTE()	傳回參數分鐘
MONTH()	傳回已過日期起的月份
MONTHNAME()	傳回月份名稱
NOW()	傳回目前日期和時間
PERIOD_ADD()	給年-月添加一段時間
PERIOD_DIFF()	傳回一段時間之間的月數
QUARTER()	從日期參數傳回季度
SEC_TO_TIME()	将秒轉換為“hh:mm:ss”格式
SECOND()	傳回秒 (0-59)
STR_TO_DATE()	将字元串轉換為日期
SUBDATE()	當使用三個參數調用時，和DATE_SUB（）同義
SUBTIME()	減去時間
SYSDATE()	傳回函數執行的時間
TIME()	根據傳遞的表達式擷取時間
TIME_FORMAT()	格式為時間
TIME_TO_SEC()	傳回轉換為秒的參數
TIMEDIFF()	時間相減
TIMESTAMP()	對于單個參數，此函數傳回日期或日期時間表達式；對于兩個參數，傳回參數的和
TIMESTAMPADD()	向日期時間表達式添加間隔
TIMESTAMPDIFF()	從日期時間表達式中減去間隔
TO_DAYS()	傳回轉換為天的日期參數
TO_SECONDS()	傳回從0年起轉換為秒的日期或日期時間參數
UNIX_TIMESTAMP()	傳回Unix時間戳
UTC_DATE()	傳回目前UTC日期
UTC_TIME()	傳回目前UTC時間
UTC_TIMESTAMP()	傳回目前UTC日期和時間
WEEK()	傳回星期數
WEEKDAY()	傳回工作日序号
WEEKOFYEAR()	傳回日期（1-53）的月曆周
YEAR()	傳回年
YEARWEEK()	傳回年份和星期

來看一看MySQL常用日期和時間函數的示例。

獲得目前日期時間

• 以“YYYY-MM-DD”或YYYYMMDD格式傳回目前日期，具體取決于函數是在字元串上下文中使用還是在數字上下文中使用。

mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613
           

• CURTIME([fsp])

  以'hh:mm:ss'或hhmmss格式傳回目前時間，具體取決于函數是在字元串上下文中使用還是在數字上下文中使用。該值以會話時區表示。

如果指定的fsp精度是從0到6的小數位數，則指定從0到6的小數精度。

mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026.000000
           

• NOW([fsp])

  以'YYYY-MM-DD hh:MM:ss'或YYYYMMDDhhmmss格式傳回目前日期和時間，具體取決于函數是在字元串上下文中使用還是在數字上下文中使用。該值以會話時區表示。

mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000
           

時間日期格式轉化

同一個日期時間會有多種不同的表示方式，有的時候需要在不同格式之間互相轉換。在MySQL中用的是date_format()函數:

• DATE_FORMAT(date,format):根據格式字元串格式化日期值。

相關的格式如下，除了用于這個函數，格式還可以用于：STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP().

格式
%a	縮寫星期名 (Sun..Sat)
%b	縮寫月名 (Jan..Dec)
%c	月, 數值 (0..12)
%D	帶有英文字首的月中的天 (0th, 1st, 2nd, 3rd, …)
%d	一個月裡面的某一天, 數值 (00..31)
%e	一個月裡面的某一天, 數值 (0..31)
%f	微秒 (000000..999999)
%H	小時 (00..23)
%h	小時 (01..12)
%I
%i	分鐘, 數值 (00..59)
%j	一年中的某一天 (001..366)
%k	小時 (0..23)
%l	小時 (1..12)
%M	月名 (January..December)
%m	月份, 數值 (00..12)
%p	AM or PM
%r	時間, 12 - 小時 (hh:mm:ss AM 或者 PM)
%S	秒 (00..59)
%s
%T	時間, 24 - 小時 (hh:mm:ss)
%W	星期名 (Sunday..Saturday)
%w	周幾 (0=Sunday..6=Saturday)
%Y	年, 數值, 4 個數字
%y	年, 數值, 2 個數字

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'
           

日期時間運算

• DATE_ADD(date,INTERVAL expr unit),

• DATE_SUB(date,INTERVAL expr unit)

  這些函數執行日期運算,來進行日期向前/向後的偏移。date參數指定開始日期或日期時間值。expr是一個表達式，指定從開始日期加上或減去的間隔值。expr被計算為一個字元串；它可以以-開頭表示負間隔。unit是一個關鍵字，訓示表達式應使用的機關。

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
        -> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
        -> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'
           

• DATEDIFF(expr1,expr2)

  DATEDIFF（）傳回expr1−expr2，以從一個日期到另一個日期的天數表示。expr1和expr2是日期或日期和時間表達式。計算中隻使用值的日期部分。

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31
           

參考：

【1】：12.7 Date and Time Functions

【2】： MySQL 獲得目前日期時間 函數

【3】：一文搞定Mysql日期時間函數