讨论常用的 MySQL 日期函数。

讨论常用的 MySQL 日期函数。

日期函数和运算符用于日期和时间类型的数据进行操作并返回一个结果。以下是 MySQL 中常见的日期函数和运算符：

ADDDATE()、DATE_ADD()，为日期时间增加一个时间间隔
ADDTIME()，增加指定时间
CONVERT_TZ()，将日期时间从一个时区转换为另一个时区
CURDATE()、CURRENT_DATE()、CURRENT_DATE，返回当前日期
CURTIME()、CURRENT_TIME()、CURRENT_TIME，返回当前时间
CURRENT_TIMESTAMP()、CURRENT_TIMESTAMP、e data-draft-node=“block” data-draft-type=“table” data-size=“normal” data-row-style=“normal”>、LOCALTIMESTAMP()、LOCALTIMESTAMP，返回当前日期和时间
DATE()，获取日期时间中的日期部分
DATE_FORMAT()，将日期时间格式化成字符串
DATE_SUB()、SUBDATE()，从日期时间中减去一个时间间隔
DATEDIFF()，计算两个日期之间的天数
DAY()、DAYOFMONTH()，返回日期属于一月中的第几天（0-31）
DAYNAME()，返回日期是星期几
DAYOFWEEK()，返回日期是一星期中的第几天（1 = 星期天）
DAYOFYEAR()，返回日期是一年中的第几天（1-366）
EXTRACT()，获取日期时间中某个部分，例如年、月、时、分等
FROM_DAYS()，将一个数字形式的天数（从第 0 年以来）转换为日期
FROM_UNIXTIME()，将 Unix 时间戳转换为日期时间
GET_FORMAT()，返回指定的格式字符串
HOUR()，获取时间中的小时
LAST_DAY，返回日期所属月份的最后一天
MAKEDATE()，返回一个由年和日组成的日期
MAKETIME()，返回一个由时、分、秒组成的时间
MICROSECOND()，返回时间中的毫秒
MINUTE()，返回时间中的分钟
MONTH()，返回日期中的月份
MONTHNAME()，返回日期中的月份名称
PERIOD_ADD()，为一个时间段增加指定的月数
PERIOD_DIFF()，返回两个时间段之间相差的月数
QUARTER()，返回日期所属的季节
SEC_TO_TIME()，将数字形式的秒数转换为时间类型
SECOND()，返回时间中的秒数（0-59）
STR_TO_DATE()，将字符串转换为日期时间类型
SUBTIME()，减去指定时间
SYSDATE()，返回函数执行时的日期时间
TIME()，获取日期时间中的时间部分
TIME_FORMAT()，将时间格式化成字符串
TIME_TO_SEC()，将时间转换为秒数
TIMEDIFF()，计算两个时间点之间相差的时间
TIMESTAMP()，一个参数时返回参数对应的日期时间，两个参数时将一个日期时间增加指定的时间
TIMESTAMPADD()，为一个日期时间增加指定的时间间隔
TIMESTAMPDIFF()，返回两个日期时间之间相差的时间间隔
TO_DAYS()，返回从第 0 年到该日期时间以来的天数
TO_SECONDS()，返回从第 0 年到该日期时间以来的秒数
UNIX_TIMESTAMP()，返回 Unix 时间戳
UTC_DATE()、UTC_DATE，返回当前 UTC 日期
UTC_TIME()、UTC_TIME，返回当前 UTC 时间
UTC_TIMESTAMP()、UTC_TIMESTAMP，返回当前 UTC 日期和时间
WEEK()，返回日期属于一年中的第几周
WEEKDAY()，返回日期属于一周中的第几天（0-6）
WEEKOFYEAR()，返回日期所属的日历周（1-53）
YEAR()，返回日期中的年份
YEARWEEK()，返回日期所属的年分和周数
下面我们通过一些示例来说明这些函数的作用。

16.1 获取当前日期和时间

CURDATE()、CURRENT_DATE() 和 CURRENT_DATE 函数用于返回当前时间，返回格式为日期类型的 ‘YYYY-MM-DD’ 或者整数类型的 YYYYMMDD 。例如：

