侧边栏壁纸
  • 累计撰写 93 篇文章
  • 累计创建 85 个标签
  • 累计收到 9 条评论

MySQL函数操作之日期和时间函数

bearjun
2022-01-16 / 0 评论 / 0 点赞 / 270 阅读 / 7,353 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-04-11,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

我们都知道,MySQL的日期函数是我们开发用的比较多操作,那MySQL到底有多少函数呢?我们慢慢往下看。

先看下官方文档地址:https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

我们看看官方的文档,发现MySQL关于操作时间的函数还真不少,其中我们工作中大部分都用不到,具体用到的时候再查询文档即可。

下面我们来一一看看各种函数,建议收藏保存,用到的时候直接过来查询。

获取日期、时间

函数用法
CURDATE() ,CURRENT_DATE()返回当前日期,只包含年、月、日
CURTIME() , CURRENT_TIME()返回当前时间,只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP()返回当前系统日期和时间
UTC_DATE()返回UTC(世界标准时间)日期
UTC_TIME()返回UTC(世界标准时间)时间
# 返回当前日期,只包含年、月、日
SELECT CURDATE(), CURRENT_DATE();
# 2022-01-16 | 2022-01-16

# 返回当前时间,只包含时、分、秒
SELECT CURTIME(), CURRENT_TIME();
# 14:45:10 | 14:45:10

# 返回当前系统日期和时间
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP(), LOCALTIME(), LOCALTIMESTAMP();
# 2022-01-16 14:47:10 | 2022-01-16 14:47:10 | 2022-01-16 14:47:10 | 2022-01-16 14:47:10| 2022-01-16 14:47:10

# 返回UTC(世界标准时间)日期
SELECT UTC_DATE();
# 2022-01-16

# 返回UTC(世界标准时间)时间
SELECT UTC_TIME();
# 06:47:10

其实获取日期用的最多的还是:CURDATE()NOW()SYSDATE()这几个

日期与时间戳的转换

函数用法
UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP()->1634348884
UNIX_TIMESTAMP(date)将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp)将UNIX时间戳的时间转换为普通格式的时间
# 以UNIX时间戳的形式返回当前时间
SELECT UNIX_TIMESTAMP();
# 1642316445

# 将时间date以UNIX时间戳的形式返回,当然也传指定格式的时间数据
SELECT UNIX_TIMESTAMP(now()), UNIX_TIMESTAMP('2022-01-16 15:02:04') ;
# 1642316524 | 1642316524

# 将UNIX时间戳的时间转换为普通格式的时间
SELECT FROM_UNIXTIME(1642316445), FROM_UNIXTIME(1642316524)
# 2022-01-16 15:00:45 | 2022-01-16 15:02:04

我觉者这个用的不是很多,但如果你的数据库存得就是当前时间的秒值,可以试着当前函数返回正常的时间。

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

函数用法
YEAR(date) / MONTH(date) / DAY(date)返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time)返回具体的时间值
MONTHNAME(date)返回月份:January,...
DAYNAME(date)返回星期几:MONDAY,TUESDAY.....SUNDAY
WEEKDAY(date)返回周几,注意,周一是0,依次类推,周日是6
QUARTER(date)返回日期对应的季度,范围为1~4
WEEK(date)、WEEKOFYEAR(date)返回一年中的第几周
DAYOFYEAR(date)返回日期是一年中的第几天
DAYOFMONTH(date)返回日期位于所在月份的第几天
DAYOFWEEK(date)返回周几,注意:从周日开始,周日是1,周一是2,依次类推
# 返回具体的日期值,当然指定时间也是可以的,当我们只指定时间的时候,年月日也可以查询出来
SELECT YEAR(NOW()), MONTH(now()), DAY(now()), YEAR('2022-01-16 15:02:04'), YEAR(CURTIME()), DAY(CURTIME());
# 2022 | 1 | 16 | 2022 | 2022 | 16

# 返回具体的时间值,我们指定带时间的可以,但是具体到天时候,时分秒都为0
SELECT HOUR(now()), MINUTE(now()), SECOND(now()), HOUR(CURTIME()), HOUR(CURRENT_DATE);
# 15 | 16 | 11 | 15 | 0

# 返回月份英语:当只有时间的时候,也可以返回月份
SELECT MONTHNAME(now()), MONTHNAME(CURRENT_TIME());
# January | January

# 返回星期几英语:MONDAY,TUESDAY.....SUNDAY,当只有时间时,也可以正常返回
SELECT DAYNAME(now()), DAYNAME(CURRENT_TIME());
# Sunday | Sunday

# 返回周几,注意,周一是0,依次类推,周日是6
SELECT WEEKDAY(now()), WEEKDAY(CURRENT_TIME());
# 6 | 6

