MySQL 日期時間函式大全

以下內容基於MySQL 5.6及更高，大部分函式5.5也基本適用，更低版本請參考對應版本手冊，其內容整理自官方。

mysql常用函式被分為五類，分別為：

①Date and time：日期和時間；

②String：字串函式；
http://blog.itpub.net/29773961/viewspace-1813545/

③Numeric：數字函式；
http://blog.itpub.net/29773961/viewspace-1813556/

④Control Flow：基於一個表示式的結果集選擇不同的值（控制流函式）；
http://blog.itpub.net/29773961/viewspace-1813557/

⑤Aggregate：基於一列的多個值返回單一值（聚合函式）；
http://blog.itpub.net/29773961/viewspace-1813589/

【Date and time】：

（補充說明）DATE/TIME 格式：

NOW()、CURRENT_TIMESTAMP()與CURRENT_TIMESTAMP、LOCALTIME()與LOCALTIME、LOCALTIMESTAMP()與LOCALTIMESTAMP：（均為同義詞）
返回當前的日期和時間，基於Satement（DATETIME格式）

mysql> SELECT NOW(),CURRENT_TIMESTAMP();
+---------------------+---------------------+
| NOW() | CURRENT_TIMESTAMP() |
+---------------------+---------------------+
| 2015-09-25 14:14:15 | 2015-09-25 14:14:15 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW(),LOCALTIME(),LOCALTIME,LOCALTIMESTAMP(),LOCALTIMESTAMP;
+---------------------+---------------------+---------------------+---------------------+---------------------+
| NOW() | LOCALTIME() | LOCALTIME | LOCALTIMESTAMP() | LOCALTIMESTAMP |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2015-09-25 14:21:23 | 2015-09-25 14:21:23 | 2015-09-25 14:21:23 | 2015-09-25 14:21:23 | 2015-09-25 14:21:23 |
+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

SYSDATE()：返回當前的日期和時間，基於系統時間（DATETIME格式）

mysql> mysql> SELECT NOW(),SYSDATE(),SLEEP(1),NOW(),SYSDATE();
+---------------------+---------------------+----------+---------------------+---------------------+
| NOW() | SYSDATE() | SLEEP(1) | NOW() | SYSDATE() |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2015-09-28 10:49:00 | 2015-09-28 10:49:00 | 0 | 2015-09-28 10:49:00 | 2015-09-28 10:49:01 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (1.00 sec)

CURDATE()與CURRENT_DATE()與CURRENT_DATE：返回當前的日期，基於伺服器主機（DATE格式）

mysql> SELECT CURDATE(),CURRENT_DATE();
+------------+----------------+
| CURDATE() | CURRENT_DATE() |
+------------+----------------+
| 2015-09-25 | 2015-09-25 |
+------------+----------------+
1 row in set (0.00 sec)

CURTIME()與CURRENT_TIME()與CURRENT_TIME：返回當前的時間，基於伺服器主機（TIME格式）

mysql> SELECT CURTIME(),CURRENT_TIME();
+-----------+----------------+
| CURTIME() | CURRENT_TIME() |
+-----------+----------------+
| 14:15:19 | 14:15:19 |
+-----------+----------------+
1 row in set (0.00 sec)

YEAR()：返回日期的年份（YEAR格式）
MONTH()：返回日期的月份
DAY()與DAYOFMONTH()：返回日期的天數
DAYNAME()：返回星期幾

mysql> SELECT YEAR(NOW()),
-> MONTH(NOW()),
-> DAY(NOW()),
-> DAYOFMONTH(NOW()),
-> DAYNAME(NOW());
+-------------+--------------+------------+-------------------+----------------+
| YEAR(NOW()) | MONTH(NOW()) | DAY(NOW()) | DAYOFMONTH(NOW()) | DAYNAME(NOW()) |
+-------------+--------------+------------+-------------------+----------------+
| 2015 | 9 | 25 | 25 | Friday |
+-------------+--------------+------------+-------------------+----------------+
1 row in set (0.00 sec)

DAYOFWEEK()：返回日期對應的星期，1--週日 2--週一 3--週二 4--週三 5--週四 6--週五 7--週六
DAYOFYEAR()：返回日期為該年的第多少天

mysql> \! cal
September 2015
Su Mo Tu We Th Fr Sa
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
mysql> SELECT DAYOFWEEK(NOW()),
-> DAYOFYEAR(NOW());
+------------------+------------------+
| DAYOFWEEK(NOW()) | DAYOFYEAR(NOW()) |
+------------------+------------------+
| 2 | 271 |
+------------------+------------------+
1 row in set (0.00 sec)