select curdate(), current_date + 0;

curdate()	current_date + 0
2020-07-26	20200726
CURRENT_TIME、CURRENT_TIME([fsp]) 和 CURTIME([fsp]) 函数用于返回当前时间，返回格式为时间类型的 ‘hh:mm:ss’ 或者整数类型的 hhmmss。例如：

select curtime(), current_time + 0;

curtime()	current_time + 0
20:59:57	205957
参数 fsp 用于指定小数秒的精度，取值为 0 到 6。例如：

select current_time(6);

current_time(6)
21:00:15.432279
NOW([fsp]) 函数用于返回当前日期时间，返回格式为日期时间类型的 ‘YYYY-MM-DD hh:mm:ss’ 或者整数类型的 YYYYMMDDhhmmss；CURRENT_TIMESTAMP([fsp])、CURRENT_TIMESTAMP、LOCALTIME([fsp])、LOCALTIME、LOCALTIMESTAMP([fsp])、LOCALTIMESTAMP 都是 Now() 函数的同义词。例如：

select now(), current_timestamp(6), localtime, localtimestamp(6);

now()	current_timestamp(6)	localtime	localtimestamp(6)
2020-07-26 21:01:03	2020-07-26 21:01:03.369695	2020-07-26 21:01:03	2020-07-26 21:01:03.369695
SYSDATE([fsp]) 函数用于返回函数执行时的日期时间，返回格式为日期时间类型的 ‘YYYY-MM-DD hh:mm:ss’ 或者整数类型的 YYYYMMDDhhmmss。例如：

select sysdate(), sysdate(6);

sysdate()	sysdate(6)
2020-07-26 21:08:53	2020-07-26 21:08:53.322645
需要注意的是，NOW() 函数返回的是语句开始执行的时间，对于存储函数或者触发器返回的是函数执行或者触发语句执行的时间；SYSDATE() 函数返回的是该函数被执行的时间，同一个语句中多次调用可能返回不同的值。例如：

select now(), sysdate(), sleep(6), now(), sysdate();

now()	sysdate()	sleep(6)	now()	sysdate()
2020-07-26 21:11:50	2020-07-26 21:11:50	0	2020-07-26 21:11:50	2020-07-26 21:11:56
从结果可以看出，两个 NOW() 函数返回了相同的时间；但是两个 SYSDATE() 函数的结果相差 6 秒；SLEEP() 函数用于暂停指定的秒数。

通过系统选项 --sysdate-is-now 可以将 SYSDATE() 设置为 NOW() 的同义词。
UTC_DATE() 和 UTC_DATE 函数用于返回当前 UTC 日期，UTC_TIME([fsp]) 和 UTC_TIME 函数用于返回当前 UTC 时间，UTC_TIMESTAMP([fsp]) 和 UTC_TIMESTAMP 函数用于返回当前 UTC 日期和时间。例如：

select utc_date(), utc_time(6), utc_timestamp(6);

utc_date()	utc_time(6)	utc_timestamp(6)
2020-07-26	13:23:41.993849	2020-07-26 13:23:41.993849
北京时间是 +8 时区，所以 UTC 时间比当前时间慢 8 小时。

16.2 构造一个日期时间值

FROM_DAYS(N) 函数用于将一个第 0 年以来的天数转换为日期。例如：

select from_days(737997);

from_days(737997)

   2020-07-26|

FROM_DAYS() 函数不支持公历出现之前的日期（1582 年）。另外，与该函数相反的是 TO_DAYS() 函数，参见下文第 16.4 节。

FROM_UNIXTIME(unix_timestamp[,format]) 函数用于将 Unix 时间戳转换为日期时间，返回格式为日期时间类型的 ‘YYYY-MM-DD hh:mm:ss’ 或者整数类型的 YYYYMMDDhhmmss。例如：

select from_unixtime(1595772000), from_unixtime(1595772000, ‘%Y %M %d %h:%i:%s %p’);

