mysql日期函数总结

文中代码未特殊说明时使用的mysql版本为5.7
文档地址:https://www.mysqlzh.com/doc/118.html

日期格式化、字符串转日期

select date_format(now(), '%Y-%m-%d %H:%i:%s');
select str_to_date('2020-09-24 15:20:23', '%Y-%m-%d %H:%i:%s');
select from_unixtime(unix_timestamp(now()), '%Y-%m-%d %H:%i:%s');

date_format全部符号

说明符说明
%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)或下午( 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位数)
%%‘%’文字字符

获取时间

常用now(),在一个单一询问中,对诸如NOW() 的函数多次访问总是会得到同样的结果,这项原则也适用于 CURDATE()、 CURTIME()、 UTC_DATE()、 UTC_TIME()、UTC_TIMESTAMP(),以及所有和它们意义相同的函数。于此相对应的是SYSDATE()

SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE();

在这里插入图片描述

常用获取时间函数的区别

  • now():返回当前时间,格式为YYYY-MM-DD HH:MM:SS,在一个单一询问中总是会得到同样的结果。
  • sysdate():与now()大致相同,区别在于同一查询中可能返回不同值(见上节)
  • curdate():与now()大致相同,区别在于格式为YYYY-MM-DD

对于返回值,系统会根据语境为数字或字符串而返回不同的默认格式。比如now()在默认(字符串语境下)的返回格式为YYYY-MM-DD HH:MM:SS,但是在数字语境下则为YYYYMMDDHHMMSS。

mysql> SELECT NOW();
        -> '2020-09-24 22:59:21'

mysql> SELECT NOW() + 0;
        -> 20200924230005

mysql> SELECT NOW() + NOW();
        -> 40401848460050

具体规则就是将默认格式中的空格与符号消除,并用剩下的数字进行计算。所以mysql无法简单地对时间进行计算,而是要借助相应的时间函数。

日期计算

增加日期

增加日期date_add,减少日期date_sub,以及他们的同义词adddate、subdate。

个人建议只使用date_add即可,因为每一个函数都可以轻松取代其他三个,并且同一功能却要记三个函数实在有些变态。

SELECT date_add('2020-09-24', INTERVAL 1 DAY) as "增加一天",
       date_add(now(), INTERVAL -1 DAY) as "减少一天",
	   date_add('2020-09-24', INTERVAL (1 + substr(version(), 1, 1) * 2) DAY) as "使用函数表达式"
;

date_add中是可以使用函数表达式的,但是只可以使用整数,否则系统会自动四色五入为整数进行计算。

select case when date_add(now(), INTERVAL 0.4 DAY) = now() then '相等' else '不相等' end;#相等

所以对于每种类型的时间格式,都需要指定类型进行计算。

select date_add(now(), interval 1 day); 
select date_add(now(), interval 1 hour); 
select date_add(now(), interval 1 minute); 
select date_add(now(), interval 1 second);
select date_add(now(), interval 1 microsecond);
select date_add(now(), interval 1 week);
select date_add(now(), interval 1 month);
select date_add(now(), interval 1 quarter);
select date_add(now(), interval 1 year);

还有一种更加省略的写法

select '2020-09-25' + INTERVAL 1 DAY,
	   now() + INTERVAL 1 DAY
;

这回连date_add都没了,具体用哪种,自己选择吧。

求日期差

主要用到三个函数
DATEDIFF、TIMEDIFF、TIMESTAMPDIFF。

DATEDIFF只能返回整数天,TIMEDIFF对参数限制过于严格,并且返回格式为hh24:mi:ss,范围还限制在了-838:59:59到838:59:59,实在鸡肋。

TIMESTAMPDIFF在使用上有些麻烦,但至少可以实现功能。

#求出精确到小时的天
SELECT TIMESTAMPDIFF(hour, str_to_date('2020-09-24', '%Y-%m-%d'), now()) / 24;
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页