HOUR()：提取時間的小時
MINUTE()：提取時間的分鐘
SECOND()：提取時間的秒數
MICROSECOND()：提取時間的微秒

mysql> SELECT HOUR('11:11:12.000123') h,
-> MINUTE('11:11:12.000123') m,
-> SECOND('11:11:12.000123') s,
-> MICROSECOND('11:11:12.000123') ms;
+------+------+------+------+
| h | m | s | ms |
+------+------+------+------+
| 11 | 11 | 12 | 123 |
+------+------+------+------+
1 row in set (0.00 sec)

STR_TO_DATE()：將字串轉換成日期格式（以下三例均可用DATE_FORMAT()替換）

mysql> SELECT STR_TO_DATE('2015-10-31','%Y-%m-%d');
+--------------------------------------+
| STR_TO_DATE('2015-10-31','%Y-%m-%d') |
+--------------------------------------+
| 2015-10-31 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('2015-10-31 08:30:59','%Y-%m-%d %H:%i:%s');
+--------------------------------------------------------+
| STR_TO_DATE('2015-10-31 08:30:59','%Y-%m-%d %H:%i:%s') |
+--------------------------------------------------------+
| 2015-10-31 08:30:59 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('20151031083059','%Y%m%d%H%i%s'); -- 格式化的格式要與字串一致
+----------------------------------------------+
| STR_TO_DATE('20151031083059','%Y%m%d%H%i%s') |
+----------------------------------------------+
| 2015-10-31 08:30:59 |
+----------------------------------------------+
1 row in set (0.00 sec)

DATE_FORMAT()：將日期時間格式化

mysql> SELECT DATE_FORMAT('2015-10-31 08:30:59','%H:%i:%s');
+-----------------------------------------------+
| DATE_FORMAT('2015-10-31 08:30:59','%H:%i:%s') |
+-----------------------------------------------+
| 08:30:59 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT(NOW(),'%y/%m/%d'); -- 也可以這樣用
+-------------------------------+
| DATE_FORMAT(NOW(),'%y/%m/%d') |
+-------------------------------+
| 15/09/25 |
+-------------------------------+
1 row in set (0.00 sec)

一般情況下，多數場景可用DATE_FORMAT()代替STR_TO_DATE()。

ADDDATE()：將日期相加減
DATE_ADD()與DATE_SUB()和SUBDATE()：將日期相加減

mysql> SELECT ADDDATE('2000-01-01',222);
+---------------------------+
| ADDDATE('2000-01-01',222) |
+---------------------------+
| 2000-08-10 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDDATE('2000-01-01', INTERVAL 111 DAY);
+-----------------------------------------+
| ADDDATE('2000-01-01', INTERVAL 111 DAY) |
+-----------------------------------------+
| 2000-04-21 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_ADD('2000-01-01', INTERVAL 111 DAY);
+------------------------------------------+
| DATE_ADD('2000-01-01', INTERVAL 111 DAY) |
+------------------------------------------+
| 2000-04-21 |
+------------------------------------------+
1 row in set (0.00 sec)

ADDTIME()：將日期與時間相加減

mysql> SELECT ADDTIME('2000-01-01 00:00:01.000000', '1 1:1:1.000001');
+---------------------------------------------------------+
| ADDTIME('2000-01-01 00:00:01.000000', '1 1:1:1.000001') |
+---------------------------------------------------------+
| 2000-01-02 01:01:02.000001 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDTIME('11:11:11.999999', '1:0:0.000001');
+--------------------------------------------+
| ADDTIME('11:11:11.999999', '1:0:0.000001') |
+--------------------------------------------+
| 12:11:12 |
+--------------------------------------------+
1 row in set (0.00 sec)

CONVERT_TZ()：轉換時區

mysql> SELECT CONVERT_TZ('2000-01-01 12:00:00', '+00:00', '+08:00'); -- 將+0轉換為+8
+-------------------------------------------------------+
| CONVERT_TZ('2000-01-01 12:00:00', '+00:00', '+08:00') |
+-------------------------------------------------------+
| 2000-01-01 20:00:00 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

DATE()：將日期時間轉換成日期

mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2015-09-28 |
+-------------+
1 row in set (0.00 sec)

DATEDIFF()：計算兩個日期的差值