from_unixtime(1595772000)	from_unixtime(1595772000, ‘%Y %M %d %h:%i:%s %p’)

  2020-07-26 22:00:00|2020 七月 26 10:00:00 PM                          |

其中，字符串 format 用于设置日期时间的显示格式，具体参考下文第 16.4 节。

MAKEDATE(year,dayofyear) 函数返回一个由年和日组成的日期。例如：

select makedate(2020, 210), makedate(2020, 0);

makedate(2020, 210)	makedate(2020, 0)

     2020-07-28|                 |

2020 年第 210 天对于的日期是 2020-07-28。

MAKETIME(hour,minute,second) 函数返回一个由时、分、秒组成的时间。例如：

maketime(10, 20, 30)	maketime(10, 20, 30.456)

        10:20:30|         10:20:30.456000|

SEC_TO_TIME(seconds) 函数用于将数字形式的秒数转换为时间类型。例如：

select sec_to_time(3600), sec_to_time(3600)+0;

sec_to_time(3600)	sec_to_time(3600)+0

     01:00:00|              10000|

STR_TO_DATE(str,format) 函数用于将字符串转换为日期时间类型。例如：

select str_to_date(‘5 1, 2020’,‘%m %d, %Y’), str_to_date(‘1:00:00’,‘%H:%i:%s’);

str_to_date(‘5 1, 2020’,‘%m %d, %Y’)	str_to_date(‘1:00:00’,‘%H:%i:%s’)

                      2020-05-01|                         01:00:00|

其中，字符串 format 用于设置日期时间的格式；另外，该函数是 DATE_FORMAT() 函数的相反操作，具体参考下文第 16.4 节。

TIMESTAMP(expr) 函数用于返回参数对应的日期时间，TIMESTAMP(expr1,expr2) 函数用于将一个日期时间增加指定的时间并返回结果。例如：

select timestamp(‘2020-07-26’), timestamp(‘2020-07-26 02:00:00’, ‘10:00:00’);

timestamp(‘2020-07-26’)	timestamp(‘2020-07-26 02:00:00’, ‘10:00:00’)

2020-07-26 00:00:00|                         2020-07-26 12:00:00|

16.3 获取日期时间中的信息

DATE(expr) 函数用于获取日期时间中的日期部分，TIME(expr) 函数用于获取日期时间中的时间部分。例如：

select date(‘2020-07-26 10:00:00’), date(‘2020-07-26’), time(‘2020-07-26 10:00:00.450’);

date(‘2020-07-26 10:00:00’)	date(‘2020-07-26’)	time(‘2020-07-26 10:00:00.450’)

             2020-07-26|        2020-07-26|                10:00:00.450000|

EXTRACT(unit FROM date) 函数用于获取日期时间中的某个部分。例如：

select extract(year from ‘2020-07-26’) extract,
extract(year_month from ‘2020-07-26’) extract,
extract(hour_second from ‘2020-07-26 12:30:45’) extract;

extract	extract	extract
2020	202007	123045
其中，unit 用于指定返回那一部分信息，包括年、月、日、时、分、秒等；完整的取值可以参考官方文档。

YEAR(date) 函数用于返回日期中的年份。例如：

select year(‘2020-07-26’),year(‘0000-01-01’);

year(‘2020-07-26’)	year(‘0000-01-01’)

          2020|                 0|

YEARWEEK(date) 和 YEARWEEK(date,mode) 函数返回日期 date 所属的年分和周数。例如：

select yearweek(‘2020-01-01’),yearweek(‘2020-01-01’, 3);

yearweek(‘2020-01-01’)	yearweek(‘2020-01-01’, 3)

            201952|                   202001|

参数 mode 决定了每周第一天是星期天还是星期一，以及每一天属于哪一年的第几周。默认情况（mode 为 0）下，2020-01-01 属于 2019 年第 52 周；mode 取值为 3 时，2020-01-01 属于 2020 年第 1 周。关于 mode 参数的具体介绍，参考下文中的 WEEK() 函数。

QUARTER(date) 函数用于返回日期 date 所属的季节，取值返回从 1 到 4。例如：

select quarter(‘2020-07-26’);

