MySQL中常用的日期处理方法总结

前言

  上一章回顾复习了一下MySQL中比较常用的函数，MySQL中的函数大大方便了我们的操作。在项目中经常操作数据库的时间，相信有部分小伙伴在操作数据库时间的时候可能会遇到各种各样的问题，那么这一章就简单介绍一下MySQL中时间操作的相关函数和经验。

SQL中的时间函数

  SQL中的时间函数为我们提供了处理日期的相关函数，常用的函数有NOW()、CURDATE()、CURTIME()、DATE()、EXTRACT()、DATE_ADD()、DATE_SUB()、DATEDIFF()、DATE_FORMAT()，其中DATE_FORMAT在实际使用中是比较多的，也是比较复杂的，所以会重点介绍DATE_FORMAT()按指定格式去显示时间。

获取当前时间NOW()、CURDATE()、CURTIME()

  MySQL中NOW()、CURDATE()、CURTIME()这三个函数都是获取当前时间的函数。三个方法都是获取当前时间的，但是三个函数又有不同点。其中NOW()是获取的当前的年月日+时分秒，示例：2021-11-25 22:26:45；CURDATE()是获取的当前的年月日，示例：2021-11-25；CURTIME()获取的是时分秒，示例：22:26:45。下面执行SQL，查看其中的不同点:

SELECT NOW(),CURDATE(),CURTIME()

DATE()

  DATE()函数的主要作用是提取日期或日期的时间表达式的日期部分。类似于CURDATE()函数，取得是年月日。一般在DATE(时间表达式的列)输出年月日。 例如输出商品表中商品创建的年月日和商品的名称。

SELECT goods_name,DATE(create_time) FROM goods

执行结果如下：

EXTRACT()

  EXTRACT()主要作用是返回日期时间表达式中的指定的单独部分。例如数据库中列时间表达式为2021-11-25 22:26:45可以指定只返回月份11。其语法格式为： EXTRACT(指定的时间单位 FROM 时间表达式列名) 示例：输出商品表中商品创建的月份和商品名称。

SELECT goods_name,EXTRACT( MONTH FROM  create_time) FROM goods

  其中指定的时间单位和测试如下： 时间以商品表中商品id为1的商品的创建时间为标准表达式进行测试。执行SQL如下：

SELECT EXTRACT( 时间单位格式 FROM  create_time) FROM goods WHERE id =1

时间单位格式	描述	标准时间表达式	执行结果值
MICROSECOND	微秒	2021-11-24 23:19:54	0
SECOND	秒	2021-11-24 23:19:54	54
MINUTE	分钟	2021-11-24 23:19:54	19
HOUR	小时	2021-11-24 23:19:54	23
DAY	天数	2021-11-24 23:19:54	24
WEEK	星期	2021-11-24 23:19:54	47
MONTH	月份	2021-11-24 23:19:54	11
QUARTER	季度	2021-11-24 23:19:54	4
YEAR	年份	2021-11-24 23:19:54	2021
SECOND_MICROSECOND	秒到微秒级别	2021-11-24 23:19:54	54000000
MINUTE_MICROSECOND	分钟到微秒级别	2021-11-24 23:19:54	1954000000
MINUTE_SECOND	分钟到秒级别	2021-11-24 23:19:54	1954
HOUR_MICROSECOND	小时到微妙	2021-11-24 23:19:54	231954000000
HOUR_SECOND	小时和秒	2021-11-24 23:19:54	231954
HOUR_MINUTE	小时和分钟	2021-11-24 23:19:54	2319
DAY_MICROSECOND	天到微妙级别	2021-11-24 23:19:54	24231954000000
DAY_SECOND	天到秒级别	2021-11-24 23:19:54	24231954
DAY_MINUTE	天到分钟级别	2021-11-24 23:19:54	242319
DAY_HOUR	天到小时级别	2021-11-24 23:19:54	2423
YEAR_MONTH	年月	2021-11-24 23:19:54	202111

DATE_ADD()

  DATE_ADD() 函数的主要功能是向指定的日期添加指定的时间间隔。其支持的时间单位格式与EXTRACT()支持的时间单位格式是一致的。其语法是： SELECT DATE_ADD(标准时间格式,INTERVAL 时间间隔数值 时间单位格式) FROM 表名 查询条件

SELECT goods_name,create_time,DATE_ADD(create_time,INTERVAL 3 DAY) FROM goods WHERE id =1

  例如：查询商品表中的商品id为1的商品名称和创建时间，创建时间在原有基础上加3天，其执行结果是2021-11-27 23:19:54，如下图：