# 返回日期对应的季度,范围为1~4
SELECT QUARTER(now()), QUARTER(CURRENT_TIME());
# 1 | 1

# 返回一年中的第几周
SELECT WEEK(now()),WEEK(CURRENT_TIME()), WEEKOFYEAR(now()), WEEKOFYEAR(CURRENT_TIME());
# 3 | 3 | 2 | 2

# 返回日期是一年中的第几天
SELECT DAYOFYEAR(now()), DAYOFYEAR(CURRENT_TIME());
# 16 | 16

# 返回日期位于所在月份的第几天
SELECT DAYOFMONTH(now()), DAYOFMONTH(CURRENT_TIME());
# 16 | 16

# 返回周几,注意:从周日开始,周日是1,周一是2,依次类推
SELECT DAYOFWEEK(now()), DAYOFWEEK(CURRENT_TIME());
# 1 | 1

如果说这么多函数记不住?那还有一个神奇的函数。

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

上述函数中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返回年和月
SELECT 
EXTRACT( MINUTE FROM NOW()), # 返回分钟数 40
EXTRACT( WEEK FROM NOW()), # 返回日期在一年中的第几个星期 3
EXTRACT( QUARTER FROM NOW()), # 返回日期在一年中的第几个季度 1
EXTRACT( MINUTE_SECOND FROM NOW()); # 返回分钟和秒值 4002

时间和秒钟转换的函数

函数用法
TIME_TO_SEC(time)将 time 转化为秒并返回结果值。转化的公式为: 小时 × 3600 + 分钟 × 60 + 秒
SEC_TO_TIME(seconds)将 seconds 描述转化为包含小时、分钟和秒的时间
# 将 time 转化为秒并返回结果值。转化的公式为: 小时 * 3600 + 分钟 * 60 + 秒
SELECT TIME_TO_SEC(now()), TIME_TO_SEC(CURRENT_TIME()), TIME_TO_SEC(CURRENT_DATE());
# 56825 | 56825 | 0

#将 seconds 描述转化为包含小时、分钟和秒的时间
SELECT SEC_TO_TIME(56825);
# 15:47:05

计算日期和时间的函数

函数用法
DATE_ADD(datetime, INTERVAL expr type)、ADDDATE(date,INTERVAL expr type)返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)返回与date相差INTERVAL时间间隔的日期

上述的tpye如下:

type取值含义
HOUR小时
MINUTE分钟
SECOND
YEAR
MONTH
DAY
YEAR_MONTH年和月
DAY_HOUR天和小时
DAY_MINUTE天和分钟
DAY_SECOND天和秒
HOUR_MINUTE小时和分钟
HOUR_SECOND小时和秒
MINUTE_SECOND分钟和秒
SELECT
ADDDATE(now(), INTERVAL 10 SECOND), # 当前时间加 10s
ADDDATE(now(), INTERVAL '10_30' MINUTE_SECOND), # 当前时间 分加10,秒加30
DATE_ADD(NOW(), INTERVAL -1 YEAR), # 当前时间 年减1
DATE_ADD(NOW(), INTERVAL '1_-3' YEAR_MONTH); #时间 年+1 , 月+3

通过上面我们可以知道,加或者减可以通过传的参数正副决定,但是两个时间的时候,引号不能丢掉。

你以为计算时间的函数就已经结束了?大错特错。

函数用法
ADDTIME(time1,time2)返回time1加上time2的时间。当time2为一个数字时,代表的是秒,可以为负数
SUBTIME(time1,time2)返回time1减去time2后的时间。当time2为一个数字时,代表的是秒 ,可以为负数
DATEDIFF(date1,date2)返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2)返回time1 - time2的时间间隔
FROM_DAYS(N)返回从0000年1月1日起,N天以后的日期
TO_DAYS(date)返回日期date距离0000年1月1日的天数
LAST_DAY(date)返回date所在月份的最后一天的日期
MAKEDATE(year,n)针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second)将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)返回time加上n后的时间
# 返回time1加上time2的时间。当time2为一个数字时,代表的是秒,可以为负数(但是实测0-59可行,60-99为空,100以上无规律)
SELECT ADDTIME(now(), '01:00:00'), ADDTIME(now(), 10), now();
# 2022-01-16 19:49:26 | 2022-01-16 18:49:36 | 2022-01-16 18:49:26

# 返回time1减去time2后的时间。当time2为一个数字时,代表的是秒 ,可以为负数(后面的结论同上)
SELECT SUBTIME(now(), '01:00:00'), SUBTIME(now(), 10), now();
# 2022-01-16 17:50:16 | 2022-01-16 18:50:35 | 2022-01-16 18:50:45