quarter(‘2020-07-26’)

                3|

MONTH(date) 函数返回日期 date 所在的月份，MONTHNAME(date) 函数用于返回日期 date 所属的月份名称。例如：

select month(‘2020-07-26’), monthname(‘2020-07-26’);

month(‘2020-07-26’)	monthname(‘2020-07-26’)

              7|七月                    |

显示月份名称的语言由系统变量 lc_time_names 控制，中文为“zh_CN”。
WEEK(date[,mode]) 函数用于返回日期 date 属于一年中的第几周，参数 mode 用于控制一周的第一天是星期天还是星期一，以及每一天属于哪一年的第几周。mode 的可能取值和行为如下：

下图是 2020 年 1 月份的日历（星期天为每周的第一天）：

以下示例演示了不同 mode 参数的作用：

select week(‘2020-01-01’), week(‘2020-01-01’, 2), week(‘2020-01-01’, 4), week(‘2020-01-01’, 6);

week(‘2020-01-01’)	week(‘2020-01-01’, 2)	week(‘2020-01-01’, 4)	week(‘2020-01-01’, 6)

             0|                   52|                    1|                    1|

默认的 mode（0）由系统变量 default_week_format 的值决定，此时 2020-01-01 属于 2020 年的第 0 周；mode 为 2 时 2020-01-01 属于 2019 年的第 52 周；mode 为 4 或者 6 时 2020-01-01 属于 2020 年的第 1 周。

WEEKOFYEAR(date) 函数返回 date 所属的日历周（1-53），等价于 WEEK(date,3) 。

DAYOFYEAR(date) 函数返回 date 是一年中的第几天（1-366），DAY(date) 和 DAYOFMONTH(date) 函数返回 date 属于一月中的第几天（0-31），DAYOFWEEK(date) 函数返回 date 是一星期中的第几天（1 = 星期天）。例如：

select dayofyear(‘2020-07-26’), dayofmonth(‘2020-07-26’), dayofweek(‘2020-07-26’);

dayofyear(‘2020-07-26’)	dayofmonth(‘2020-07-26’)	dayofweek(‘2020-07-26’)

                208|                      26|                      1|

DAYNAME(date) 函数返回 date 是星期几。例如：

select dayname(‘2020-07-26’);

dayname(‘2020-07-26’)
星期日
显示星期名称的语言由系统变量 lc_time_names 控制，中文为“zh_CN”。
WEEKDAY(date) 函数返回 date 属于一周中的第几天，星期一是 0，星期天是 6。例如：

select weekday(‘2020-07-26’);

weekday(‘2020-07-26’)

                6|

LAST_DAY(date) 函数返回 date 所在月份的最后一天。例如：

select last_day(‘2020-07-26’);

last_day(‘2020-07-26’)

        2020-07-31|

HOUR(time) 函数返回 time 中的小时部分。例如：

select hour(‘2020-07-26 10:00:00’), hour(‘30:20:10’);

hour(‘2020-07-26 10:00:00’)	hour(‘30:20:10’)

                     10|              30|

MINUTE(time) 函数返回 time 中的分钟。例如：

select minute(‘2020-07-26 10:20:30’), minute(‘30:20:10’);

minute(‘2020-07-26 10:20:30’)	minute(‘30:20:10’)

                       20|                20|

SECOND(time) 函数返回 time 中的秒数（0-59），MICROSECOND(expr) 函数返回 time 中的毫秒。例如：

select second(‘10:20:30’), microsecond(‘30:20:10.123456’);

second(‘10:20:30’)	microsecond(‘30:20:10.123456’)

            30|                        123456|

16.4 将日期时间转换为其他类型

DATE_FORMAT(date,format) 函数将 date 格式化成字符串，参数 format 用于指定格式化字符串；它是 STR_TO_DATE() 的相反函数。例如：

select date_format(now(), ‘%W %d %M %Y, %r’);

date_format(now(), ‘%W %d %M %Y, %r’)
星期一 27 七月 2020, 03:29:19 AM
其中，百分号开始的字符是格式说明符，具体特定的意义。不同的格式说明符和意义如下：