mysql> SELECT DATEDIFF(NOW(),'1993-06-12');
+------------------------------+
| DATEDIFF(NOW(),'1993-06-12') |
+------------------------------+
| 8143 |
+------------------------------+
1 row in set (0.00 sec)

額外引數：INTERVAL，用於日期或時間相加（相減），更加靈活。

mysql> SELECT NOW(),
-> NOW() + INTERVAL 100 DAY,
-> NOW() - INTERVAL 299 MINUTE;
+---------------------+--------------------------+-----------------------------+
| NOW() | NOW() + INTERVAL 100 DAY | NOW() - INTERVAL 299 MINUTE |
+---------------------+--------------------------+-----------------------------+
| 2015-09-25 14:39:59 | 2016-01-03 14:39:59 | 2015-09-25 09:40:59 |
+---------------------+--------------------------+-----------------------------+
1 row in set (0.00 sec)

EXTRACT()：提取日期時間的部分內容

mysql> SELECT EXTRACT(YEAR FROM '2000-01-02 01:02:03'),
-> EXTRACT(YEAR_MONTH FROM '2000-01-02 01:02:03'),
-> EXTRACT(DAY_MINUTE FROM '2000-01-02 01:02:03'),
-> EXTRACT(MICROSECOND FROM '2000-01-02 01:02:03.000123')\G
*************************** 1. row ***************************
EXTRACT(YEAR FROM '2000-01-02 01:02:03'): 2000
EXTRACT(YEAR_MONTH FROM '2000-01-02 01:02:03'): 200001
EXTRACT(DAY_MINUTE FROM '2000-01-02 01:02:03'): 20102
EXTRACT(MICROSECOND FROM '2000-01-02 01:02:03.000123'): 123
1 row in set (0.00 sec)

FROM_DAYS()：根據整型數字N（天）返回對應日期

mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
| 0001-01-01 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS(735555);
+-------------------+
| FROM_DAYS(735555) |
+-------------------+
| 2013-11-18 |
+-------------------+
1 row in set (0.00 sec)

FROM_UNIXTIME()：從UNIX時間戳開始返回經過N秒後的時間

mysql> SELECT FROM_UNIXTIME(0);
+---------------------+
| FROM_UNIXTIME(0) |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1);
+---------------------+
| FROM_UNIXTIME(1) |
+---------------------+
| 1970-01-01 08:00:01 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(3600);
+---------------------+
| FROM_UNIXTIME(3600) |
+---------------------+
| 1970-01-01 09:00:00 |
+---------------------+
1 row in set (0.00 sec)

GET_FORMAT()：返回格式字串
一般在使用DATE_FORMAT時和STR_TO_DATE()時配合。

Function Call Result
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'

mysql> SELECT GET_FORMAT(DATE,'USA') a,
-> STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA')) b,
-> GET_FORMAT(DATE,'EUR') c,
-> DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')) d;
+----------+------------+----------+------------+
| a | b | c | d |
+----------+------------+----------+------------+
| %m.%d.%Y | 2003-10-31 | %d.%m.%Y | 03.10.2003 |
+----------+------------+----------+------------+
1 row in set (0.00 sec)

LAST_DAY()：返回某個日期的該月最後一天的日期

mysql> SELECT LAST_DAY('2000-02-02'), LAST_DAY('2001-02-03'), LAST_DAY('2000-01-01 00:01:02'), LAST_DAY('2000-00-03'), LAST_DAY('2000-01-33');
+------------------------+------------------------+---------------------------------+------------------------+------------------------+
| LAST_DAY('2000-02-02') | LAST_DAY('2001-02-03') | LAST_DAY('2000-01-01 00:01:02') | LAST_DAY('2000-00-03') | LAST_DAY('2000-01-33') |
+------------------------+------------------------+---------------------------------+------------------------+------------------------+
| 2000-02-29 | 2001-02-28 | 2000-01-31 | NULL | NULL |
+------------------------+------------------------+---------------------------------+------------------------+------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2000-00-03' |
| Warning | 1292 | Incorrect datetime value: '2000-01-33' |
+---------+------+----------------------------------------+
2 rows in set (0.00 sec)

MAKEDATE()：返回某一年的第多少天的具體日期
MAKETIME()：根據給定引數返回時間（h的上限為838，m的上限為60，s的上限為60，超過上限返回null）

