MYSQL基础之函数：日期，时间函数

上一篇演示一些数值函数还有字符串函数，这个一篇聊日期函数。

获取日期时间

这个主要得到的是当前日期的一些函数。

函数	作用
CURDATE() / CURRENT_DATE()	返回当前日期，只能包含年月日，默认格式是yyyy-mm-dd
CURTIME() / CURRENT_TIME()	返回当前时间，只包含时，分，秒默认格式是hh:mm:ss
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前日期时间，默认格式 yyyy-mm-dd hh:mm:ss
UTC_DATE()	返回UTC(世界标准时间) 日期 （而我们采用的是东八区 ）
UTC_TIME()	返回UTC(世界标准时间) 时间而我们采用的是东八区 ）

现在具体演示

SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME() FROM  DUAL;

SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() FROM DUAL;

SELECT  UTC_DATE(),UTC_TIME() FROM DUAL;

日期和时间戳的转换

这个很常见操作日期和时间戳的转换。

函数	作用
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前的时间。
UNIX_TIMESTAMP(date)	将时间dat以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp)	将UNIX时间戳转换为日期

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('12,11,22'),FROM_UNIXTIME(UNIX_TIMESTAMP('12,11,22')),UNIX_TIMESTAMP('12-11-22'),FROM_UNIXTIME(UNIX_TIMESTAMP('12-11-22')),UNIX_TIMESTAMP('2021-11-22 14:12:22') FROM DUAL;

其实这个UNIX_TIMESTAMP涉及到一个隐式转换，为了更好的理解，其参数最好写：yyyy-mm-dd hh:mm:ss格式。

获取月份，星期，天数等函数

函数	作用
YEAR(date) / MONTH(date) / DAY(date)	返回对应日期的年份/月份/日
HOUR(date) / MINUTE(date) / SECOND(date)	返回对应的日期的小时/分钟/秒
MONTHNAME(date)	返回这个日期是月份名字（英语）
DAYNAME(date)	返回这个日期是星期几名字（英语）
WEEKDAY(date)	返回一周种的第几天，其值：0-6 这个是从0开始的，周一为0
QUARTER(date)	对应的季度 其值：1-4
WEEK(date) / WEEKOFYEAR(date)	返回日期是一年种第二周
DAYOFYEAR(date)	返回年中第多少天
DAYOFMONTH(date)	返回月中第多少天
DAYOFWEEK(date)	返回周中第多少天，不过其周日为1，周一为2

还是通过伪表演示。

SELECT YEAR('2021-12-01 14:12:13'),MONTH('2021-12-01 14:12:13'),DAY('2021-12-01 14:12:13'),HOUR('2021-12-01 14:12:13'),MINUTE('2021-12-01 14:12:13') ,SECOND('2021-12-01 14:12:13')   FROM DUAL;

SELECT MONTHNAME('2021-12-01 14:12:13'),DAYNAME('2021-12-01 14:12:13'),WEEKDAY('2021-12-01 14:12:13'),QUARTER('2021-12-01 14:12:13') FROM DUAL;

SELECT WEEK('2021-12-01 14:12:13'),WEEKOFYEAR('2021-12-01 14:12:13'),DAYOFYEAR('2021-12-01 14:12:13'),DAYOFMONTH('2021-12-01 14:12:13'),DAYOFWEEK('2021-12-01 14:12:13') FROM DUAL;

日期操作函数

这个是对日期进行操作，现在看一下这个函数

函数	作用
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指返回的值

这个先了解type中参数的意义：

参数	作用
MICROSECOND	返回毫秒数
SECOND	返回秒数
MINUTE	返回分钟数
HOUR	返回小时数
DAY	返回天数
WEEK	返回日期在一年中的第几个星期
MONTH	返回日期在一年中的第几个月
QUARTER	返回日期在一年中的第几个季度
YEAR	返回日期的年份
SECOND_MICROSECOND	返回秒数和毫秒数
MINUTE_MICROSECOND	返回分钟和毫秒数
MINUTE_SECOND	返回分钟和秒数
HOUR_MICROSECOND	返回小时和毫秒数
HOUR_SECOND	返回小时和秒数
HOUR_MINUTE	返回小时和分钟
DAY_MICROSECOND	返回天和毫秒数
DAY_SECOND	返回天和秒数
DAY_MINUTE	返回天和分钟
DAY_HOUR	返回天和小时
YEAR_MONTH	返回年和月

还是伪表演示，但是不会演示所有的，毕竟其用法就是修改type而已。

SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(HOUR FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT( DAY_HOUR FROM NOW() ) FROM DUAL;

时间和秒钟转换函数

函数	作用
TIME_TO_SEC(time)	将时间格式转换为秒，其值：小时*3600+分钟*60+秒
SEC_TO_TIME(seconds)	将秒转换为小时分钟秒格式的时间。

现在伪表演示：

SELECT CURTIME(),TIME_TO_SEC(CURTIME()),SEC_TO_TIME(TIME_TO_SEC(CURTIME())) FROM  DUAL;

计算日期和时间的函数

这些都是对时间和日期进行操作的函数。

日期与间隔

给一个定值日期，然后算出其间隔某单位的时间后或者后的日期。

函数	作用
DATE_ADD(date, INTERVAL num type) / ADDDATE(date, INTERVAL num type)	date日期再加上num个type单位后的日期
DATE_SUB(date, INTERVAL num type) / SUBDATE(date, INTERVAL num type)	date日期再减去num个type单位后的日期

现在看一下参数 type ：

参数	作用
HOUR	间隔单位：小时
MINUTE	间隔单位：分钟
SECOND	间隔单位：秒
YEAR	间隔单位：年
MONTH	间隔单位：月
DAY	间隔单位：天
YEAR_MONTH	间隔单位：年和月
DAY_HOUR	间隔单位：天和小时
DAY_MINUTE	间隔单位：天和分钟
DAY_SECOND	间隔单位：天和秒
HOUR_MINUTE	间隔单位：小时和分钟
HOUR_SECOND	间隔单位：小时和分钟
MINUTE_SECOND	间隔单位：分钟和秒

现在演示一下：

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 DAY),SUBDATE(NOW(),INTERVAL 1 DAY),ADDDATE(NOW(),INTERVAL -1 DAY),SUBDATE(NOW(),INTERVAL -1 DAY)      FROM DUAL;

可以看出两者是相对的两种操作，但是如果各自传递的数值为负数的事后，又可以达到其相同的效果。

SELECT NOW(),DATE_ADD(NOW(),INTERVAL '1_2' YEAR_MONTH) FROM DUAL;

日期与日期

函数	作用
ADDTIME(date,time)	返回date加上time后的日期，如果为time是数字的时候，代表的是秒，也可以为负数。还可以格式hh:mm:ss
SUBTIME(date1,time)	和ADDTIME刚好相反。
DATEDIFF(date1,date2)	返回date1-date2的日期间隔天数。
TIMEDIFF(date1,date2)	返回date1-date2的日期间时间间隔。
FROM_DAYS(n)	返回距离0000年1月1日n天后的日期
TO_DAYS(date)	返回date距离0000年1月1日的天数
LAST_DAY(date)	返回date所在月份的最后一天的日期
MAKEDATE(year,n)	返回某年year中n天日期
MAKETIME(hour,minute,second)	将指定的小时，分钟和秒组成一时间
PERIOD_ADD(time,n)	返回time加上n后的时间

SELECT NOW(),ADDTIME(NOW(),' 1:1'),SUBTIME(NOW(),-1100) FROM DUAL;
-- 这个和操作间隔的差不多，都可以通过负数进行反向操作。

SELECT DATEDIFF('2021-12-12 13:11:11','2021-12-11 10:11:11'), TIMEDIFF('2021-12-12 13:11:11','2021-12-11 10:11:11'),TIMEDIFF('2021-12-12','2021-12-11'),TIMEDIFF('11:3:40','10:1:2') FROM DUAL;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xEPcfnPW-1640092604786)(F:\文档\笔记\数据库\mysql\6_1：函数分类–日期和时间.assets\image-20211221205005232.png)]

SELECT FROM_DAYS(1000),TO_DAYS(NOW()),LAST_DAY(NOW()),MAKEDATE("2021-10-11",4) FROM DUAL;

日期格式化

前面一直说mysql默认的日期格式是yyyy-mm-dd。但有时候我们会对格式进行调整，也就是修改器呈现的样式，所以就需要下面的函数。

函数	作用
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,fmt_type)	返回日期字符串显示格式
STR_TO_DATE(str,fmt)	按照字符串fmt对str进行解析，解析为一个日期

函数中一直说fmt，fmt自然会用到格式符号，其如下：

参数	作用
%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 位

现在开始演示：

SELECT NOW(),DATE_FORMAT(NOW(),'%Y-%M-%D'), DATE_FORMAT(NOW(),'%Y-%m-%d') FROM DUAL;

SELECT GET_FORMAT(DATE, 'USA') , GET_FORMAT(DATE, 'ISO'),DATE_FORMAT(NOW(),GET_FORMAT(DATE, 'USA'))  FROM DUAL;

SELECT STR_TO_DATE('2021-December-21st','%Y-%M-%D') FROM DUAL;