%a，星期的简写（Mon、一）
%b，月份名称的简写（Jan、1月）
%c，数字形式的月份（0-12）
%D，一个月中的第几天，带英文后缀（0th、1st、2nd、…）
%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、一月）
%m，月份（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），星期天是一周的第一天；对应 WEEK() 函数中的 mode = 0
%u，一年中的第几周（00-53），星期一是一周的第一天；对应 WEEK() 函数中的 mode = 1
%V，一年中的第几周（01-53），星期天是一周的第一天；对应 WEEK() 函数中的 mode = 2；和 %X 一起使用
%v，一年中的第几周（01-53），星期一是一周的第一天；对应 WEEK() 函数中的 mode = 3；和 %x 一起使用
%W，星期的名称（Monday、星期一）
%w，数字形式的星期（0=星期天、6=星期六）
%X，当前周所在的年份，星期天是一周的第一天；和 %V 一起使用
%x，当前周所在的年份，星期一是一周的第一天；和 %v 一起使用
%Y，4 位数的年份
%y，2 位数的年份
%%，字面值 %
%x，除了以上列出的字符之外的字面值 x
TIME_FORMAT(time,format) 函数将 time 格式化成字符串，参数 format 用于指定格式化字符串。例如：

select time_format(now(), ‘%r’);

time_format(now(), ‘%r’)
04:24:49 AM
TIME_TO_SEC(time) 函数将 time 转换为秒数。例如：

select time_to_sec(‘1:00:00’);
TO_DAYS(date) 函数返回从第 0 年到 date 以来的天数。例如：

select to_days(‘2020-07-26’), from_days(737997);

to_days(‘2020-07-26’)	from_days(737997)

           737997|       2020-07-26|

TO_DAYS() 函数和 FROM_DAYS() 函数一样，不支持公历出现之前的日期（1582 年）。

TO_SECONDS(expr) 函数返回从第 0 年到 expr 以来的秒数。例如：

select to_seconds(‘2020-07-26 00:00:00’);

to_seconds(‘2020-07-26 00:00:00’)

                  63762940800|

TO_SECONDS() 函数和 TO_DAYS() 函数一样，不支持公历出现之前的日期（1582 年）。

UNIX_TIMESTAMP([date]) 函数返回 date 对应的 Unix 时间戳（自 ‘1970-01-01 00:00:00’ UTC 以来的秒数），省略参数 date 表示当前时间对应的时间戳。例如：

select unix_timestamp(), unix_timestamp(now());

unix_timestamp()	unix_timestamp(now())

  1595796384|           1595796384|

UNIX_TIMESTAMP() 是 FROM_UNIXTIME() 函数的相反操作。

GET_FORMAT({DATE|TIME|DATETIME}, {‘EUR’|‘USA’|‘JIS’|‘ISO’|‘INTERNAL’}) 函数返回指定的格式字符串，通常与 DATE_FORMAT() 和 STR_TO_DATE() 函数一起使用。例如：

select date_format(‘2020-07-26’,get_format(date,‘USA’)) date_format,
str_to_date(‘2020-07-26 20:30:00’,GET_FORMAT(DATETIME,‘ISO’)) str_to_date;

date_format	str_to_date
07.26.2020	2020-07-26 20:30:00
第一个 GET_FORMAT() 函数返回了 USA 的日期格式字符串，第二个GET_FORMAT() 函数返回了 ISO 的日期时间格式字符串。

16.5 日期时间的加减运算

ADDDATE(date,INTERVAL expr unit)、ADDDATE(expr,days)、DATE_ADD(date,INTERVAL expr unit) 函数为日期时间增加一个时间间隔。例如：

select adddate(‘2020-01-01’, interval 1 hour), date_add(‘2020-01-01’, interval 1 hour), adddate(‘2020-01-01’, 1);

adddate(‘2020-01-01’, interval 1 hour)	date_add(‘2020-01-01’, interval 1 hour)	adddate(‘2020-01-01’, 1)
2020-01-01 01:00:00	2020-01-01 01:00:00	2020-01-02
参数 INTERVAL expr unit 可以指定不同的时间间隔，具体可以参考官方文档。

