转载自:http://blog.itpub.net/29773961/viewspace-1808967
以下内容基于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种模式。
ModeFirst day of weekRangeWeek 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)