MySQL日期与时间函数的使用汇总

吾爱主题阅读：143 2024-04-02 07:58:04 评论：0

本文基于MySQL8.0

本文介绍MySQL关于日期和时间操作的函数。

日期和时间函数

函数	描述
ADDDATE()	给日期值添加时间值
ADDTIME()	添加time
CONVERT_TZ()	从一个时区转换为另一个时区
CURDATE()	返回当前日期
CURRENT_DATE(), CURRENT_DATE	和CURDATE()同义
CURRENT_TIME(), CURRENT_TIME	和CURDATE()同义
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP	和NOW() 同义
CURTIME()	返回当前时间
DATE()	获取日期或日期时间表达式的日期部分
DATE_ADD()	将时间值（间隔）添加到日期值
DATE_FORMAT()	按指定格式格式化日期
DATE_SUB()	从日期中减去时间值（间隔）
DATEDIFF()	减去两个日期
DAY()	和 DAYOFMONTH()同义
DAYNAME()	返回工作日名称
DAYOFMONTH()	返回月份的日期 (0-31)
DAYOFWEEK()	根据参数返回工作日的排序
DAYOFYEAR()	返回一年中的某一天 (1-366)
EXTRACT()	获取日期中的一部分
FROM_DAYS()	将一天的序号转化为日期
FROM_UNIXTIME()	将Unix时间戳格式化为日期
GET_FORMAT()	返回日期格式字符串
HOUR()	获取小时
LAST_DAY	返回参数月份的最后一天
LOCALTIME(), LOCALTIME	和NOW()同义
LOCALTIMESTAMP, LOCALTIMESTAMP()	和 NOW()同义
MAKEDATE()	从年和日创建日期
MAKETIME()	从时、分、秒创建时间
MICROSECOND()	从参数返回微秒
MINUTE()	返回参数分钟
MONTH()	返回已过日期起的月份
MONTHNAME()	返回月份名称
NOW()	返回当前日期和时间
PERIOD_ADD()	给年-月添加一段时间
PERIOD_DIFF()	返回一段时间之间的月数
QUARTER()	从日期参数返回季度
SEC_TO_TIME()	将秒转换为“hh:mm:ss”格式
SECOND()	返回秒 (0-59)
STR_TO_DATE()	将字符串转换为日期
SUBDATE()	当使用三个参数调用时，和DATE_SUB（）同义
SUBTIME()	减去时间
SYSDATE()	返回函数执行的时间
TIME()	根据传递的表达式获取时间
TIME_FORMAT()	格式为时间
TIME_TO_SEC()	返回转换为秒的参数
TIMEDIFF()	时间相减
TIMESTAMP()	对于单个参数，此函数返回日期或日期时间表达式；对于两个参数，返回参数的和
TIMESTAMPADD()	向日期时间表达式添加间隔
TIMESTAMPDIFF()	从日期时间表达式中减去间隔
TO_DAYS()	返回转换为天的日期参数
TO_SECONDS()	返回从0年起转换为秒的日期或日期时间参数
UNIX_TIMESTAMP()	返回Unix时间戳
UTC_DATE()	返回当前UTC日期
UTC_TIME()	返回当前UTC时间
UTC_TIMESTAMP()	返回当前UTC日期和时间
WEEK()	返回星期数
WEEKDAY()	返回工作日序号
WEEKOFYEAR()	返回日期（1-53）的日历周
YEAR()	返回年
YEARWEEK()	返回年份和星期

来看一看MySQL常用日期和时间函数的示例。

获得当前日期时间

CURDATE()

以“YYYY-MM-DD”或YYYYMMDD格式返回当前日期，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。

1 2 3 4 mysql> SELECT CURDATE(); -> '2008-06-13' mysql> SELECT CURDATE() + 0; -> 20080613

CURTIME([fsp])

以'hh:mm:ss'或hhmmss格式返回当前时间，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。该值以会话时区表示。

如果指定的fsp精度是从0到6的小数位数，则指定从0到6的小数精度。

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

NOW([fsp])

以'YYYY-MM-DD hh:MM:ss'或YYYYMMDDhhmmss格式返回当前日期和时间，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。该值以会话时区表示。

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

时间日期格式转化

同一个日期时间会有多种不同的表示方式，有的时候需要在不同格式之间相互转换。在MySQL中用的是date_format()函数:

DATE_FORMAT(date,format):根据格式字符串格式化日期值。

相关的格式如下，除了用于这个函数，格式还可以用于：STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP().

格式	描述
%a	缩写星期名 (Sun..Sat)
%b	缩写月名 (Jan..Dec)
%c	月, 数值 (0..12)
%D	带有英文前缀的月中的天 (0th, 1st, 2nd, 3rd, …)
%d	一个月里面的某一天, 数值 (00..31)
%e	一个月里面的某一天, 数值 (0..31)
%f	微秒 (000000..999999)
%H	小时 (00..23)
%h	小时 (01..12)
%I	小时 (01..12)
%i	分钟, 数值 (00..59)
%j	一年中的某一天 (001..366)
%k	小时 (0..23)
%l	小时 (1..12)
%M	月名 (January..December)
%m	月份, 数值 (00..12)
%p	AM or PM
%r	时间, 12 - 小时 (hh:mm:ss AM 或者 PM)
%S	秒 (00..59)
%s	秒 (00..59)
%T	时间, 24 - 小时 (hh:mm:ss)
%W	星期名 (Sunday..Saturday)
%w	周几 (0=Sunday..6=Saturday)
%Y	年, 数值, 4 个数字
%y	年, 数值, 2 个数字

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

日期时间运算

DATE_ADD(date,INTERVAL expr unit),
DATE_SUB(date,INTERVAL expr unit)

这些函数执行日期运算,来进行日期向前/向后的偏移。date参数指定开始日期或日期时间值。expr是一个表达式，指定从开始日期加上或减去的间隔值。expr被计算为一个字符串；它可以以-开头表示负间隔。unit是一个关键字，指示表达式应使用的单位。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 mysql> SELECT DATE_ADD( '2018-05-01' ,INTERVAL 1 DAY ); -> '2018-05-02' mysql> SELECT DATE_SUB( '2018-05-01' ,INTERVAL 1 YEAR ); -> '2017-05-01' mysql> SELECT DATE_ADD( '2020-12-31 23:59:59' , -> INTERVAL 1 SECOND ); -> '2021-01-01 00:00:00' mysql> SELECT DATE_ADD( '2018-12-31 23:59:59' , -> INTERVAL 1 DAY ); -> '2019-01-01 23:59:59' mysql> SELECT DATE_ADD( '2100-12-31 23:59:59' , -> INTERVAL '1:1' MINUTE_SECOND); -> '2101-01-01 00:01:00' mysql> SELECT DATE_SUB( '2025-01-01 00:00:00' , -> INTERVAL '1 1:1:1' DAY_SECOND); -> '2024-12-30 22:58:59' mysql> SELECT DATE_ADD( '1900-01-01 00:00:00' , -> INTERVAL '-1 10' DAY_HOUR); -> '1899-12-30 14:00:00' mysql> SELECT DATE_SUB( '1998-01-02' , INTERVAL 31 DAY ); -> '1997-12-02' mysql> SELECT DATE_ADD( '1992-12-31 23:59:59.000002' , -> INTERVAL '1.999999' SECOND_MICROSECOND); -> '1993-01-01 00:00:01.000001'

DATEDIFF(expr1,expr2)

DATEDIFF（）返回expr1−expr2，以从一个日期到另一个日期的天数表示。expr1和expr2是日期或日期和时间表达式。计算中只使用值的日期部分。

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