本文对MySql中常用的时间和日期函数进行整理,常用的函数主要包含三个部分:返回部分值、返回当前时间和日期以及对时间的计算。详情如下:
一
返回部分值
1
返回年、季度、月、日、时、分、秒、年月日、时分秒
SELECT create_time -- 时间 ,YEAR(create_time)-- 返回年 ,QUARTER(create_time)-- 返回季度 , MONTH(create_time)-- 返回月 , DAY(create_time)-- 返回日 , HOUR(create_time)-- 返回小时 , MINUTE(create_time)-- 返回分钟 , SECOND(create_time)-- 返回秒 , DATE(create_time)-- 返回年月日 , TIME(create_time)-- 返回时分秒FROM time_function;
2
跟周有关的函数
SELECT create_time ,week(create_time)-- 返回所在周数(本年遇到的第一个星期天作为第一周,前面的计算为第0周) ,week(create_time,1)-- 返回所在周数(第一周超过3天,那么计算为本年的第一周) ,weekofyear(create_time)-- 返回所在周数 ,yearweek(create_time)-- 返回年份和周数 ,weekday(create_time)-- 返回工作日索引值(0代表周一) ,dayofweek(create_time)-- 返回工作日索引值(1代表周日)FROM time_function;
3
所属第几天
SELECT create_time ,dayofmonth(create_time)-- 返回月份的第几天 ,dayofyear(create_time)-- 返回年份的第几天FROM time_function;
4
返回英文名
SELECT create_time ,dayname(create_time)-- 返回工作日英文名 ,monthname(create_time)-- 返回月份英文名FROM time_function;
5
返回特定的格式
SELECT create_time ,date_format(create_time,'%Y-%m-%d %H:%i:%s')-- 返回年月日时分秒 ,date_format(create_time,'%w')-- 返回所在工作日索引值(0代表周日) ,time_format(create_time,'%H:%i')-- 返回时分FROM time_function;
二
当前时间和日期
SELECT current_date()-- 当前日期,current_time-- 当前时间,now()-- 当前时间和日期,localtime()-- 当前时间和日期
三
时间和日期的计算
1
时间和日期的加减
01
固定时间类型的加减
函数 | 说明 |
addtime()、subtime() | 时间的加减 |
adddate()、subdate() | 天数的加减 |
period_add() | 月份的加减 |
SELECT create_time,addtime(create_time,'1:00:00') as add1hour-- 增加一小时,subtime(create_time,'1:00:00') as reduce1hour-- 减少一小时,adddate(create_time,1) as add1day-- 增加一天,subdate(create_time,1) as reduce1day-- 减少一天,period_add(date_format(create_time,'%Y%m'),1) as add1month-- 增加一个月,period_add(date_format(create_time,'%Y%m'),-1) as reduce1month-- 减少一个月from time_function
02
自定义时间类型的加减
函数 | 说明 |
timestampadd(type,int_expr,datetime) | type:year,quarter,month,day,hour,minute,second int_expr:所要加减的数值 datetime:所要处理的时间 |
date_add(datetime,interval int_expr type) | |
date_sub(datetime,interval int_expr type) |
SELECT create_time ,TIMESTAMPADD(day,1,create_time) as add1day-- 增加一天 ,TIMESTAMPADD(day,-1,create_time) as reduce1day-- 减少一天 ,date_add(create_time,interval 1 hour) as add1hour_1-- 增加一小时 ,date_add(create_time,interval -1 hour) as reduce1hour_1-- 减少一小时 ,date_sub(create_time,interval -1 hour) as add1hour_2-- 增加一小时 ,date_sub(create_time,interval 1 hour) as reduce1hour_2-- 减少一小时FROM time_function;
2
返回两个时间格式之间的差值
01
返回固定类型的时间差
函数 | 说明 | 备注 |
timediff(datetime1,datetime2) | 返回两者相差的时间 | 其结果被限制在-838:59:59 到838:59:59 |
datediff(datetime1,datetime2) | 返回两者之间相差的天数 | 无 |
period_diff(P1,P2) | 返回两者之间相差的月份 | 格式均为YYYYMM或者YYMM |
SELECT create_time ,timediff('2019-03-01 16:00:00',create_time) as date_diff-- 返回两者之间相差的时间 ,now()-- 当前时间 ,datediff(now(),create_time) as date_diff-- 返回两者之间相差的天数 ,date_format(now(),'%Y%m')-- 当前月份 ,period_diff(date_format(now(),'%Y%m'),date_format(create_time,'%Y%m')) as month_diff-- 返回两者之间的月份差值FROM time_function;
02
返回自定义类型的时间差
TIMESTAMPDIFF(type,datetime1,datetime2)SELECT create_time ,now() ,timestampdiff(month,create_time,now()) as month_diff-- 返回两者之间的月份差值 ,timestampdiff(day,create_time,now()) as day_diff-- 返回两者之间的天数差值FROM time_function;
时间和日期函数的思维导图如下:
1
-END-