SUBDATE(date,INTERVAL expr unit)、SUBDATE(expr,days)、DATE_SUB(date,INTERVAL expr unit) 函数从日期时间中减去一个时间间隔。例如：

select subdate(‘2020-01-01’, interval 1 day), subdate(‘2020-01-01’, 1), date_sub(‘2020-01-01’, interval 1 day);

subdate(‘2020-01-01’, interval 1 day)	subdate(‘2020-01-01’, 1)	date_sub(‘2020-01-01’, interval 1 day)
2019-12-31	2019-12-31	2019-12-31
日期时间数据也可以使用 + 或者 - 直接进行加减运算。例如：

select ‘2020-01-01’ + interval 1 day, ‘2020-01-01 10:00:00’ - interval 1 hour;

‘2020-01-01’ + interval 1 day	‘2020-01-01 10:00:00’ - interval 1 hour
2020-01-02	2020-01-01 09:00:00
ADDTIME(expr1,expr2) 函数为 expr1 增加指定的时间 expr2，SUBTIME(expr1,expr2) 为 expr1 减去指定的时间 expr2。例如：

select addtime(‘2020-01-01 00:00:00’, ‘10:00:00’), subtime(‘10:00:00’, ‘10:00:00’);

addtime(‘2020-01-01 00:00:00’, ‘10:00:00’)	subtime(‘10:00:00’, ‘10:00:00’)
2020-01-01 10:00:00	00:00:00
PERIOD_ADD(P,N) 函数为一个时间段 P（不是 date 类型）增加指定的月数，返回格式为 YYYYMM。例如：

select period_add(202001, 6);

period_add(202001, 6)

           202007|

TIMESTAMPADD(unit,interval,datetime_expr) 函数为 datetime_expr 增加一个指定的时间间隔。例如：

select timestampadd(day, 1, ‘2020-07-26’), timestampadd(minute, 10, ‘2020-07-26’);
unit 用于指定时间间隔的单位，可以为 MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或者 YEAR。

DATEDIFF(expr1,expr2) 函数计算两个日期之间的天数（expr1 − expr2）。例如：

select datediff(‘2020-01-02’,‘2020-01-01’), datediff(‘2020-01-02 00:00:00’,‘2020-01-01 23:59:59’);

datediff(‘2020-01-02’,‘2020-01-01’)	datediff(‘2020-01-02 00:00:00’,‘2020-01-01 23:59:59’)

                              1|                                                    1|

datetime 类型中的时间部分不参与计算。

PERIOD_DIFF(P1,P2) 函数返回两个时间段（P1 和 P2 不是 date 类型）之间相差的月数（P1-P2）。例如：

select period_diff(000002, 000001);

period_diff(000002, 000001)

                      1|

TIMEDIFF(expr1,expr2) 函数计算两个时间点之间相差的时间（expr1-expr2）。例如：

select timediff(‘2020-07-26 23:59:59’, ‘2020-07-25 00:00:00’);

timediff(‘2020-07-26 23:59:59’, ‘2020-07-25 00:00:00’)

                                          47:59:59|

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 函数返回两个日期时间之间相差的时间间隔（datetime_expr2 − datetime_expr1），unit 用于指定时间间隔的单位，可以为 MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 或者 YEAR。例如：

select timestampdiff(second,‘1970-01-01 08:00:00’, ‘2020-07-26’), unix_timestamp(‘2020-07-26’);

timestampdiff(second,‘1970-01-01 08:00:00’, ‘2020-07-26’)	unix_timestamp(‘2020-07-26’)

                                           1595692800|                  1595692800|

16.6 时区转换

CONVERT_TZ(dt,from_tz,to_tz) 函数用于将日期时间从一个时区转换为另一个时区。例如：

select convert_tz(‘2020-07-26 00:00:00’,‘+00:00’,‘+08:00’);

convert_tz(‘2020-07-26 00:00:00’,‘+00:00’,‘+08:00’)