MySQL中日期加减（前一天、后一天等）以及格式化的函数

序言

查询条件中使用时间区间作为筛选条件，难免的会碰到对日期的操作，如获取前一天、后一天、一周前、一个月前等，索性整理一下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');