【MySQL】关于日期相关的函数操作

业务场景：出现于在做可视化、报表相关的接口时，我们需要接收前端的一个时间参数或者是默认当前的时间，前半年数据查询或者是求总和之类的。本篇主要想分享一些关于日期计算、转换的方法，后面会写一篇探讨如何优化的文章。

一、 date_format(date, format)

date是需要转化的日期数据，format定义的是转换格式。例如：

SELECT DATE_FORMAT('2000.6.1','%y-%m-%d %W')

还可以这样：

SELECT DATE_FORMAT('2000.6.1','%Y年%m月%d日')
SELECT DATE_FORMAT('2000.6.8','%h%时-%m%分-%s%秒')

二、str_to_date(str,format)

str是需要转换的字符串数据，format定义的是转换格式即DATETIME类型的值，例如：
注意：str的格式需要和format的格式保持一致，否则结果为null；

SELECT STR_TO_DATE('2000-06-01 10:20:30','%Y-%m-%d')
SELECT STR_TO_DATE('2000-06-01 10:20:30','%Y-%m-%d %H:%i:%s')

三、format包含的格式

格式	描述
%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 位
%文字	展示文字，%文字

上面部分截选自 https://blog.csdn.net/qq_45844443/article/details/120936965

四、unix_timestamp(date) 函数

将时间转换为时间戳，如果参数为空，则处理的是当前的时间（返回从’1970-01-01 00:00:00’GMT开始的到当前时间的秒数，不为空则它返回从’1970-01-01 00:00:00’ GMT开始的到指定date的秒数值），date可以是一个DATE字符串、一个DATETIME字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。
例：

SELECT (SELECT UNIX_TIMESTAMP()) AS a,(SELECT UNIX_TIMESTAMP(NOW()))AS b

SELECT UNIX_TIMESTAMP('2022/06/01 10/20/30') AS a
SELECT UNIX_TIMESTAMP('10/20/30') AS b
SELECT UNIX_TIMESTAMP('1900/12/29 10:20:30') AS c

五、from_unixtime(unix_timestamp, format) 函数

MySQL时间戳格式化函数from_unixtime

SELECT FROM_UNIXTIME(1654050030,'%Y-%m-%d %H:%i:%s %W'); 

六、 current_time，current_date()与now()区别

current_date()只显示的是当前时间的日期

例如：
SELECT CURRENT_DATE()
结果：2022-05-24

current_time()只显示当前时间的时分秒

例如：
SELECT CURRENT_TIME()
11:32:35

now（）显示全部

例如：
SELECT NOW() 
结果：2022-05-24 11:33:11

在增加减少日期时current_time，current_date()类似与now()不同详细如下:
CURRENT_DATE()函数

#获取当前日期
SELECT CURRENT_DATE() AS newDate;    #2022-05-24
#当前日期加1天（其他天数方法一样）
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY) AS newDate;    #2022-05-25
#当前日期减1天
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) AS newDate;   #2022-05-23
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS newDate;   #2022-05-23
#当前日期加1个月
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL -1 MONTH) AS newDate;  #2022-06-24
#当前日期减1个月
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH) AS newDate; #2022-04-24
#当前日期加1年
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 1 YEAR) AS newDate;   #2023-05-24
#当前日期减1年
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AS newDate;  #2021-05-24

#指定日期加1天（可将DAY换为MONTH,YEAR来实现加1个月,1年）
SELECT DATE_ADD('2022-05-20', INTERVAL 1 DAY) AS newDate;    #2022-05-21
#指定日期减1月
SELECT DATE_SUB('2022-05-21', INTERVAL 1 MONTH) AS newDate;   #2022-04-21

#当前时间添加1小时10分钟10秒钟
SELECT DATE_ADD(NOW(), INTERVAL '1:10:10' HOUR_SECOND) AS newTime;    #2022-05-24 12:51:45
#指定时间添加1年10个月
SELECT DATE_ADD('2019-04-17 2:00:00', INTERVAL '1:10' YEAR_MONTH) AS newTime;    #2021-02-17 02:00:00

NOW()函数

#获取当前时间
SELECT NOW() AS newTime;    
#当前时间减30秒
SELECT (NOW() - INTERVAL 30 SECOND) AS newTime;    
#当前时间加30秒
SELECT (NOW() + INTERVAL 30 SECOND) AS newTime;    
#当前时间减30分钟
SELECT (NOW() - INTERVAL 30 MINUTE) AS newTime;    
#当前时间加30分钟
SELECT (NOW() + INTERVAL 30 MINUTE) AS newTime;    
#当前时间减1天
SELECT (NOW() - INTERVAL 1 DAY) AS newTime;        
#当前时间加1天
SELECT (NOW() + INTERVAL 1 DAY) AS newTime;        
#当前时间减1个月
SELECT (NOW() - INTERVAL 1 MONTH) AS newTime;      
#当前时间加1个月
SELECT (NOW() + INTERVAL 1 MONTH) AS newTime;      
#当前时间减1年
SELECT (NOW() - INTERVAL 1 YEAR) AS newTime;       
#当前时间加1年
SELECT (NOW() + INTERVAL 1 MONTH) AS newTime;      

#指定时间的加减，将上面的NOW()函数换为指定日期时间即可，以加30分钟为例，如下:
SELECT ('2022-05-20 12:30:00' + INTERVAL 30 MINUTE) AS newTime;        #2022-05-20 13:00:00

本文部分截选自 https://blog.csdn.net/weixin_47343544/article/details/119910041，如有侵权，请联系删除。