mysql> SELECT MAKEDATE(2000,32) a,
-> MAKEDATE(2000,366) b,
-> MAKEDATE(2000,367) c,
-> MAKEDATE(2000,0) d,
-> MAKETIME(11,22,30) e,
-> MAKETIME(25,11,11) f;
+------------+------------+------------+------+----------+----------+
| a | b | c | d | e | f |
+------------+------------+------------+------+----------+----------+
| 2000-02-01 | 2000-12-31 | 2001-01-01 | NULL | 11:22:30 | 25:11:11 |
+------------+------------+------------+------+----------+----------+
1 row in set (0.00 sec)

MONTHNAME()：返回月份的名字

mysql> SELECT MONTHNAME(NOW());
+------------------+
| MONTHNAME(NOW()) |
+------------------+
| September |
+------------------+
1 row in set (0.00 sec)

PERIOD_ADD()：增加x個月後並返回，格式是YYM或YYYYMM

mysql> SELECT PERIOD_ADD(200001,5),
-> PERIOD_ADD(200808,10);
+----------------------+-----------------------+
| PERIOD_ADD(200001,5) | PERIOD_ADD(200808,10) |
+----------------------+-----------------------+
| 200006 | 200906 |
+----------------------+-----------------------+
1 row in set (0.00 sec)

PERIOD_DIFF()：返回兩個日期相差的月份，格式YYYYMM

mysql> SELECT PERIOD_DIFF(200001,200005), PERIOD_DIFF(199912,199810);
+----------------------------+----------------------------+
| PERIOD_DIFF(200001,200005) | PERIOD_DIFF(199912,199810) |
+----------------------------+----------------------------+
| -4 | 14 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

QUARTER()：返回日期的季度，月份1-3為1，4-6為2，7-9為3，10-12為4

mysql> SELECT QUARTER('2015-01-01'), QUARTER('2015-07-01');
+-----------------------+-----------------------+
| QUARTER('2015-01-01') | QUARTER('2015-07-01') |
+-----------------------+-----------------------+
| 1 | 3 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

SEC_TO_TIME()：將數字轉換成時分秒

mysql> SELECT SEC_TO_TIME(1),
-> SEC_TO_TIME(1) +0,
-> SEC_TO_TIME(3600);
+----------------+-------------------+-------------------+
| SEC_TO_TIME(1) | SEC_TO_TIME(1) +0 | SEC_TO_TIME(3600) |
+----------------+-------------------+-------------------+
| 00:00:01 | 1 | 01:00:00 |
+----------------+-------------------+-------------------+
1 row in set (0.00 sec)

SUBTIME()：返回兩個時間相減的結果

mysql> SELECT SUBTIME('2000-01-02 23:59:59.000003','2 3:58:58.000002') a,
-> SUBTIME('01:02:03.123456','02:02:02.123465') b;
+----------------------------+------------------+
| a | b |
+----------------------------+------------------+
| 1999-12-31 20:01:01.000001 | -00:59:59.000009 |
+----------------------------+------------------+
1 row in set (0.00 sec)

TIME_FORMAT()：返回格式化後的日期，若包含小時的部分大於23，%k為小時格式說明符，產生的小時值會模12。

mysql> SELECT TIME_FORMAT('25:00:00', '%H %k %h %I %l'),
-> TIME_FORMAT('100:00:00', '%H %k %h %i %l');
+-------------------------------------------+--------------------------------------------+
| TIME_FORMAT('25:00:00', '%H %k %h %I %l') | TIME_FORMAT('100:00:00', '%H %k %h %i %l') |
+-------------------------------------------+--------------------------------------------+
| 25 25 01 01 1 | 100 100 04 00 4 |
+-------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)

TIME_TO_SEC()：

mysql> SELECT TIME_TO_SEC('23:59:59'), TIME_TO_SEC('00:01:01');
+-------------------------+-------------------------+
| TIME_TO_SEC('23:59:59') | TIME_TO_SEC('00:01:01') |
+-------------------------+-------------------------+
| 86399 | 61 |
+-------------------------+-------------------------+
1 row in set (0.00 sec)

TIME()：返回時間值

mysql> SELECT NOW(), TIME(NOW());
+---------------------+-------------+
| NOW() | TIME(NOW()) |
+---------------------+-------------+
| 2015-10-09 15:11:49 | 15:11:49 |
+---------------------+-------------+
1 row in set (0.00 sec)