DATE_SUB()

  DATE_SUB() 函数与DATE_ADD()函数正好相反，DATE_SUB()的主要功能是向指定的日期减去指定的时间间隔。其支持的时间单位格式与EXTRACT()支持的时间单位格式是一致的。其语法是： SELECT DATE_SUB(标准时间格式,INTERVAL 时间间隔数值 时间单位格式) FROM 表名 查询条件

SELECT goods_name,create_time,DATE_SUB(create_time,INTERVAL 3 DAY) FROM goods WHERE id =1

  例如：查询商品表中的商品id为1的商品名称和创建时间，创建时间在原有基础上减去3天，其执行结果是2021-11-21 23:19:54，如下图：

DATEDIFF()

  DATEDIFF() 函数主要作用是返回两个日期之间的天数。只有日期的部分参与计算，时分秒的时间不参与计算。其语法格式是：SELECT DATEDIFF(时间1,时间2)

SELECT DATEDIFF('2021-11-25','2021-01-01') 

其执行结果是：

DATE_FORMAT()

  DATE_FORMAT()函数的主要功能是输出指定格式化的时间进行显示。其语法结构是：

DATE_FORMAT(日志,需要显示的日期格式)

  示例其中日期取值是商品表中的商品id为1的商品创建的时间。其SQL如下：

SELECT goods_name,create_time,DATE_FORMAT(create_time,"%a") FROM goods WHERE id =1

执行结果截图如下：

  由于经常使用到相关日期格式化显示，而DATE_FORMAT()的日期格式表达式又比较复杂，因此在表格中列出来了，方便给大家演示参考。

格式	描述	日期	输出示例
%a	缩写星期名	2021-11-24 23:19:54	Wed
%b	缩写月名	2021-11-24 23:19:54	Nov
%c	月，数值	2021-11-24 23:19:54	11
%D	带有英文前缀的月中的天	2021-11-24 23:19:54	24th
%d	月的天，数值(00-31)	2021-11-24 23:19:54	24
%e	月的天，数值(0-31)	2021-11-24 23:19:54	24
%f	微秒	2021-11-24 23:19:54	000000
%H	小时 (00-23)	2021-11-24 23:19:54	23
%h	小时 (01-12)	2021-11-24 23:19:54	11
%I	小时 (01-12)	2021-11-24 23:19:54	11
%i	分钟，数值(00-59)	2021-11-24 23:19:54	19
%j	年的天 (001-366)	2021-11-24 23:19:54	328
%k	小时 (0-23)	2021-11-24 23:19:54	23
%l	小时 (1-12)	2021-11-24 23:19:54	11
%M	月名	2021-11-24 23:19:54	November
%m	月，数值(00-12)	2021-11-24 23:19:54	11
%p	AM 或 PM	2021-11-24 23:19:54	PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）	2021-11-24 23:19:54	11:19:54 PM
%S	秒(00-59)	2021-11-24 23:19:54	54
%s	秒(00-59)	2021-11-24 23:19:54	54
%T	时间, 24-小时 (hh:mm:ss)	2021-11-24 23:19:54	23:19:54
%U	周 (00-53) 星期日是一周的第一天	2021-11-24 23:19:54	47
%u	周 (00-53) 星期一是一周的第一天	2021-11-24 23:19:54	47
%V	周 (01-53) 星期日是一周的第一天，与%X 使用	2021-11-24 23:19:54	47
%v	周 (01-53) 星期一是一周的第一天，与%x 使用	2021-11-24 23:19:54	47
%W	星期名	2021-11-24 23:19:54	Wednesday
%w	周的天 （0=星期日, 6=星期六）	2021-11-24 23:19:54	3
%X	年，其中的星期日是周的第一天，4 位，与%V 使用	2021-11-24 23:19:54	2021
%x	年，其中的星期一是周的第一天，4 位，与%v 使用	2021-11-24 23:19:54	2021
%Y	年，4 位	2021-11-24 23:19:54	2021
%y	年，2 位	2021-11-24 23:19:54	21

结语

  好了，以上就是MySQL中常用的日期处理方法总结，感谢您的阅读，希望您喜欢，如对您有帮助，欢迎点赞收藏。如有不足之处，欢迎评论指正。下次见。

  作者介绍：【小阿杰】一个爱鼓捣的程序猿，JAVA开发者和爱好者。公众号【Java全栈架构师】维护者，欢迎关注阅读交流。