MYSQL五大类函数,前面已经介绍了聚合函数、字符串函数和数值型函数,还有时间日期函数和控制流函数,这篇介绍一下日期时间函数,日期和时间函数主要用于处理日期和时间,其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。
这一篇主要介绍常用的日期和时间函数,然后后面会给出常用的计算(本)月初、月末,本周、上周初末、昨日今日等时间的表达式。
函数名称 |
作用 |
CURDATE和CURRENT_DATE |
两个函数作用相同,返回当前系统的日期值 |
CURTIME和CURRENT_TIME |
两个函数作用相同,返回当前系统的时间值 |
NOW和SYSDATE |
两个函数作用相同,返回当前系统的日期和时间值 |
MONTHNAME |
获取指定日期中的月份英文名称 |
DAYNAME |
获取指定日期对应的星期几的英文名称 |
DAYOFWEEK |
获取指定日期对应的一周的索引位置值 |
DAYOFYEAR |
获取指定曰期是一年中的第几天 |
DAYOFMONTH |
获取指定日期是一个月中是第几天 |
WEEK |
获取指定日期是一年中的第几周 |
MONTH |
获取指定日期中的月份 |
YEAR |
获取年份 |
DATE_ADD和ADDDATE |
两个函数功能相同,都是向日期添加指定的时间间隔 |
DATE_SUB和SUBDATE |
两个函数功能相同,都是向日期减去指定的时间间隔 |
ADDTIME |
时间加法运算,在原始时间上添加指定的时间 |
SUBTIME |
时间减法运算,在原始时间上减去指定的时间 |
DATEDIFF |
获取两个日期之间间隔 |
DATE_FORMAT |
格式化指定的日期,根据参数返回指定格式的值 |
WEEKDAY |
获取指定日期在一周内的对应的工作日索引 |
【CURDATE和CURRENT_DATE】
CURDATE和CURRENT_DATE获取系统当前日期。可以通过"CURDATE()+0"将当前日期值转换为数值型。
SELECT CURDATE(),CURRENT_DATE(),CURDATE()+0
【CURTIME和CURRENT_TIME】
CURTIME和CURRENT_TIME获取系统当前时间。通过"CURRENT_TIME()+0"将当前时间值转换为数值型。
SELECT CURTIME(),CURRENT_TIME(),CURTIME()+0
【NOW和SYSDATE】
NOW和SYSDATE获取当前系统的日期和时间。NOW()函数始终获取的是SQL语句开始执行的时间,而 SYSDATE()函数则是动态获取的实时时间。
SELECT NOW(),SYSDATE()
【MONTHNAME】
使用MONTHNAME()函数返回指定日期中月份的名称。
SELECT birthday, MONTHNAME( birthday ) FROM wangfalin;
【DAYNAME】
使用DAYNAME()函数返回指定日期的工作日名称。
SELECT birthday, DAYNAME( birthday ) FROM wangfalin;
【DAYOFWEEK】
使用DAYOFWEEK()函数返回日期对应的周索引,星期天为开始1。
SELECT birthday, DAYOFWEEK(birthday) FROM wangfalin;
【DAYOFYEAR】
DAYOFYEAR()函数返回是一年中的第几天,范围为1~366。
SELECT birthday, DAYOFYEAR(birthday) FROM wangfalin;
【DAYOFMONTH】
DAYOFMONTH()函数返回是一个月中的第几天,范围为1~31。
SELECT birthday, DAYOFMONTH(birthday) FROM wangfalin;
【WEEK】
WEEK()函数计算日期 date 是一年中的第几周。WEEK(date,mode)函数允许指定星期是否起始于周日或周一,以及返回值的范围是否为 0~52 或 1~53。
- date是要获取周数的日期。
- mode是一个可选参数,用于确定周数计算的逻辑。
模式 |
一周的第一天 |
范围 |
0 |
星期日 |
0-53 |
1 |
星期一 |
0-53 |
2 |
星期日 |
1-53 |
3 |
星期一 |
1-53 |
4 |
星期日 |
0-53 |
5 |
星期一 |
0-53 |
6 |
星期日 |
1-53 |
7 |
星期一 |
1-53 |
SELECT birthday, WEEK(birthday,3) FROM wangfalin;
【MONTH】
MONTH(date)函数返回指定日期对应的月份,范围为1~12。
SELECT birthday, MONTH(birthday) FROM wangfalin;
【YEAR】
YEAR()函数从指定日期值中来获取年份值。
SELECT birthday, YEAR(birthday) FROM wangfalin;
【DATE_ADD和ADDDATE】
DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type) 两个函数的作用相同,都是用于执行日期的加运算。
- date是DATE或DATETIME的起始值
- INTERVAL expr 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 DATE_ADD('2021-05-24 23:26:59',INTERVAL 1 SECOND) AS C1, DATE_ADD('2021-05-24 23:26:59',INTERVAL '1:1' MINUTE_SECOND) AS C2, ADDDATE('2021-05-24 23:26:59',INTERVAL -1 DAY) AS C3;
【DATE_SUB和SUBDATE】
DATE_SUB(date,INTERVAL expr type)和SUBDATE(date,INTERVAL expr type)两个函数作用相同,都是执行日期的减法运算。
- date 是 DATE 或 DATETIME 的起始值
- expr 是一个字符串,用于确定从起始日期减去的间隔值。type 是 expr 可解析的间隔单位,例如DAY,HOUR等
SELECT DATE_SUB('2021-05-24 23:26:59',INTERVAL 1 SECOND) AS C1, DATE_SUB('2021-05-24 23:26:59',INTERVAL '1:1' MINUTE_SECOND) AS C2, SUBDATE('2021-05-24 23:26:59',INTERVAL -1 DAY) AS C3;
【ADDTIME、SUBTIME】
ADDTIME(time,expr)函数用于执行时间的加、减法运算。添加expr到time并返回结果。
SELECT ADDTIME('2021-05-24 21:26:59','1:2:3')
SELECT SUBTIME('2021-05-24 21:26:59','1:2:3')
【DATEDIFF】
DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或date-time表达式。计算时只用到这些值的日期部分。
SELECT DATEDIFF('2021-11-30','2021-11-29') AS COL1, DATEDIFF('2021-11-30','2021-12-15') AS col2;
【DATE_FORMAT】
DATE_FORMAT(date,format)函数是根据format指定的格式显示date值。
- date:是要格式化的有效日期值
- format:是由预定义的说明符组成的格式字符串,每个说明符前面都有一个百分比字符(%)
%a |
工作日的缩写名称(Sun~Sat) |
%b |
月份的缩写名称(Jan…Dec) |
%c |
月份,数字形式(0~12) |
%D |
带有英语后缀的该月日期(0th, 2st, 3nd,…) |
%d |
该月日期,数字形式(00~31) |
%e |
该月日期,数字形式((0~31) |
%f |
微秒(000000 …999999) |
%H |
以 2 位数表示 24 小时(00~23) |
%h, %I |
以 2 位数表示 12 小时(01~12) |
%i |
分钟,数字形式(00~59) |
%j |
—年中的天数(001~366) |
%k |
以 24 小时(0~23)表示 |
%l |
以12小时(1~12)表示 |
%M |
月份名称(January~December) |
%m |
月份,数字形式(00~12) |
%p |
上午(AM) 或下午(PM) |
%r |
时间,12小时制(小时 (hh): 分钟 (mm) : 秒数 (ss) 后加 AM 或 PM) |
%S, %s |
以 2 位数形式表示秒(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 birthday, DATE_FORMAT(birthday,'%Y%m %M') FROM wangfalin;
【WEEKDAY】
WEEKDAY(d)返回d对应的工作日索引。0 表示周一,1 表示周二,……,6 表示周日。
SELECT birthday, WEEKDAY(birthday) FROM wangfalin;
【常用日期表示方法】
【今天】
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS '今天开始', DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS '今天结束';
【昨天】
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS '昨天开始', DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS '昨天结束';
【上周】
SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS '上周一', DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS '上周末';
【本周】
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS '本周一', DATE_FORMAT( DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS '本周末';
【上月】
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS '上月初', DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS '上月末';
【本月】
SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') AS '本月初', DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS '本月末';
End