MySQL中日期加减(前一天、下个月等)以及格式化的函数
侧边栏壁纸
  • 累计撰写 51 篇文章
  • 累计收到 1 条评论

MySQL中日期加减(前一天、下个月等)以及格式化的函数

admin
2022-04-07 / 0 评论 / 23 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2022年04月07日,已超过302天没有更新,若内容或图片失效,请留言反馈。

序言

查询条件中使用时间区间作为筛选条件,难免的会碰到对日期的操作,如获取前一天、后一天、一周前、一个月前等,索性整理一下MySQL中的相关函数

日期的加减

DATE_ADD和DATE_SUB

语法为:DATE_ADD(date,interval expr type)、DATE_SUB(date,interval expr type)
其中常用的type的类型有:second、minute、hour、day、month、year等

DATE_ADD是对日期的增加,如果天数为负数时,则表示对日期减少,
DATE_SUB是对日期的减少,如果天数为负数时,则表示对日期增加

详细说明请查看官方文档

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

可灵活使用

例如:

-- 获取当前日期 2020-04-07
SELECT CURDATE();
-- 获取当前时间 15:24:01
SELECT CURTIME();
-- 获取当前日期加时间 2020-04-07 23:10:30
SELECT NOW();
-- 获取明天的日期 2020-04-08 获取日期时间格式将CURDATE()替换成NOW()即可
SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY);
-- 或者 其他获取月、年方法同样就不重复写了
SELECT DATE_SUB(CURDATE(), INTERVAL -1 DAY);
# 获取下个月、明年将DAY替换成对应MONTH、YEAR即可
SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
-- 获取明年的日期 2021-04-07
SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR);
-- 获取减去一天2小时
SELECT DATE_ADD('2022-02-04 20:00:00', INTERVAL '-1 2' DAY_HOUR);
# -> '2022-02-03 18:00:00'
# 获取减去一天1小时1分钟1秒
SELECT DATE_SUB('2025-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
# -> '2024-12-30 22:58:59'

日期/时间转换为字符串(格式化):

date_format

语法为:date_format(date,format),date 参数是合法的日期。format 规定日期/时间的输出格式。
常用的格式有:

格式描述
%Y年,4 位
%y年,2 位
%m月,数值(00-12)
%M月名
%D带有英文前缀的月中的天
%d月的天,数值(00-31)
%H小时 (00-23)
%h小时 (01-12)
%i分钟,数值(00-59)
%S秒(00-59)
%s秒(00-59)
-- 格式化当前日期 2020-04-07 23:23:23
date_format(now(),'%Y-%m-%d %H:%i:%s' )
-- 格式化当前时间 23:23:23
time_format(CURTIME(),'%H:%i:%s') 

字符串转换为日期:

str_to_date

这是DATE_FORMAT()函数的逆函数。它接受一个字符串str和一个格式字符串format。如果格式字符串同时包含日期和时间部分,STR_TO_DATE()返回一个DATETIME值;如果字符串只包含日期或时间部分,则返回一个date或time值。str中包含的日期、时间或datetime值应该以format指定的格式给出。关于可以在格式中使用的说明符,请参阅DATE_FORMAT()函数描述。如果str包含非法的日期、时间或datetime值

STR_TO_DATE(STR, FORMAT)

SELECT STR_TO_DATE('08/11/2018', '%M/%D/%Y'); -- 2018-08-11

SELECT STR_TO_DATE('08/11/08' , '%M/%D/%Y'); -- 2018-08-11

SELECT STR_TO_DATE('08.11.2008', '%M.%D.%Y'); -- 2018-08-11

SELECT STR_TO_DATE('08:00:30', '%H:%I:%S'); -- 08:00:30

SELECT STR_TO_DATE('08.11.2018 08:00:30', '%M.%D.%Y %H:%I:%S');

日期的差值

datediff

DATEDIFF(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数(date2-date1,正负情况都存在)。date1 和 date2 为日期或 date-and-time 表达式,计算差值时只会计算日期的差值,单位为天。

-- 当前时间2020-04-08,差值为-2
SELECT DATEDIFF(NOW(),'2020-04-10') 
-- 当前时间2020-04-08,差值为2
SELECT DATEDIFF(NOW(),'2020-04-06') 

timestampdiff

语法为:TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)。
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。
常用的值有:

  • FRAC_SECOND。表示间隔是毫秒
  • SECOND。秒
  • MINUTE。分钟
  • HOUR。小时
  • DAY。天
  • WEEK。星期
  • MONTH。月
  • QUARTER。季度
  • YEAR。年
-- now()值为 2020-04-08 23:20:20
SELECT TIMESTAMPDIFF(DAY,NOW(),'2020-04-10 23:23:23') 
-- 结果为2,相差两天,取整数
-- 其他单位同理

Unix 时间戳转换:

# 1533956241 (s)
SELECT UNIX_TIMESTAMP(); 
 
# 1533916800
SELECT UNIX_TIMESTAMP('2018-08-11'); 
 
# 1533961800
SELECT UNIX_TIMESTAMP('2018-08-11 12:30:00'); 
 
# 2018-08-11 10:57:21
SELECT FROM_UNIXTIME(1533956241);
 
# 2018-08-11 00:00:00
SELECT FROM_UNIXTIME(1533916800); 
 
# 2018-08-11 12:30:00
SELECT FROM_UNIXTIME(1533961800); 
 
# 2018 11th August 12:30:00 2018
SELECT FROM_UNIXTIME(1533961800, '%Y %D %M %h:%i:%s %x');
2

打赏

海报

正在生成.....

评论 (0)

取消