備注:測試數(shù)據(jù)庫版本為MySQL 8.0
這個blog我們來聊聊常見的日期時間函數(shù)
mysql的日期時間函數(shù)比oracle更豐富，內(nèi)容也更多，從oracle轉(zhuǎn)過來的多少有些不熟悉，需要慢慢的練習(xí)
| 函數(shù)名 | 函數(shù)用途 | |
|---|---|---|
| ADDDATE() | 增加日期間隔，默認(rèn)為天,功能和語法與DATE_ADD()/DATE_SUB()/SUBDATE()類似 | |
| ADDTIME() | 增加時間間隔 | |
| CURDATE() | 返回當(dāng)前日期，同義詞CURRENT_DATE(), CURRENT_DATE | |
| CURTIME() | 返回當(dāng)前時間，同義詞CURRENT_TIME(), CURRENT_TIME | |
| NOW() | 返回當(dāng)前日期和時間，同義詞CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP LOCALTIME(), LOCALTIME LOCALTIMESTAMP, LOCALTIMESTAMP() | |
| DATE() | 從日期時間類型中截取日期 | |
| DATE_FORMAT() | 格式日期指定 | |
| DATEDIFF() | 返回兩個日期間隔的天數(shù) | |
| DAYNAME() | 返回日期的星期英文,例如:'Saturday' | |
| DAYOFMONTH() | 返回指定日期當(dāng)月的天數(shù)， 同義詞DAY() | |
| DAYOFWEEK() | 返回星期幾，數(shù)值類型，1-7 | |
| DAYOFYEAR() | 返回今天是當(dāng)年的第幾天 | |
| EXTRACT() | 提取日期的部分 | |
| FROM_DAYS() | 與to_days()結(jié)合使用，數(shù)值轉(zhuǎn)換成日期 | |
| FROM_UNIXTIME() | 與UNIX_TIMESTAMP()結(jié)合使用，數(shù)值轉(zhuǎn)換成日期時間類型 | |
| GET_FORMAT() | 返回日期格式的字符串 | |
| HOUR() | 提取小時 | |
| LAST_DAY | 返回當(dāng)月最后一天 | |
| MAKEDATE() | 根據(jù)年份及當(dāng)年的天數(shù)，返回日期 | |
| MAKETIME() | 根據(jù)時分秒，返回時間 | |
| MICROSECOND() | 從表達式中返回微妙 | |
| MINUTE() | 從時間中返回分鐘 | |
| MONTH() | 從日期中返回月份-數(shù)值 | |
| MONTHNAME() | 從時間類型中返回月份-英文 | |
| PERIOD_ADD() | 給年月類型增加月份 | |
| PERIOD_DIFF() | 返回兩個年月間隔的月份 | |
| QUARTER() | 返回年份中的季度 取值1-4 | |
| SEC_TO_TIME() | 將數(shù)值類型的秒轉(zhuǎn)換為時分秒 | |
| SECOND() | 返回時間中的秒 | |
| STR_TO_DATE() | 將字符串轉(zhuǎn)為日期時間格式 | |
| SUBTIME() | SUBTIME(expr1,expr2) 返回expr1和expr2的差 | |
| SYSDATE() | 返回當(dāng)前時間 | |
| TIME() | 返回日期時間格式中的時間 | |
| TIME_FORMAT() | 指定時間格式 | |
| TIME_TO_SEC() | 將時間類型轉(zhuǎn)換為秒，與SEC_TO_TIME相反 | |
| TIMEDIFF() | 返回兩個時間的差，結(jié)果為時間類型 | |
| TIMESTAMP() | 返回日期時間類型值 | |
| TIMESTAMPADD() | 日期時間類型增加間隔 | |
| TIMESTAMPDIFF() | 求兩個日期時間的間隔 | |
| TO_DAYS() | 返回日期對應(yīng)的天數(shù)，0000-00-01為第一天 | |
| TO_SECONDS() | 返回日期對應(yīng)的秒，0000-00-00 00:00:01為第一秒 | |
| UNIX_TIMESTAMP() | 將時間轉(zhuǎn)成數(shù)字，與FROM_UNIXTIME相互對應(yīng) | |
| WEEK() | 返回本年的星期數(shù) | |
| WEEKDAY() | 返回數(shù)值的星期數(shù) | |
| WEEKOFYEAR() | 返回本年的星期數(shù) | |
| YEAR() | 返回年份 | |
| YEARWEEK() | 返回星期數(shù) |
1.ADDDATE
語法:ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
用于給時間類型增加時間間隔，默認(rèn)為天
--通過interval增加
mysql> SELECT ADDDATE('2020-01-01', INTERVAL 31 DAY);
+----------------------------------------+
| ADDDATE('2020-01-01', INTERVAL 31 DAY) |
+----------------------------------------+
| 2020-02-01 |
+----------------------------------------+
1 row in set (0.00 sec)
--直接通過數(shù)字進行增加
mysql> SELECT ADDDATE('2020-01-01',31);
+--------------------------+
| ADDDATE('2020-01-01',31) |
+--------------------------+
| 2020-02-01 |
+--------------------------+
1 row in set (0.00 sec)
--有時分秒的也可以進行增加
mysql> SELECT ADDDATE('2020-01-01 09:00:00',31);
+-----------------------------------+
| ADDDATE('2020-01-01 09:00:00',31) |
+-----------------------------------+
| 2020-02-01 09:00:00 |
+-----------------------------------+
1 row in set (0.00 sec)
--增加1年
mysql> SELECT ADDDATE('2020-01-01 09:00:00',INTERVAL 1 YEAR);
+------------------------------------------------+
| ADDDATE('2020-01-01 09:00:00',INTERVAL 1 YEAR) |
+------------------------------------------------+
| 2021-01-01 09:00:00 |
+------------------------------------------------+
1 row in set (0.00 sec)
--增加1月
mysql> SELECT ADDDATE('2020-01-01 09:00:00',INTERVAL 1 MONTH);
+-------------------------------------------------+
| ADDDATE('2020-01-01 09:00:00',INTERVAL 1 MONTH) |
+-------------------------------------------------+
| 2020-02-01 09:00:00 |
+-------------------------------------------------+
1 row in set (0.00 sec)
--增加1天
mysql> SELECT ADDDATE('2020-01-01 09:00:00',INTERVAL 1 DAY);
+-----------------------------------------------+
| ADDDATE('2020-01-01 09:00:00',INTERVAL 1 DAY) |
+-----------------------------------------------+
| 2020-01-02 09:00:00 |
+-----------------------------------------------+
1 row in set (0.00 sec)
--增加1小時
mysql> SELECT ADDDATE('2020-01-01 09:00:00',INTERVAL 1 DAY_HOUR);
+----------------------------------------------------+
| ADDDATE('2020-01-01 09:00:00',INTERVAL 1 DAY_HOUR) |
+----------------------------------------------------+
| 2020-01-01 10:00:00 |
+----------------------------------------------------+
1 row in set (0.00 sec)
--增加1分鐘
mysql> SELECT ADDDATE('2020-01-01 09:00:00',INTERVAL 1 MINUTE);
+--------------------------------------------------+
| ADDDATE('2020-01-01 09:00:00',INTERVAL 1 MINUTE) |
+--------------------------------------------------+
| 2020-01-01 09:01:00 |
+--------------------------------------------------+
1 row in set (0.00 sec)
--增加1秒
mysql> SELECT ADDDATE('2020-01-01 09:00:00',INTERVAL 1 DAY_SECOND);
+------------------------------------------------------+
| ADDDATE('2020-01-01 09:00:00',INTERVAL 1 DAY_SECOND) |
+------------------------------------------------------+
| 2020-01-01 09:00:01 |
+------------------------------------------------------+
1 row in set (0.00 sec)
--增加1分1秒
mysql> SELECT ADDDATE('2020-01-01 09:00:00',INTERVAL '1:1' MINUTE_SECOND);
+-------------------------------------------------------------+
| ADDDATE('2020-01-01 09:00:00',INTERVAL '1:1' MINUTE_SECOND) |
+-------------------------------------------------------------+
| 2020-01-01 09:01:01 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
2.ADDTIME
語法:ADDTIME(expr1,expr2)
給日期列增加一個時間，可以是天/時/分/秒/微妙
--增加1小時、30分、10秒
mysql> SELECT ADDTIME('2020-01-01 09:00:00','01:30:10');
+-------------------------------------------+
| ADDTIME('2020-01-01 09:00:00','01:30:10') |
+-------------------------------------------+
| 2020-01-01 10:30:10 |
+-------------------------------------------+
1 row in set (0.00 sec)
--增加1天、1小時、30分、10秒
mysql> SELECT ADDTIME('2020-01-01 09:00:00','1 01:30:10');
+---------------------------------------------+
| ADDTIME('2020-01-01 09:00:00','1 01:30:10') |
+---------------------------------------------+
| 2020-01-02 10:30:10 |
+---------------------------------------------+
1 row in set (0.00 sec)
--如果剛好是月底，增加1天后，會自動跳到下個月
mysql> SELECT ADDTIME('2020-01-31 09:00:00','1 01:30:10');
+---------------------------------------------+
| ADDTIME('2020-01-31 09:00:00','1 01:30:10') |
+---------------------------------------------+
| 2020-02-01 10:30:10 |
+---------------------------------------------+
1 row in set (0.00 sec)
--給時間加上50天
mysql> SELECT ADDTIME('2020-01-31 09:00:00','50 01:30:10');
+----------------------------------------------+
| ADDTIME('2020-01-31 09:00:00','50 01:30:10') |
+----------------------------------------------+
| 2020-03-06 07:59:59 |
+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
--給時間加上1秒2微妙
mysql> SELECT ADDTIME('2020-01-31 09:00:00.999999','0:0:1.000002');
+------------------------------------------------------+
| ADDTIME('2020-01-31 09:00:00.999999','0:0:1.000002') |
+------------------------------------------------------+
| 2020-01-31 09:00:02.000001 |
+------------------------------------------------------+
1 row in set (0.00 sec)
3.CURDATE()
返回一個'YYYY-MM-DD' 或者 YYYYMMDD 格式的日期
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-05-22 |
+------------+
1 row in set (0.00 sec)
mysql> select curdate()+0;
+-------------+
| curdate()+0 |
+-------------+
| 20200522 |
+-------------+
1 row in set (0.00 sec)
4.CURTIME()
返回'hh:mm:ss' 或 hhmmss 格式時間
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:29:27 |
+-----------+
1 row in set (0.00 sec)
mysql> select curtime()+0;
+-------------+
| curtime()+0 |
+-------------+
| 152930 |
+-------------+
1 row in set (0.00 sec)
5.NOW()
返回當(dāng)前日期和時間，返回格式 'YYYY-MM-DD hh:mm:ss' 或 YYYYMMDDhhmmss
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-05-22 15:33:27 |
+---------------------+
1 row in set (0.00 sec)
mysql> select now()+0
-> ;
+----------------+
| now()+0 |
+----------------+
| 20200522153333 |
+----------------+
1 row in set (0.00 sec)
--now()是返回查詢開始的時間，即便有sleep也沒用
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2020-05-22 15:34:00 | 0 | 2020-05-22 15:34:00 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)
--sysdate返回的是查詢當(dāng)時的時間
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2020-05-22 15:34:12 | 0 | 2020-05-22 15:34:14 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)
6.DATE()
從日期時間類型中截取日期類型
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-05-22 15:39:03 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2020-05-22 |
+-------------+
1 row in set (0.00 sec)
mysql> select date('2020-05-22 23:59:59');
+-----------------------------+
| date('2020-05-22 23:59:59') |
+-----------------------------+
| 2020-05-22 |
+-----------------------------+
1 row in set (0.00 sec)
7.DATE_FORMAT()
格式日期指定，真的很豐富
| 格式 | 描述 |
|---|---|
| %a | 縮寫星期名 |
| %b | 縮寫月名 |
| %c | 月，數(shù)值 |
| %D | 帶有英文前綴的月中的天 |
| %d | 月的天，數(shù)值(00-31) |
| %e | 月的天，數(shù)值(0-31) |
| %f | 微秒 |
| %H | 小時 (00-23) |
| %h | 小時 (01-12) |
| %I | 小時 (01-12) |
| %i | 分鐘，數(shù)值(00-59) |
| %j | 年的天 (001-366) |
| %k | 小時 (0-23) |
| %l | 小時 (1-12) |
| %M | 月名 |
| %m | 月，數(shù)值(00-12) |
| %p | AM 或 PM |
| %r | 時間，12-小時（hh:mm:ss AM 或 PM） |
| %S | 秒(00-59) |
| %s | 秒(00-59) |
| %T | 時間, 24-小時 (hh:mm:ss) |
| %U | 周 (00-53) 星期日是一周的第一天 |
| %u | 周 (00-53) 星期一是一周的第一天 |
| %V | 周 (01-53) 星期日是一周的第一天，與 %X 使用 |
| %v | 周 (01-53) 星期一是一周的第一天，與 %x 使用 |
| %W | 星期名 |
| %w | 周的天 （0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，與 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，與 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
| %% | % 字符 |
| %x | 上述之外的，用x表示 |
內(nèi)容太多，我挑幾個常用的來練習(xí)一下:
mysql> -- 顯示年月日時分秒 24小時制
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %H:%i:%s');
+---------------------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %H:%i:%s') |
+---------------------------------------------------------+
| 2020-05-22 13:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 顯示年月日時分秒 12小時制
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %h:%i:%s %p');
+------------------------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%Y-%m-%d %h:%i:%s %p') |
+------------------------------------------------------------+
| 2020-05-22 01:00:00 PM |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 顯示英文月名
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%M');
+------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%M') |
+------------------------------------------+
| May |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 顯示英文月名簡稱
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%b');
+------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%b') |
+------------------------------------------+
| May |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 顯示星期英文名
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%W');
+------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%W') |
+------------------------------------------+
| Friday |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 用數(shù)值顯示今天周幾，0代表周日
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%w');
+------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%w') |
+------------------------------------------+
| 5 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 用數(shù)值顯示今天是本年的第幾周，星期一是周的第一天
mysql> SELECT DATE_FORMAT('2020-05-22 13:00:00', '%x-%v');
+---------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00', '%x-%v') |
+---------------------------------------------+
| 2020-21 |
+---------------------------------------------+
1 row in set (0.00 sec)
8.DATEDIFF()
返回兩個時間間隔的天數(shù)
語法:DATEDIFF(expr1,expr2)
--計算2個時間間隔的天數(shù)
mysql> select datediff('2020-05-22','2020-05-01');
+-------------------------------------+
| datediff('2020-05-22','2020-05-01') |
+-------------------------------------+
| 21 |
+-------------------------------------+
1 row in set (0.00 sec)
--有時分秒的話，都截取了，只比較日期部分
mysql>
mysql> select datediff('2020-05-22','2020-05-01 23:59:59');
+----------------------------------------------+
| datediff('2020-05-22','2020-05-01 23:59:59') |
+----------------------------------------------+
| 21 |
+----------------------------------------------+
1 row in set (0.00 sec)
9.dayname()
返回指定日期的星期英文
mysql> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Friday |
+----------------+
1 row in set (0.00 sec)
10.DAYOFMONTH()
返回指定日期當(dāng)月的天數(shù)，數(shù)字類型，返回值1-31
mysql> select DAYOFMONTH('2020-05-22');
+--------------------------+
| DAYOFMONTH('2020-05-22') |
+--------------------------+
| 22 |
+--------------------------+
1 row in set (0.00 sec)
mysql> select DAYOFMONTH('2020-05-02');
+--------------------------+
| DAYOFMONTH('2020-05-02') |
+--------------------------+
| 2 |
+--------------------------+
1 row in set (0.00 sec)
11.DAYOFWEEK()
返回星期幾，數(shù)值類型，1-7
date (1 = Sunday, 2 = Monday, …, 7 = Saturday).
mysql> select DAYOFWEEK('2020-05-22');
+-------------------------+
| DAYOFWEEK('2020-05-22') |
+-------------------------+
| 6 |
+-------------------------+
1 row in set (0.00 sec)
12.DAYOFYEAR()
返回今天是當(dāng)年的第幾天 (1-366)
mysql> select DAYOFYEAR('2020-05-22');
+-------------------------+
| DAYOFYEAR('2020-05-22') |
+-------------------------+
| 143 |
+-------------------------+
1 row in set (0.00 sec)
13.EXTRACT()
提取日期的部分
語法:EXTRACT(unit FROM date)
mysql> -- 提取年
mysql> SELECT EXTRACT(YEAR FROM '2020-05-22');
+---------------------------------+
| EXTRACT(YEAR FROM '2020-05-22') |
+---------------------------------+
| 2020 |
+---------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 提取月
mysql> SELECT EXTRACT(MONTH FROM '2020-05-22');
+----------------------------------+
| EXTRACT(MONTH FROM '2020-05-22') |
+----------------------------------+
| 5 |
+----------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 提取日
mysql> SELECT EXTRACT(DAY FROM '2020-05-22');
+--------------------------------+
| EXTRACT(DAY FROM '2020-05-22') |
+--------------------------------+
| 22 |
+--------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 提取小時
mysql> SELECT EXTRACT(HOUR FROM '2020-05-22 13:02:01');
+------------------------------------------+
| EXTRACT(HOUR FROM '2020-05-22 13:02:01') |
+------------------------------------------+
| 13 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 提取分鐘
mysql> SELECT EXTRACT(MINUTE FROM '2020-05-22 13:02:01');
+--------------------------------------------+
| EXTRACT(MINUTE FROM '2020-05-22 13:02:01') |
+--------------------------------------------+
| 2 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 提取秒
mysql> SELECT EXTRACT(SECOND FROM '2020-05-22 13:02:01');
+--------------------------------------------+
| EXTRACT(SECOND FROM '2020-05-22 13:02:01') |
+--------------------------------------------+
| 1 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 提取毫秒
mysql> SELECT EXTRACT(MICROSECOND FROM '2020-05-22 13:00:00.000123');
+--------------------------------------------------------+
| EXTRACT(MICROSECOND FROM '2020-05-22 13:00:00.000123') |
+--------------------------------------------------------+
| 123 |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- 也可以組合
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2020-05-22');
+---------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2020-05-22') |
+---------------------------------------+
| 202005 |
+---------------------------------------+
1 row in set (0.00 sec)
14.FROM_DAYS()
與to_days()結(jié)合使用，數(shù)值轉(zhuǎn)換成日期
mysql> select to_days('2020-05-22');
+-----------------------+
| to_days('2020-05-22') |
+-----------------------+
| 737932 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select from_days(737932);
+-------------------+
| from_days(737932) |
+-------------------+
| 2020-05-22 |
+-------------------+
1 row in set (0.00 sec)
15.FROM_UNIXTIME()
與UNIX_TIMESTAMP()結(jié)合使用，數(shù)值轉(zhuǎn)換成日期時間類型
mysql> select UNIX_TIMESTAMP('2020-05-22 13:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-05-22 13:00:00') |
+---------------------------------------+
| 1590123600 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select UNIX_TIMESTAMP('2020-05-22');
+------------------------------+
| UNIX_TIMESTAMP('2020-05-22') |
+------------------------------+
| 1590076800 |
+------------------------------+
1 row in set (0.00 sec)
mysql> select FROM_UNIXTIME(1590123600);
+---------------------------+
| FROM_UNIXTIME(1590123600) |
+---------------------------+
| 2020-05-22 13:00:00 |
+---------------------------+
1 row in set (0.00 sec)
16.GET_FORMAT()
返回日期格式的字符串
| 語句 | 格式 |
|---|---|
| GET_FORMAT(DATE,'USA') | '%m.%d.%Y' |
| GET_FORMAT(DATE,'JIS') | '%Y-%m-%d' |
| GET_FORMAT(DATE,'ISO') | '%Y-%m-%d' |
| GET_FORMAT(DATE,'EUR') | '%d.%m.%Y' |
| GET_FORMAT(DATE,'INTERNAL') | '%Y%m%d' |
| GET_FORMAT(DATETIME,'USA') | '%Y-%m-%d %H.%i.%s' |
| GET_FORMAT(DATETIME,'JIS') | '%Y-%m-%d %H:%i:%s' |
| GET_FORMAT(DATETIME,'ISO') | '%Y-%m-%d %H:%i:%s' |
| GET_FORMAT(DATETIME,'EUR') | '%Y-%m-%d %H.%i.%s' |
| GET_FORMAT(DATETIME,'INTERNAL') | '%Y%m%d%H%i%s' |
| GET_FORMAT(TIME,'USA') | '%h:%i:%s %p' |
| GET_FORMAT(TIME,'JIS') | '%H:%i:%s' |
| GET_FORMAT(TIME,'ISO') | '%H:%i:%s' |
| GET_FORMAT(TIME,'EUR') | '%H.%i.%s' |
| GET_FORMAT(TIME,'INTERNAL') | '%H%i%s' |
需要與DATE_FORMAT函數(shù)結(jié)合使用，標(biāo)準(zhǔn)輸出的話，看起來還蠻方便的
mysql> select DATE_FORMAT('2020-05-22 13:00:00',GET_FORMAT(DATE,'ISO'));
+-----------------------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00',GET_FORMAT(DATE,'ISO')) |
+-----------------------------------------------------------+
| 2020-05-22 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> select DATE_FORMAT('2020-05-22 13:00:00',GET_FORMAT(DATETIME,'ISO'));
+---------------------------------------------------------------+
| DATE_FORMAT('2020-05-22 13:00:00',GET_FORMAT(DATETIME,'ISO')) |
+---------------------------------------------------------------+
| 2020-05-22 13:00:00 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
17.HOUR()
提取小時，可以超過23
mysql> SELECT HOUR('10:05:03');
+------------------+
| HOUR('10:05:03') |
+------------------+
| 10 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT HOUR('272:59:59');
+-------------------+
| HOUR('272:59:59') |
+-------------------+
| 272 |
+-------------------+
1 row in set (0.00 sec)
18.LAST_DAY()
返回當(dāng)月的最后一天，如果日期錯誤，返回null
mysql> SELECT LAST_DAY('2020-05-22');
+------------------------+
| LAST_DAY('2020-05-22') |
+------------------------+
| 2020-05-31 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT LAST_DAY('2020-05-32');
+------------------------+
| LAST_DAY('2020-05-32') |
+------------------------+
| NULL |
+------------------------+
1 row in set, 1 warning (0.00 sec)
19.MAKEDATE
根據(jù)年份和當(dāng)年的第幾天，返回日期
語法:MAKEDATE(year,dayofyear)
mysql> select makedate(2020,100);
+--------------------+
| makedate(2020,100) |
+--------------------+
| 2020-04-09 |
+--------------------+
1 row in set (0.00 sec)
mysql> select makedate(2020,0);
+------------------+
| makedate(2020,0) |
+------------------+
| NULL |
+------------------+
1 row in set (0.00 sec)
mysql> select makedate(2020,null);
+---------------------+
| makedate(2020,null) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.00 sec)
20.MAKETIME
根據(jù)時分秒構(gòu)造時間
語法:MAKETIME(hour,minute,second)
mysql> SELECT MAKETIME(12,15,30);
+--------------------+
| MAKETIME(12,15,30) |
+--------------------+
| 12:15:30 |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> SELECT MAKETIME(12,15,0);
+-------------------+
| MAKETIME(12,15,0) |
+-------------------+
| 12:15:00 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(12,15,null);
+----------------------+
| MAKETIME(12,15,null) |
+----------------------+
| NULL |
+----------------------+
1 row in set (0.00 sec)
21.MICROSECOND()
從表達式中返回微妙
mysql> SELECT MICROSECOND('12:00:00.123456');
+--------------------------------+
| MICROSECOND('12:00:00.123456') |
+--------------------------------+
| 123456 |
+--------------------------------+
1 row in set (0.02 sec)
mysql> SELECT MICROSECOND('2019-12-31 23:59:59.000010');
+-------------------------------------------+
| MICROSECOND('2019-12-31 23:59:59.000010') |
+-------------------------------------------+
| 10 |
+-------------------------------------------+
1 row in set (0.00 sec)
22.MINUTE()
從時間類型中返回分
mysql> SELECT MINUTE('2020-05-22 20:05:03');
+-------------------------------+
| MINUTE('2020-05-22 20:05:03') |
+-------------------------------+
| 5 |
+-------------------------------+
1 row in set (0.01 sec)
23.MONTH()
從時間類型中返回月份-數(shù)值類型
mysql> SELECT MONTH('2020-05-22 20:05:03');
+------------------------------+
| MONTH('2020-05-22 20:05:03') |
+------------------------------+
| 5 |
+------------------------------+
1 row in set (0.00 sec)
24.MONTHNAME()
從時間類型中返回月份-英文
mysql> SELECT MONTHNAME('2020-05-22 20:05:03');
+----------------------------------+
| MONTHNAME('2020-05-22 20:05:03') |
+----------------------------------+
| May |
+----------------------------------+
1 row in set (0.05 sec)
25.PERIOD_ADD（）
語法:PERIOD_ADD(P,N)
給P增加N個月份，P的格式Y(jié)YMM or YYYYMM,返回格式
mysql> select PERIOD_ADD(2005,3);
+--------------------+
| PERIOD_ADD(2005,3) |
+--------------------+
| 202008 |
+--------------------+
1 row in set (0.02 sec)
mysql> select PERIOD_ADD(202005,3);
+----------------------+
| PERIOD_ADD(202005,3) |
+----------------------+
| 202008 |
+----------------------+
1 row in set (0.00 sec)
mysql> select PERIOD_ADD(202005,15);
+-----------------------+
| PERIOD_ADD(202005,15) |
+-----------------------+
| 202108 |
+-----------------------+
1 row in set (0.00 sec)
26.PERIOD_DIFF()
返回兩個年月間隔的月份
語法:PERIOD_DIFF(P1,P2)
mysql> select PERIOD_DIFF(202108,202005);
+----------------------------+
| PERIOD_DIFF(202108,202005) |
+----------------------------+
| 15 |
+----------------------------+
1 row in set (0.02 sec)
mysql> select PERIOD_DIFF(202108,202405);
+----------------------------+
| PERIOD_DIFF(202108,202405) |
+----------------------------+
| -33 |
+----------------------------+
1 row in set (0.00 sec)
27.QUARTER()
返回年份中的季度 取值1-4
語法:QUARTER(date)
mysql> select QUARTER('2020-05-22');
+-----------------------+
| QUARTER('2020-05-22') |
+-----------------------+
| 2 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select QUARTER('2020-01-22');
+-----------------------+
| QUARTER('2020-01-22') |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select QUARTER('2020-08-22');
+-----------------------+
| QUARTER('2020-08-22') |
+-----------------------+
| 3 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select QUARTER('2020-12-22');
+-----------------------+
| QUARTER('2020-12-22') |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.00 sec)
28.SEC_TO_TIME()
將數(shù)值類型的秒轉(zhuǎn)換為時分秒
mysql> select SEC_TO_TIME(61);
+-----------------+
| SEC_TO_TIME(61) |
+-----------------+
| 00:01:01 |
+-----------------+
1 row in set (0.04 sec)
mysql> select SEC_TO_TIME(3661);
+-------------------+
| SEC_TO_TIME(3661) |
+-------------------+
| 01:01:01 |
+-------------------+
1 row in set (0.00 sec)
mysql> select SEC_TO_TIME(36610);
+--------------------+
| SEC_TO_TIME(36610) |
+--------------------+
| 10:10:10 |
+--------------------+
1 row in set (0.00 sec)
29.SECOND()
返回時間中的秒
mysql> select SECOND('2020-05-22 23:50:59');
+-------------------------------+
| SECOND('2020-05-22 23:50:59') |
+-------------------------------+
| 59 |
+-------------------------------+
1 row in set (0.00 sec)
30.STR_TO_DATE()
語法:STR_TO_DATE(str,format)
將字符串轉(zhuǎn)為日期時間格式
-- 這樣隨意的格式就是字符串，可以轉(zhuǎn)為日期時間類型
mysql> select STR_TO_DATE('20200522 23:50:59','%Y%m%d %H:%i:%s');
+----------------------------------------------------+
| STR_TO_DATE('20200522 23:50:59','%Y%m%d %H:%i:%s') |
+----------------------------------------------------+
| 2020-05-22 23:50:59 |
+----------------------------------------------------+
1 row in set (0.00 sec)
-- 這樣標(biāo)準(zhǔn)的格式被mysql默認(rèn)為時間類型了，不能再轉(zhuǎn)了
mysql> select STR_TO_DATE('2020-05-22 23:50:59','%Y%m%d %H:%i:%s');
+------------------------------------------------------+
| STR_TO_DATE('2020-05-22 23:50:59','%Y%m%d %H:%i:%s') |
+------------------------------------------------------+
| NULL |
+------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
31.SUBTIME()
語法:SUBTIME(expr1,expr2)
返回expr1和expr2的差，返回依舊是日期時間類型
-- expr2只能是時間類型 不能是日期類型
mysql> select SUBTIME('2020-01-01 00:00:00','2020-05-22 23:50:59');
+------------------------------------------------------+
| SUBTIME('2020-01-01 00:00:00','2020-05-22 23:50:59') |
+------------------------------------------------------+
| NULL |
+------------------------------------------------------+
1 row in set (0.00 sec)
-- 日期時間減去時間，得到日期時間
mysql>
mysql> select SUBTIME('2020-01-01 00:00:00','23:50:59');
+-------------------------------------------+
| SUBTIME('2020-01-01 00:00:00','23:50:59') |
+-------------------------------------------+
| 2019-12-31 00:09:01 |
+-------------------------------------------+
1 row in set (0.00 sec)
--時間減去時間，得到的依舊是時間
mysql>
mysql> select SUBTIME('23:50:59','01:01:01');
+--------------------------------+
| SUBTIME('23:50:59','01:01:01') |
+--------------------------------+
| 22:49:58 |
+--------------------------------+
1 row in set (0.00 sec)
32.SYSDATE()
返回當(dāng)前時間,參考前面的NOW()
33.TIME()
返回日期時間格式中的時間
mysql> select TIME('2020-05-22 23:50:59');
+-----------------------------+
| TIME('2020-05-22 23:50:59') |
+-----------------------------+
| 23:50:59 |
+-----------------------------+
1 row in set (0.02 sec)
mysql> select TIME('2020-05-22 23:50:59.000123');
+------------------------------------+
| TIME('2020-05-22 23:50:59.000123') |
+------------------------------------+
| 23:50:59.000123 |
+------------------------------------+
1 row in set (0.00 sec)
34.TIME_FORMAT()
語法:TIME_FORMAT(time,format)
指定時間格式
mysql> select TIME_FORMAT('23:50:59','%H-%i-%s');
+------------------------------------+
| TIME_FORMAT('23:50:59','%H-%i-%s') |
+------------------------------------+
| 23-50-59 |
+------------------------------------+
1 row in set (0.00 sec)
35.TIME_TO_SEC()
將時間類型轉(zhuǎn)換為秒，與SEC_TO_TIME相反
mysql> select TIME_TO_SEC('23:50:59');
+-------------------------+
| TIME_TO_SEC('23:50:59') |
+-------------------------+
| 85859 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select SEC_TO_TIME(85859);
+--------------------+
| SEC_TO_TIME(85859) |
+--------------------+
| 23:50:59 |
+--------------------+
1 row in set (0.00 sec)
36.TIMEDIFF()
語法:TIMEDIFF(expr1,expr2)
返回expr1和expr2的差，返回值認(rèn)為時間類型
mysql> select timediff('2020-05-22 23:50:59.000001','2020-05-22 23:50:59.000003');
+---------------------------------------------------------------------+
| timediff('2020-05-22 23:50:59.000001','2020-05-22 23:50:59.000003') |
+---------------------------------------------------------------------+
| -00:00:00.000002 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
37.TIMESTAMP()
語法:TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
如果有只1個參數(shù)，返回日期時間類型
如果有2個參數(shù)，expr1+expr2 然后返回日期時間類型
mysql> select timestamp('2020-05-22 23:50:59');
+----------------------------------+
| timestamp('2020-05-22 23:50:59') |
+----------------------------------+
| 2020-05-22 23:50:59 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select timestamp('2020-05-22');
+-------------------------+
| timestamp('2020-05-22') |
+-------------------------+
| 2020-05-22 00:00:00 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select timestamp('2020-05-22 23:50:59','00:10:01');
+---------------------------------------------+
| timestamp('2020-05-22 23:50:59','00:10:01') |
+---------------------------------------------+
| 2020-05-23 00:01:00 |
+---------------------------------------------+
1 row in set (0.00 sec)
38.TIMESTAMPADD()
語法:TIMESTAMPADD(unit,interval,datetime_expr)
日期時間類型增加間隔
MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
select TIMESTAMPADD(MICROSECOND,1,'2020-05-22 23:50:59');
select TIMESTAMPADD(SECOND,1,'2020-05-22 23:50:59');
select TIMESTAMPADD(MINUTE,1,'2020-05-22 23:50:59');
select TIMESTAMPADD(HOUR,1,'2020-05-22 23:50:59');
select TIMESTAMPADD(DAY,1,'2020-05-22 23:50:59');
select TIMESTAMPADD(WEEK,1,'2020-05-22 23:50:59');
select TIMESTAMPADD(MONTH,1,'2020-05-22 23:50:59');
select TIMESTAMPADD(QUARTER,1,'2020-05-22 23:50:59');
select TIMESTAMPADD(YEAR,1,'2020-05-22 23:50:59');
mysql> select TIMESTAMPADD(MICROSECOND,1,'2020-05-22 23:50:59');
+---------------------------------------------------+
| TIMESTAMPADD(MICROSECOND,1,'2020-05-22 23:50:59') |
+---------------------------------------------------+
| 2020-05-22 23:50:59.000001 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select TIMESTAMPADD(SECOND,1,'2020-05-22 23:50:59');
+----------------------------------------------+
| TIMESTAMPADD(SECOND,1,'2020-05-22 23:50:59') |
+----------------------------------------------+
| 2020-05-22 23:51:00 |
+----------------------------------------------+
1 row in set (0.02 sec)
mysql> select TIMESTAMPADD(MINUTE,1,'2020-05-22 23:50:59');
+----------------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2020-05-22 23:50:59') |
+----------------------------------------------+
| 2020-05-22 23:51:59 |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql> select TIMESTAMPADD(HOUR,1,'2020-05-22 23:50:59');
+--------------------------------------------+
| TIMESTAMPADD(HOUR,1,'2020-05-22 23:50:59') |
+--------------------------------------------+
| 2020-05-23 00:50:59 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select TIMESTAMPADD(DAY,1,'2020-05-22 23:50:59');
+-------------------------------------------+
| TIMESTAMPADD(DAY,1,'2020-05-22 23:50:59') |
+-------------------------------------------+
| 2020-05-23 23:50:59 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select TIMESTAMPADD(WEEK,1,'2020-05-22 23:50:59');
+--------------------------------------------+
| TIMESTAMPADD(WEEK,1,'2020-05-22 23:50:59') |
+--------------------------------------------+
| 2020-05-29 23:50:59 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select TIMESTAMPADD(MONTH,1,'2020-05-22 23:50:59');
+---------------------------------------------+
| TIMESTAMPADD(MONTH,1,'2020-05-22 23:50:59') |
+---------------------------------------------+
| 2020-06-22 23:50:59 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select TIMESTAMPADD(QUARTER,1,'2020-05-22 23:50:59');
+-----------------------------------------------+
| TIMESTAMPADD(QUARTER,1,'2020-05-22 23:50:59') |
+-----------------------------------------------+
| 2020-08-22 23:50:59 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select TIMESTAMPADD(YEAR,1,'2020-05-22 23:50:59');
+--------------------------------------------+
| TIMESTAMPADD(YEAR,1,'2020-05-22 23:50:59') |
+--------------------------------------------+
| 2021-05-22 23:50:59 |
+--------------------------------------------+
39.TIMESTAMPDIFF()
語法:TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
求兩個日期時間的間隔
MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
-- 求2個時間間隔的月份
mysql> SELECT TIMESTAMPDIFF(MONTH,'2020-02-01','2020-05-01');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2020-02-01','2020-05-01') |
+------------------------------------------------+
| 3 |
+------------------------------------------------+
1 row in set (0.00 sec)
-- 默認(rèn)向下取整，沒有帶小數(shù)
mysql> SELECT TIMESTAMPDIFF(MONTH,'2020-02-01','2020-05-02');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2020-02-01','2020-05-02') |
+------------------------------------------------+
| 3 |
+------------------------------------------------+
1 row in set (0.00 sec)
40.TO_DAYS()
返回日期對應(yīng)的天數(shù)，0000-00-01為第一天
mysql> SELECT TO_DAYS('0000-01-01');
+-----------------------+
| TO_DAYS('0000-01-01') |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_DAYS('0001-01-01');
+-----------------------+
| TO_DAYS('0001-01-01') |
+-----------------------+
| 366 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_DAYS('2020-01-01');
+-----------------------+
| TO_DAYS('2020-01-01') |
+-----------------------+
| 737790 |
+-----------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT TO_DAYS('20-01-01');
+---------------------+
| TO_DAYS('20-01-01') |
+---------------------+
| 737790 |
+---------------------+
1 row in set (0.00 sec)
41.TO_SECONDS()
返回日期對應(yīng)的秒，0000-00-00 00:00:01為第一秒
mysql> SELECT TO_SECONDS('0000-01-01 00:00:01');
+-----------------------------------+
| TO_SECONDS('0000-01-01 00:00:01') |
+-----------------------------------+
| 86401 |
+-----------------------------------+
1 row in set (0.00 sec)
42.UNIX_TIMESTAMP()
將時間轉(zhuǎn)成數(shù)字，與FROM_UNIXTIME相互對應(yīng)
mysql> select UNIX_TIMESTAMP('2020-05-22 23:50:59');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-05-22 23:50:59') |
+---------------------------------------+
| 1590162659 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select FROM_UNIXTIME(1590162659);
+---------------------------+
| FROM_UNIXTIME(1590162659) |
+---------------------------+
| 2020-05-22 23:50:59 |
+---------------------------+
1 row in set (0.02 sec)
43.WEEK()
返回本年的星期數(shù)
[圖片上傳中...(image.png-3aa5e3-1590162278137-0)]
-- 今天是本年的第20周，返回20
mysql> select week('2020-05-22');
+--------------------+
| week('2020-05-22') |
+--------------------+
| 20 |
+--------------------+
1 row in set (0.02 sec)
-- 0代表星期天為一周的開始
mysql> select week('2020-05-22',0);
+----------------------+
| week('2020-05-22',0) |
+----------------------+
| 20 |
+----------------------+
1 row in set (0.00 sec)
-- 1代表星期1位一周的開始
mysql> select week('2020-05-22',1);
+----------------------+
| week('2020-05-22',1) |
+----------------------+
| 21 |
+----------------------+
1 row in set (0.00 sec)
44.WEEKDAY()
返回數(shù)值的星期數(shù)
0 = Monday, 1 = Tuesday, … 6 = Sunday
-- 今天是周五，返回了數(shù)值4
mysql> select weekday('2020-05-22');
+-----------------------+
| weekday('2020-05-22') |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.00 sec)
45.WEEKOFYEAR()
等價于:WEEK(date,3)
46.YEAR()
返回年份
mysql> select YEAR('2020-05-22');
+--------------------+
| YEAR('2020-05-22') |
+--------------------+
| 2020 |
+--------------------+
1 row in set (0.01 sec)
47.YEARWEEK()
語法:YEARWEEK(date), YEARWEEK(date,mode)
返回周，'0000:01:01'為第一周
mode同week，代表是周一還是昨天為本周的第一周
mysql> select YEARWEEK('0000:01:01');
+------------------------+
| YEARWEEK('0000:01:01') |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.02 sec)
mysql> select YEARWEEK('2020-05-22');
+------------------------+
| YEARWEEK('2020-05-22') |
+------------------------+
| 202020 |
+------------------------+
1 row in set (0.00 sec)
