一、时间表达式
时间间隔表达式的语法:
INTERVAL expr unit
expr指的是时间的间隔数量,通常是规定的时间表达式。
unit 指的是时间间隔的单位,比如年月日等等
注意:建议将expr传入为字符串,谨慎传入数字。例如’6/4’是6个小时4分钟,而6/4是1个小时5000分钟。如果实际需求中需要传入数值,建议用cast将6/4进行转化。例如 INTERVAL CAST(6/4 AS DECIMAL(2,1))。
关于表达式中所有的unit和exper格式,如下图:
所以我们的表达式就可以这样子来写。INTERVAL 1 HOUR.。表示一个小时。至于是往前推一个小时还是往后推一个小时,那就需要看我们具体的sql函数。时间表达式和时间间隔表达式在时间函数中是很重要的一部分。所以我们必须牢记。要注意不要将时间表达式和时间间隔表达式闹混。
二、时间函数
1.ADDDATE()
语法:ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
描述:将传入时间向后推移表达式所指定的时间间隔。他有两种写法,第一种需要我们自己输入时间间隔表达式。第二种只需要填入数值,但它指定的时间间隔为天。
应用:
SELECT ADDDATE(‘2020-10-06’,INTERVAL 1 DAY)
SELECT ADDDATE(‘2020-10-06’,1)
2.ADDTIME()
语法:ADDTIME(expr1,expr2)
描述:将传入时间向后推移传入的时间表达式。expr1指的是传入时间,expr2指的是时间表达式。要注意的是,ADDTIME()不支持时间间隔表达式
应用:
SELECT ADDTIME(‘2020-10-06 00:00:00’, ‘1 01:30:00’)
SELECT ADDTIME(‘2020-10-06 00:00:00’, ‘01:30:00’)
3.CONVERT_TZ
语法:CONVERT_TZ(dt,from_tz,to_tz)
描述:将时间从给定的from_tz时区转化到指定的to_tz时区。可以理解成把一个时间从中国时间变成了美国时间。
应用:
SELECT CONVERT_TZ(‘2020-09-06 12:00:00’,’+00:00’,’+08:00’)
4.获取当前日期
语法:CURDATE()、CURRENT_DATE、CURRENT_DATE()
描述:以上三个函数效果是一样的。都是用来获取当前日期。
应用:
以字符串形式返回当前日期
SELECT CURDATE()
SELECT CURRENT_DATE
SELECT CURRENT_DATE()
以数字形式返回当前日期
SELECT CURDATE() + 0
SELECT CURRENT_DATE + 0
SELECT CURRENT_DATE() + 0
注意:如果后面的0变成了其他任意数字,那么会在结果加上该数值。比如SELECT CURDATE() + 0返回是20201006,如果0变成了1,那么就会变成20201007。
5.获取当前时间
语法:CURTIME()、CURRENT_TIME、 CURRENT_TIME()
描述:以上三个函数的效果是一样的。都是用获取当前时间
应用:
以字符串形式返回当前时间
SELECT CURTIME()
SELECT CURRENT_TIME
SELECT CURRENT_TIME()
以数字形式返回当前日期
SELECT CURTIME() + 0
SELECT CURRENT_TIME + 0
SELECT CURRENT_TIME() + 0
6.获取当前日期时间
语法: CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()、NOW()、LOCALTIME()、LOCALTIME、
LOCALTIMESTAMP()、LOCALTIMESTAMP
描述:以上三个函数的效果都是一样的。都是用来获取当前日期时间
应用:
以字符串形式返回当前时间
SELECT CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP()
SELECT LOCALTIME()
SELECT LOCALTIME
SELECT LOCALTIMESTAMP()
SELECT LOCALTIMESTAMP
SELECT NOW()
SELECT NOW(3)
以数字形式返回当前日期
SELECT CURRENT_TIMESTAMP + 0
SELECT CURRENT_TIMESTAMP() + 0
SELECT LOCALTIME() + 0
SELECT LOCALTIME + 0
SELECT LOCALTIMESTAMP() + 0
SELECT LOCALTIMESTAMP + 0
SELECT NOW() + 0
7.DATE()
语法: DATE(expr)
描述:提取时间的日期部分,年月日
应用:
SELECT DATE(‘2020-10-06 00:00:00’)
SELECT DATE(20201006013000)
8.DATE_ADD()
语法:DATE_ADD(datetime,INTERVAL expr unit)
描述:与ADDDATE()作用相同。需要注意的是,它只有一种写法。也就是说不支持DATE_ADD(‘2020-10-06’,1)这种写法。
应用:
SELECT DATE_ADD(‘2020-10-06’,INTERVAL 1 DAY);
9.DATE_FORMAT()
语法:DATE_FROMAT(dateime,format)
描述:将日期时间格式的参数转换为指定格式的字符串。date指的是时间,format字符串格式。关于字符串格式如下:
名称 | 描述 | 应用 |
---|---|---|
%a | 日的英文缩写 | ![]() |
%b | 月份的英文缩写 | ![]() |
%c | 用数字表示月份。格式为0 | ![]() |
%D | 用英文后缀表示天 | ![]() |
%d | 用数字表示日。格式为00 | ![]() |
%e | 用数字表示日。格式为0 | ![]() |
%f | 用数字表示微妙 | ![]() |
%H | 用二十四小时制表示小时。格式为00 | ![]() |
%h | 用十二小时制表示小时。格式为00 | ![]() |
%I | 用十二小时制表示小时。格式为00 | ![]() |
%i | 用数字表示分钟 | ![]() |
%j | 用数字表示当前日期是第几天 | ![]() |
%k | 二十四小时制表示小时。格式为0 | ![]() |
%l | 十二小时制表示小时。格式为0 | ![]() |
%M | 用英文表示月份 | ![]() |
%m | 用数字表示月份。格式为00 | ![]() |
%p | 用来表示时间是AM还是PM。上午和下午 | ![]() |
%r | 用十二小时制表示时间。自带格式hh:mm:ss。相当于 %h:%i:%s %p | ![]() |
%S | 用数字表示秒 | ![]() |
%s | 用数字表示秒 | ![]() |
%T | 用二十小时制表示时间。自带格式hh:mm:ss。相当于%H:%i:%S | ![]() |
%U | 计算当前时间是第几周。星期日为一周的第一天 | ![]() |
%u | 计算当前时间是第几周。星期一为一周第一天 | ![]() |
%V | 计算当前时间是第几周。星期日为一周的第一天 | ![]() |
%v | 计算当前时间是第几周。星期一为一周第一天 | ![]() |
%W | 用英文表示当前时间是本周的第几天 | ![]() |
%w | 用数字表示当前时间是本周的第几天 | ![]() |
%X | 计算当前时间是第几周,返回当前时间的年份。星期日是一周的第一天 | ![]() |
%x | 计算当前时间是第几周,返回当前时间的年份。星期一是一周的第一天 | ![]() |
%Y | 用四位数表示年份 | ![]() |
%y | 用两位数表示年份 | ![]() |
10.DATE_SUB()
语法:DATE_SUB(datetime,INTERVAL expr unit)
描述:与SUBDATE()作用相同。需要注意的是,它只有一种写法。也就是说不支持DATE_SUB(‘2020-10-06’,1)这种写法。
应用:
SELECT DATE_SUB(‘2020-10-06’,INTERVAL 1 DAY);
11.DATEDIFF()
语法:DATEIDFF(expr1,expr2)
描述:用来计算两个日期的相差日期。expr1 - expr2。所以如果expr1小于expr2,就会变成负数。那么我们可以用ABS()函数变成整数。同时只计算日期部分,时间部分不被计算
应用:
值为负数
SELECT DATEDIFF(‘2020-10-06’,‘2020-10-07’)
用abs()是负数变成整数
SELECT ABS(DATEDIFF(‘2020-10-06’,‘2020-10-07’))
证明只计算日期部分
SELECT DATEDIFF(‘2020-10-07 10:30:00’,‘2020-10-06 00:00:00’)
15.DAY()
语法:DAY(datetime)
描述:返回传入参数的天
应用:
SELECT DAY(‘2020-10-06 00:00:00’)
16.DAYNAME()
语法:DAYNAME(date)
描述:返回时间的天,并用英文描述
应用:
SELECT DAYNAME(‘2020-10-06’)
17.DAYOFMONTH()
语法:DAYOFMONTH(datetime)
描述:返回传入参数中月份的某天
应用:
SELECT DAYOFMONTH(‘2020-10-06’)
18.DAYOFWEEK()
语法:DAYOFWEEK(datetime)
描述:返回传入参数是这周的第几天。星期日为第一天
应用:
SELECT DAYOFWEEK(‘2020-10-06’)
19.DAYOFYEAR()
语法:DAYOFYEAR(datetime)
描述:返回传入参数是今年的第几天
应用:
SELECT DAYOFYEAR(‘2020-10-06’)
20.EXTRACT()
语法:EXTRACT(unit FROM datetime)
描述:unit是时间表达式。date是传入的参数。作用是根据时间表达式截取传入参数
应用:
SELECT EXTRACT(YEAR FROM ‘2020-10-06’)
21.FROM_DAYS()
语法:FROM_DAYS(n)
描述:给定天数n。返回date格式的日期
应用:
SELECT FROM_DAYS(366)
22.FROM_UNIXTIME()
语法:FROM_UNIXTIME(unix_timestamp)、FROM_UNIXTIME(unix_timestamp,format)
描述:传入时间戳,返回标准date格式的日期;传入时间戳和format(时间字符串格式),返回指定的时间格式日期。
应用:
SELECT FROM_UNIXTIME(1601938635)
SELECT FROM_UNIXTIME(1601938635,’%Y-%m-%d %H:%i:%s’)
23.GET_FORMAT()
语法:GET_FORM({date、time、datetime},{USA、ISO…})
描述:返回指定格式的时间。通常配合DATE_FORMAT()和STR_TO_DATE()使用。
可以指定的格式如下图:
应用:
SELECT DATE_FORMAT(‘2020-10-06’,GET_FORMAT(DATE,‘USA’));
24.HOUR()
语法:HOUR(dateTime)
描述:返回传入参数的小时
应用:
SELECT HOUR(‘2020-10-06 09:30:00’)
25.LAST_DAY()
语法:LAST_DAY(dateTime)
描述:返回传入参数最后一天的日期
应用:
SELECT LAST_DAY(‘2020-10-06’)
SELECT LAST_DAY(‘2020-10-06 09:30:00’)
26.MAKEDATE()
语法:MAKEDATE(year,day)
描述:传入年份和天数。返回对于的日期
应用:
SELECT MAKEDATE(2019,366)
27.MAKETIME()
语法:MAKETIME(hour,minute,second)
描述:传入小时、分钟和秒。返回相应的时间
应用:
SELECT MAKETIME(08,30,12)
28.MICROSECOND()
语法:MICROSECOND(dateTime)
描述:传入时间或者日期时间返回微妙
应用:
SELECT MICROSECOND(‘2020-10-06 09:30:00.123456’)
29.MINUTE()
语法:MINUTE(dateTime)
描述:传入时间或者日期时间返回分钟
应用:
SELECT MINUTE(‘08:30:00’)
30.MONTH()
语法:MONTH(datetime)
描述:传入日期或者日期时间返回月份
应用:
SELECT MONTH(‘2020-10-06 01:00:00’)
31.MONTHNAME()
语法:MONTHNAME(dateTime)
描述:传入日期或者日期时间返回月份的英文名
应用:
SELECT MONTHNAME(‘2020-10-06 01:00:00’)
32.PERIOD_ADD()
语法:PERIOD_ADD(p,n)
描述:给传入日期§加上指定的月数(n)。日期格式为YYMM或者YYYYMM
应用:
SELECT PERIOD_ADD(202001,12)
33.PERIOD_DIFF()
语法:PERIOD_DIFF(p1,p2)
描述:返回两个参数之前相差的月数,p1-p2。p1和p2的格式为YYMM或者YYYYMM
应用:
SELECT PERIOD_DIFF(202005,202001)
34.QUARTER()
语法:QUARTER(datetime)
描述:返回日期对应的季度
应用:
SELECT QUARTER(‘2020-10-04’)
35.SECOND()
语法:SECOND(time)
描述:返回时间的秒
应用:
SELECT SECOND(‘02:30:28’)
36.SEC_TO_TIME()
语法:SEC_TO_TIME(seconds)
描述:将传入的秒,转化为时间
应用:
SELECT SEC_TO_TIME(10)
37.STR_TO_DATE()
语法:STR_TO_DATE(str,format)
描述:将传入的字符串转化为指定格式的日期、时间或者日期时间
应用:
SELECT STR_TO_DATE(‘06,10,2020’,’%d,%m,%Y’);
38.SUBDATE()
语法:SUBDATE(date,INTERVAL expr unit);SUBDATE(expr,days)
描述:传入时间向前推移表达式指定的时间间隔。有两种用法,第一种是手动输入时间间隔表达式,第二种只需要填入数值,但它指定的时间间隔为天。
应用:
SELECT SUBDATE(‘2020-10-06’,INTERVAL 1 DAY)
SELECT SUBDATE(‘2020-10-06’,1)
39.SUBTIME()
语法:SUBTIME(expr1,expr2)
描述:将传入时间向前推移传入的时间表达式。expr1指的是传入时间,expr2指的是时间表达式。需要注意的是,SUBTIME()不支持时间间隔表达式。
应用:
SELECT SUBTIME(‘2020-10-06 00:00:00’,‘1 01:30:00’)
SELECT SUBTIME(‘2020-10-06 00:00:00’,‘01:30:00’)
40.SYSDATE()
语法:SYSDATE()
描述:获取当前日期时间。与NOW()不同的是,NOW()在执行开始的时候就获取到值了,而SYSDATE()在执行时动态获取值
应用:
SELECT SYSDATE()
证明NOW()在执行开始时,就获取到了值
SELECT NOW(), SLEEP(3), NOW()
证明SYSDATE()在执行时动态获取值
SELECT NOW(), SLEEP(3), SYSDATE()
41.TIME()
语法:TIME(dateTime)
描述:截取日期时间的时间部分
应用:
SELECT TIME(‘2020-10-06 08:01:02’);
42.TIME_FORMAT()
语法:TIME_FORMAT(time,format)
描述:将时间转化为字符串格式。format字符串只能包含小时、分钟、秒和微妙的格式符
应用:
SELECT TIME_FORMAT(‘23:00:00’, ‘%H %i %S’);
43.TIME_TO_SEC()
语法:TIME_TO_SEC(time)
描述:将时间转化为秒
应用:
SELECT TIME_TO_SEC(‘10:30:11’)
44.TIMEDIFF()
语法:TIMEDIFF(expr1,expr2)
描述:比较两个参数,计算相差时间。expr1 - expr2
应用:
SELECT TIMEDIFF(‘2020-10-07 12:30:00’,‘2020-10-06 12:00:00’ )
45.TIMESTAMP()
语法:TIMESTAMP(expr),TIMESTAMP(expr1,expr2)
描述:expr只能指定为date或者datetime类型。一个参数时,结果作为datetime类型返回。两个参数时,expr1+expr2,将结果作为datetime类型返回。
应用:
SELECT TIMESTAMP(‘2020-10-06’)
SELECT TIMESTAMP(‘2020-10-06’,‘20:30:00’)
46.TIMESTAMPADD()
语法:TIMESTAMPADD(unit,interval,expr)
描述:expr只能指定为date或者datetime类型。Unit用来指定要添加的单位,将interval + expr的结果返回
应用:
SELECT TIMESTAMPADD(MINUTE,1,‘2020-10-06 20:30:00’);
47.TIMESTAMPDIFF()
语法:TIMESTAMPDIFF(unit,expr1,expr2)
描述:返回expr2-expr1之间的差值,用unit来指定单位。
应用:
SELECT TIMESTAMPDIFF(DAY,‘2020-10-06’,‘2020-10-07’)
48.TO_DAYS()
语法:TO_DAYS()
描述:返回从0年以来,到传入参数的天数
应用:
SELECT TO_DAYS(‘2020-10-06’)
49.TO_SECONDS()
语法: TO_SECONDS(expr)
描述:返回从0年以来,到传入参数的秒数
应用:
SELECT TO_SECONDS(‘2020-10-06 10:00:00’)
50.UNIX_TIMESTAMP()
语法:UNIX_TIMESTAMP(),UNIX_TIMESTAMP(datetime)
描述:不带参数时返回当前时间的时间戳;带参数时,返回传入参数的时间戳
应用:
SELECT UNIX_TIMESTAMP()
SELECT UNIX_TIMESTAMP(‘2020-10-06 10:00:00’)
51.WEEKDAY()
语法:WEEKDAY(datetime)
描述:返回传入参数中是本周的第几天。0为周一,6为周日
应用:
SELECT WEEKDAY(‘2020-10-06 00:00:00’)
52.WEEKOFYEAR()
语法:WEEKOFYEAR(datetime)
描述:返回传入参数的周数
应用:
SELECT WEEKOFYEAR(‘2020-12-31’)
53.YEAR()
语法:YEAR(datetime)
描述:提取日期的年份。
应用:
SELECT YEAR(‘2020-09-08’)
54.YEARWEEK()
语法:YEARWEEK(datetime)
描述:返回传入参数的年份和周数。当传入天数是一年中第一天的时候,返回的是去年年份周数
应用:
SELECT YEARWEEK(‘2020-01-06’)
SELECT YEARWEEK(‘2020-01-01’)
注意事项:
文中传入的date指的是日期,time指的是时间,datetime指的是日期或者日期时间。expr指的是时间格式(例如:YEARS-MONTHS),unit是时间的单位(例如:YEAR_MONTH)日期函数玩法多种多样。所以需要我们不断的测试,不断的去思考发现新的玩法。