mysql最大时间函数_MySQL 日期时间函数大全（转）

最新推荐文章于 2024-07-19 03:43:24 发布

吴婧羡

最新推荐文章于 2024-07-19 03:43:24 发布

阅读量1.1k

点赞数

文章标签： mysql最大时间函数

本文链接：https://blog.csdn.net/weixin_30114649/article/details/113272713

版权

转载自：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;

+---------------------+---------------------+---------------------+---------------------+---------------------+

+---------------------+---------------------+---------------------+---------------------+---------------------+

| 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();

+---------------------+---------------------+----------+---------------------+---------------------+

+---------------------+---------------------+----------+---------------------+---------------------+

| 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());

+-------------+--------------+------------+-------------------+----------------+

+-------------+--------------+------------+-------------------+----------------+

| 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');

+------------------------+------------------------+---------------------------------+------------------------+------------------------+

+------------------------+------------------------+---------------------------------+------------------------+------------------------+

| 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 …

Sunday

0-53

with a Sunday in this year

Monday

0-53

with 4 or more days this year

Sunday

1-53

with a Sunday in this year

Monday

1-53

with 4 or more days this year

Sunday

0-53

with 4 or more days this year

Monday

0-53

with a Monday in this year

Sunday

1-53

with 4 or more days this year

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)

吴婧羡

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql最大时间函数_MySQL 日期时间函数大全（转）

转载自：http://blog.itpub.net/29773961/viewspace-1808967以下内容基于MySQL 5.6及更高，大部分函数5.5也基本适用，更低版本请参考对应版本手册，其内容整理自官方。mysql常用函数被分为五类，分别为：①Date and time：日期和时间；②String：字符串函数；http://blog.itpub.net/29773961/viewspa...
复制链接

扫一扫