MYSQL 日期函数 一些心得

写在前面

参加工作之后，发现自己学的那点东西完全不够用，遂将所学知识一一记录，一方面方便自己随时查阅，另一方面希望能帮到有需要的人，必须狠狠的发扬互联网的分享精神（笑）

从NOW()开始的日期函数

不出意外的话，用的最多的日期函数应该就是now了，他返回一个语句开始执行的时间

select now();

上述语句的执行结果如下：

注意

• 这个时间与数据库的时区设定相关
• 如果你是用WSL用来辅助测试开发的，电脑中间如果待机过，那数据库和系统的时间有可能会被冻结，只有再次唤醒后才会继续，可以用ntpdate同步一下时间后再继续测试开发

用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)，其中星期日是一周的第一天； WEEK()模式 0
%u	周 ( 00.. 53)，其中星期一是一周的第一天； WEEK()方式一
%V	周 ( 01.. 53)，其中星期日是一周的第一天； WEEK()方式二；与 %X
%v	周 ( 01.. 53)，其中星期一是一周的第一天； WEEK()模式 3；与 %x
%W	工作日名称 ( Sunday.. Saturday)
%w	星期几（0=Sunday.. 6=Saturday）
%X	Year for week where Sunday is the first day of the week, numeric, 四位数字；与%V
%x	Year for week，其中Monday为一周的第一天，numeric，四位；与%v
%Y	年份，数字，四位数字
%y	年份，数字（两位数）
%%	文字%字符
%x	x, 对于上面未列出的任何 “ x”

看着挺多挺吓人，但实际上常用的也就那几个，用例子来说明更好理解一点

获取今天的年月日

select date_format(now(), "%Y-%m-%d");

语句结果如下

获取语句执行时的时分秒（24小时制）

select date_format(now(), "%H:%i:%s");

语句结果如下：

获取当天0点

select date_format(now(), "%Y-%m-%d 00:00:00");

语句结果如下：

用DATE_ADD和DATE_SUB获取特定时间

日期时间这些东西，在表里是经常能见到的，无论是手工建立还是程序生成，在项目中都多多少少会遇到，一个比较经典的例子就是服务的起始时间和终止时间，就好比你手机APP里的会员，你买一月就是到下月的今天截止，你买一年就是到明年今天截止，应用到数据库里面，可能就是一个start_time，一个end_time，然后各自对应一个datetime，但如果每次都要自己计算然后手工插入，浪费时间不说还容易出错，这里date_add和date_sub就要派上用场了，这两个函数可以分别用来计算某个日期时间加上、减去特定时间间隔的日期时间，比如下面这个例子：

获取下个月今天的日期

 select date_add(date_format(now(), "%Y-%m-%d"), interval 1 month);

执行结果如下：

这里涉及到了interval间隔表达式，用法就是interval expr uint，这个expr unit就是时间间隔，下表提供了所有可选的值

unit值	预期expr格式
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

看着也不少，常用的其实也不多，上几个例子：

获取30分钟后的时间

select date_format(now(), "%Y-%m-%d %H:%i:%s"),
date_add(date_format(now(), "%Y-%m-%d %H:%i:%s"), interval 30 minute);

执行结果如下：

这里有个坑，如果你不带年月日的话。。。。

这两个函数支持互相嵌套，就比如下面这个例子

获取明年今天的前一天

select date_format(now(), "%Y-%m-%d %H:%i:%s"),subdate((select adddate((select date_format(now(), '%Y-%m-%d %H:%i:%s')), interval 1 year)), interval 1 day);

注意这里subdate和adddate与date_sub和date_add是同义同功能的（官方文档有提到）
执行结果如下：