TIMESTAMPADD()：時間戳相加

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2015-01-01 00:59:00') a,
-> TIMESTAMPADD(WEEK,2,'2000-01-01') b,
-> TIMESTAMPADD(SECOND,66,'2000-01-01') c;
+---------------------+------------+---------------------+
| a | b | c |
+---------------------+------------+---------------------+
| 2015-01-01 01:00:00 | 2000-01-15 | 2000-01-01 00:01:06 |
+---------------------+------------+---------------------+
1 row in set (0.00 sec)

TIMESTAMPDIFF()：時間戳相減

mysql> SELECT TIMESTAMPDIFF(MINUTE,'2015-01-01 00:01:00','2015-01-01 00:00:00') a;
+------+
| a |
+------+
| -1 |
+------+
1 row in set (0.00 sec)

TO_DAYS()：返回天數，從0年開始（0000-00-00）

mysql> SELECT TO_DAYS('20000101') a, -- 這樣的寫法和'2000-01-01'都可以
-> TO_DAYS('0000-01-01') b,
-> TO_DAYS('0000-00-00') c;
+--------+------+------+
| a | b | c |
+--------+------+------+
| 730485 | 1 | NULL |
+--------+------+------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

TO_SECONDS()：返回秒數，從0年開始（0000-00-00）

mysql> SELECT TO_SECONDS('20000101') a,
-> TO_SECONDS('00000101') b,
-> TO_SECONDS('11:11:11') c,
-> TO_SECONDS('2015-10-09 11:11:11') d;
+-------------+-------+-------------+-------------+
| a | b | c | d |
+-------------+-------+-------------+-------------+
| 63113904000 | 86400 | 63488188800 | 63611608271 |
+-------------+-------+-------------+-------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP()：

mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP('1970-01-02 00:00:00');
+------------------+-----------------------+---------------------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP('1970-01-02 00:00:00') |
+------------------+-----------------------+---------------------------------------+
| 1444376257 | 1444376257 | 57600 |
+------------------+-----------------------+---------------------------------------+
1 row in set (0.00 sec)

UTC_DATE()：

mysql> SELECT UTC_DATE(), UTC_DATE() + 1;
+------------+----------------+
| UTC_DATE() | UTC_DATE() + 1 |
+------------+----------------+
| 2015-10-09 | 20151010 |
+------------+----------------+
1 row in set (0.00 sec)

UTC_TIME()：

mysql> SELECT UTC_TIME(), UTC_TIME() + 60, UTC_TIME + 0;
+------------+-----------------+--------------+
| UTC_TIME() | UTC_TIME() + 60 | UTC_TIME + 0 |
+------------+-----------------+--------------+
| 07:42:54 | 74314 | 74254 |
+------------+-----------------+--------------+
1 row in set (0.00 sec)

UTC_TIMESTAMP()：

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------+---------------------+
| UTC_TIMESTAMP() | UTC_TIMESTAMP() + 0 |
+---------------------+---------------------+
| 2015-10-09 07:44:48 | 20151009074448 |
+---------------------+---------------------+
1 row in set (0.00 sec)

WEEK()與WEEKOFYEAR()：返回日期是該年的第幾個星期（但WEEKOFYEAR()沒有模式選擇）。
由於習慣不同，有8種模式。

Mode	First day of week	Range	Week 1 is the first week …
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with 4 or more days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with 4 or more days this year
4	Sunday	0-53	with 4 or more days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with 4 or more days this year
7	Monday	1-53	with a Monday in this year

mysql> select week('2015-01-08',5);
+----------------------+
| week('2015-01-08',5) |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
mysql> select week('2015-01-08',1);
+----------------------+
| week('2015-01-08',1) |
+----------------------+
| 2 |
+----------------------+
1 row in set (0.00 sec)

WEEKDAY()：返回星期的索引；0-週一，1-週二，2-週三，3-週四，4-週五，5-週六，6-週日

mysql> SELECT WEEKDAY('2015-10-09');
+-----------------------+
| WEEKDAY('2015-10-09') |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.00 sec)

YEARWEEK()：返回日期是哪一年的第幾個星期

mysql> \! cal 1 2015
January 2015
Su Mo Tu We Th Fr Sa
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
mysql> SELECT YEARWEEK('2015-01-03'), YEARWEEK('2015-01-04');
+------------------------+------------------------+
| YEARWEEK('2015-01-03') | YEARWEEK('2015-01-04') |
+------------------------+------------------------+
| 201452 | 201501 |
+------------------------+------------------------+
1 row in set (0.00 sec)

作者公眾號（持續更新）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1808967/，如需轉載，請註明出處，否則將追究法律責任。

MySQL 日期時間函式大全

相關文章