Mysql日期时间函数汇总

常用的mysql时间函数记录

Posted by Hyuga on December 13, 2018

前言

写sql的时候,经常需要处理一些日期时间格式或者差值计算等,有些常用的函数记录下,以备后面用到。

常用表达式

格式 描述
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%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) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位
  • 年月日时分秒:%Y-%m-%d %H:%i:%s
  • 年月日:%Y-%m-%d
  • 时分秒:%T

时间差函数

DATE_FORMAT

时间格式化转字符串

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
result:2019-01-04 19:37:46

STR_TO_DATE

字符串格式化转时间

SELECT STR_TO_DATE('2018-12-11', '%Y-%m-%d');
result:2018-12-11
SELECT STR_TO_DATE('2018-12-11 23:59:59', '%Y-%m-%d %H:%i:%s');
result:2018-12-11 23:59:59
TO_DAYS

时间转天数

SELECT TO_DAYS(NOW());
result:737428
含义:就是从0年开始 到当前时间之间的天数

注:这个粒度的查询是比较粗糙的

TIMESTAMPDIFF

计算两个时间多维度的差值

TIMESTAMPDIFF函数,有参数设置,可以精确到天(DAY)、小时(HOUR),分钟(MINUTE)和秒(SECOND),使用起来比datediff函数更加灵活。对于比较的两个时间,时间小的放在前面,时间大的放在后面。

语法:TIMESTAMPDIFF(interval, datetime_expr1, datetime_expr2)

结果:返回(时间2-时间1)的时间差,结果单位由interval参数给出。

DEMO

# 毫秒(低版本不支持,用second,再除于1000[MySQL 5.6之后才支持毫秒的记录和计算]
SELECT TIMESTAMPDIFF(FRAC_SECOND,'2012-10-01','2013-01-13');# 8985600
# 
SELECT TIMESTAMPDIFF(SECOND,'2012-10-01','2013-01-13');# 8985600
# 
SELECT TIMESTAMPDIFF(MINUTE,'2012-10-01','2013-01-13'); # 149760
# 
SELECT TIMESTAMPDIFF(HOUR,'2012-10-01','2013-01-13'); # 2496
# 
SELECT TIMESTAMPDIFF(DAY,'2012-10-01','2013-01-13'); # 104
# 
SELECT TIMESTAMPDIFF(WEEK,'2012-10-01','2013-01-13'); # 14
# 
SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13'); # 3
# 季度
SELECT TIMESTAMPDIFF(QUARTER,'2012-10-01','2013-01-13'); # 1
# 
SELECT TIMESTAMPDIFF(YEAR,'2012-10-01','2013-01-13'); # 0

DATEDIFF

计算两个时间的差值,比较粗糙,只能比较天数,不如TIMESTAMPDIFF函数

datediff函数,返回值是相差的天数,不能定位到小时、分钟和秒。

datediff(datetime_expr1, datetime_expr2)

传入两个日期参数,比较DAY天数,第一个参数减去第二个参数的天数值。

SELECT DATEDIFF(now(), now());
result:0
SELECT DATEDIFF('2015-04-22 23:59:59', '2015-04-20 00:00:00');
result:2
SELECT DATEDIFF('2015-04-22 00:00:00', '2015-04-20 23:59:00');
result:2

TIMESTAMPADD

时间增量函数

TIMESTAMPADD(interval,int_expr,datetime_expr)

将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中。式中的interval和上文中列举的取值是一样的。

可用于时间增量

SELECT TIMESTAMPADD(YEAR,1,'2018-12-11 09:00:00'); # 2019-12-11 09:00:00
SELECT TIMESTAMPADD(QUARTER,1,'2018-12-11 09:00:00'); # 2019-03-11 09:00:00
SELECT TIMESTAMPADD(MONTH,1,'2018-12-11 09:00:00'); # 2019-01-11 09:00:00
SELECT TIMESTAMPADD(WEEK,1,'2018-12-11 09:00:00'); # 2018-12-18 09:00:00
SELECT TIMESTAMPADD(DAY,1,'2018-12-11 09:00:00'); # 2018-12-12 09:00:00
SELECT TIMESTAMPADD(HOUR,1,'2018-12-11 09:00:00'); # 2018-12-11 10:00:00
SELECT TIMESTAMPADD(MINUTE,60,'2018-12-11 09:00:00'); # 2018-12-11 10:00:00
SELECT TIMESTAMPADD(SECOND,60,'2018-12-11 09:00:00'); # 2018-12-11 09:01:00

DATE_ADD

日期加法函数

DATE_ADD(date,INTERVAL expr type)

SELECT DATE_ADD(NOW(), INTERVAL 2 YEAR); # 2021-01-04 19:45:25
SELECT DATE_ADD(NOW(), INTERVAL 2 QUARTER); # 2019-07-04 19:45:20
SELECT DATE_ADD(NOW(), INTERVAL 2 MONTH); # 2019-03-04 19:45:16
SELECT DATE_ADD(NOW(), INTERVAL 2 DAY); # 2019-01-06 19:45:12
SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR); # 2019-01-04 21:45:08
SELECT DATE_ADD(NOW(), INTERVAL 2 WEEK); # 2019-01-18 19:45:04
SELECT DATE_ADD(NOW(), INTERVAL 2 MINUTE); # 2019-01-04 19:46:59
SELECT DATE_ADD(NOW(), INTERVAL 2 SECOND); # 2019-01-04 19:44:57
SELECT DATE_ADD(NOW(), INTERVAL 2 MICROSECOND); # 2019-01-04 19:44:48.000002