# 返回date1 - date2的日期间隔天数
SELECT DATEDIFF(now(), now()), DATEDIFF(now(), '2022-01-17');
# 0 | -1

# 返回time1 - time2的时间间隔(注意格式)
SELECT TIMEDIFF(now(), now()), TIMEDIFF(now(), '2022-01-17 00:00:00'), TIMEDIFF(now(), '2022-01-17') ;
# 00:00:00 |  -05:06:21 | 空

# 返回从0000年1月1日起,N天以后的日期(注意:小于365不计算)
SELECT FROM_DAYS(365), FROM_DAYS(366);
# 0000-00-00 | 0001-01-01

# 返回日期date距离0000年1月1日的天数
SELECT TO_DAYS(now()), TO_DAYS('0001-01-01');
# 738536 | 366

# 返回date所在月份的最后一天的日期
SELECT LAST_DAY(now()), LAST_DAY('2020-02-01');
# 2022-01-31 | 2020-02-29

# 针对给定年份与所在年份中的天数返回一个日期(其实就是当年的第一天加上后面的天数)
SELECT MAKEDATE(2022, 123), MAKEDATE(2022, 520);
# 2022-05-03 | 2023-06-04

# 将给定的小时、分钟和秒组合成时间并返回
SELECT MAKETIME(12, 34, 56);
# 12:34:56

# 返回time加上n后的时间(看着好懵)
SELECT PERIOD_ADD(20200101010101, 1);
# 20200101010102

上述有些方法确实很懵,如果有交流的可以下面留言。

日期的格式化与解析

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

上述非GET_FORMAT函数中fmt参数常用的格式符:

符号说明符号说明
%Y4位数字表示年份%y表示两位数字表示年份
%M月名表示月份(Janury...)%m两位数字表示月份(01,02,03....)
%b缩写的月名(Jan., Feb., ...)%C数字表示月份(1,2,3....)
%D英文后缀表示月中的天数(1st,2nd,3rd...)%d两位数字表示月中的天数(01,2...)
%e数字形式表示月中的天数(1,2,4,....)
%H两位数字表示小数,24小时制(01,02..)%h两位数字表示小时,12小时制(01,02..)
%k数字形式的小时,24小时制(1,2,3)%l数字形式表示小时,12小时制(1,2,3,4....)
%i两位数字表示分钟(00,01,02)%S和%s两位数字表示秒00,01,02..
%W一周中的星期名称 (Sunda...)%a一周中的星期缩写(Sun.Mon.,Tues., .. )
%w以数字表示周中的天数(0=Sunday,1=Monday....)
%j以3位数字表示年中的天数(001,002...)%U以数字表示年中的第几周,(1,2,3...)其中Sunday为周中第一天
%u以数字表示年中的第几周,(1,2,3...)其中Monday为周中第一天
%T24小时制%r12小时制
%pAM或PM%%表示%

GET_FORMAT函数中date_type和format_type参数取值如下:

类型标志时间格式
DATEUSA%m.%d.%Y
DATEJIS%Y-%m-%d
DATEISO%Y-%m-%d
DATEEUR%d.%m.%Y
DATEINTERNAL%Y%m%d
TIMEUSA%h:%i:%s %p
TIMEJIS%H:%i:%s
TIMEISO%H:%i:%s
TIMEEUR%H.%i.%s
TIMEINTERNAL%H%i%s
DATETIMEUSA%Y-%m-%d %H.%i.%s
DATETIMEJIS%Y-%m-%d %H:%i:%s
DATETIMEISO%Y-%m-%d %H:%i:%s
DATETIMEEUR%Y-%m-%d %H.%i.%s
DATETIMEINTERNAL%Y%m%d%H%i%s
# 按照字符串fmt格式化日期date值
SELECT DATE_FORMAT(now(), '%H:%i:%s'), DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s'), 
DATE_FORMAT(now(), '%Y-%y %M-%m-%c %e-%d'), 
DATE_FORMAT(now(), '%H-%h-%k-%l:%i:%S-%s');
# 19:30:50 | 2022-01-16 19:30:50 | 2022-22 January-01-1 16-16 | 19-07-19-7:30:50-50

#按照字符串fmt对str进行解析,解析为一个日期
SELECT STR_TO_DATE('2022|01|16','%Y|%m|%d'), STR_TO_DATE('2022-January-16','%Y-%M-%d');
# 2022-01-16 | 2022-01-16

# 返回日期字符串的显示格式
SELECT GET_FORMAT(DATE, 'ISO'), DATE_FORMAT(NOW(), GET_FORMAT(DATE,'ISO'));
# %Y-%m-%d | 2022-01-16

其实我感觉还是最后一个,格式化用的比较多和用的比较频繁。

0

评论区