DATE_SUB

日期减法函数

DATE_SUB(date,INTERVAL expr type)

SELECT DATE_SUB(NOW(), INTERVAL 2 YEAR); # 2017-01-04 19:46:25
SELECT DATE_SUB(NOW(), INTERVAL 2 QUARTER); # 2018-07-04 19:46:22
SELECT DATE_SUB(NOW(), INTERVAL 2 MONTH); # 2018-11-04 19:46:18
SELECT DATE_SUB(NOW(), INTERVAL 2 DAY); # 2019-01-02 19:46:13
SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR); # 2019-01-04 17:46:10
SELECT DATE_SUB(NOW(), INTERVAL 2 WEEK); # 2018-12-21 19:46:06
SELECT DATE_SUB(NOW(), INTERVAL 2 MINUTE); # 2019-01-04 19:44:02
SELECT DATE_SUB(NOW(), INTERVAL 2 SECOND); # 2019-01-04 19:45:57
SELECT DATE_SUB(NOW(), INTERVAL 2 MICROSECOND); # 2019-01-04 19:45:55.999998

EXTRACT(unit FROM date)

从日期中抽取出某个单独的部分或组合

  • SELECT NOW(), extract(YEAR FROM NOW()); – 年:2019-01-04 19:46:56/2019
  • SELECT NOW(), extract(QUARTER FROM NOW()); – 季度:2019-01-04 19:46:56/1
  • SELECT NOW(), extract(MONTH FROM NOW()); – 月:2019-01-04 19:46:56/1
  • SELECT NOW(), extract(WEEK FROM NOW()); – 周:2019-01-04 19:46:56/0
  • SELECT NOW(), extract(DAY FROM NOW()); – 日:2019-01-04 19:46:56/4
  • SELECT NOW(), extract(HOUR FROM NOW()); – 小时:2019-01-04 19:46:56/19
  • SELECT NOW(), extract(MINUTE FROM NOW()); – 分钟:2019-01-04 19:46:56/46
  • SELECT NOW(), extract(SECOND FROM NOW()); – 秒:2019-01-04 19:46:56/56
  • SELECT NOW(), extract(YEAR_MONTH FROM NOW()); – 年月:2019-01-04 19:46:56/201901
  • SELECT NOW(), extract(HOUR_MINUTE FROM NOW()); – 时分:2019-01-04 19:46:56/1946

常用组合函数

-- 用日期与字符串转换,计算当月第一天、下月第一天
SELECT
    CURDATE() AS '当前日期',
    DATE_FORMAT(CURDATE(), '%Y-%m') AS '当前月份',
    STR_TO_DATE(CONCAT(DATE_FORMAT(CURDATE(), '%Y-%M'), '-01'), '%Y-%M-%D') AS '当前月的第一天',
    DATE_ADD(STR_TO_DATE(CONCAT(DATE_FORMAT(CURDATE(), '%Y-%M'), '-01'), '%Y-%M-%D'), INTERVAL 1 MONTH) AS '下月的第一天';

-- 当前月的最后一天
SELECT LAST_DAY(CURDATE());

-- 下月第一天
SELECT DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY);

-- 当天为当月的第几天
SELECT DAY(CURDATE());

-- 当月第一天
SELECT DATE_ADD(CURDATE(), INTERVAL 1-(DAY(CURDATE())) DAY);

日期是第几天?

  • DAYOFWEEK(date):一周内第几天
  • DAYOFMONTH(date):一个月内第几天
  • DAYOFYEAR(date):一年内第几天
SELECT DAYOFWEEK(NOW());
SELECT DAYOFMONTH(NOW());
SELECT DAYOFYEAR(NOW());
  • DAYNAME(date):日期是周几的名称
  • MONTHNAME(date):日期是第几月的名称
SELECT DAYNAME(NOW());
SELECT MONTHNAME(NOW());

其他日期函数

  • SELECT NOW(); # 返回当前的日期和时间
  • SELECT CURDATE(); # 返回当前日期
  • SELECT SYSDATE(); # 返回当前日期
  • SELECT CURTIME(); # 返回当前时间
  • SELECT CURRENT_TIME(); # 返回当前时间
  • SELECT CURRENT_TIMESTAMP; # 返回当前时间
  • SELECT CURRENT_TIMESTAMP(); # 返回当前时间
  • SELECT UNIX_TIMESTAMP(); # 返回当前时间戳
  • SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); # 返回UNIX时间戳的日期值
  • SELECT YEAR(NOW()); # 返回日期date的年份
  • SELECT QUARTER(NOW()); # 返回日期date的年份
  • SELECT MONTH(NOW()); # 返回日期date的月份
  • SELECT MONTHNAME(NOW()); # 返回日期date的月份名
  • SELECT DAY(NOW()); # 返回当前日期的天数
  • SELECT DATE(NOW()); # 将当前时间转换为当前日期
  • SELECT TIME(NOW()); # 将当前时间转换为当前时分秒
  • SELECT WEEK(NOW()); # 返回日期date为一年中的第几周
  • SELECT HOUR(NOW()); # 返回日期date的小时
  • SELECT MINUTE(NOW()); # 返回日期date的分钟
  • SELECT SECOND(NOW()); # 返回日期date的秒
  • SELECT MICROSECOND(NOW()); # 返回日期date的微秒
  • SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d’); # 返回按字符串格式化的date值
  • LAST_DAY(NOW()); # 返回date所在月最后一天,如果参数无效,返回NULL

注意:

now()与sysdate()类似,只不过now()在执行开始时就获取,而sysdate()可以在函数执行时动态获取。