MYSQL时间函数

一、格式转换

（1）日期转换成时间戳

SELECT UNIX_TIMESTAMP('2019-03-26');   --1553529600

（2）时间戳转换成日期

select from_unixtime(1553529600,'%Y年%m月%d日');    --2019年03月26日

（3）时间戳转字符串

select from_unixtime(1451997924,'%Y-%d');

（4）时间转字符串

DATE_FORMAT(date,format) ，根据format字符串格式化date值

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

还有TIME_FORMAT(time,format)，只处理小时、分钟和秒(其余符号产生一个NULL值或0)

在format字符串中可用标志符:

  %M 月名字(January……December) 
　%W 星期名字(Sunday……Saturday) 
　%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
　%Y 年, 数字, 4 位 
　%y 年, 数字, 2 位 
　%a 缩写的星期名字(Sun……Sat) 
　%d 月份中的天数, 数字(00……31) 
　%e 月份中的天数, 数字(0……31) 
　%m 月, 数字(01……12) 
　%c 月, 数字(1……12) 
　%b 缩写的月份名字(Jan……Dec) 
　%j 一年中的天数(001……366) 
　%H 小时(00……23) 
　%k 小时(0……23) 
　%h 小时(01……12) 
　%I 小时(01……12) 
　%l 小时(1……12) 
　%i 分钟, 数字(00……59) 
　%r 时间,12 小时(hh:mm:ss [AP]M) 
　%T 时间,24 小时(hh:mm:ss) 
　%S 秒(00……59) 
　%s 秒(00……59) 
　%p AM或PM 
　%w 一个星期中的天数(0=Sunday ……6=Saturday ） 
　%U 星期(0……52), 这里星期天是星期的第一天 
　%u 星期(0……52), 这里星期一是星期的第一天 
　%% 字符%

（5）字符串转时间

STR_TO_DATE(字符串，日志格式)

SELECT STR_TO_DATE('2019-01-20 16:01:45', '%Y-%m-%d %H:%i:%s');

（6）时间、秒转换

select time_to_sec('01:00:05');   --3605
select sec_to_time(3605); -- '01:00:05'

（7）拼凑时间

makdedate(year,dayofyear),

maketime(hour,minute,second)

select makedate(2020,03); -- '2020-01-03'
select makedate(2020,32); -- '2020-02-01'
select maketime(12,15,30); -- '12:15:30'

（8）时区转换函数

select convert_tz('2020-03-26 12:00:00', '+08:00', '+00:00');   --2020-03-26 04:00:00

还可通过如下实现

select date_add('2020-03-26 12:00:00', interval -8 hour); -- 2020-03-26 04:00:00
select date_sub('2020-03-26 12:00:00', interval 8 hour); -- 2020-03-26 04:00:00
select timestampadd(hour, -8, '2020-03-26 12:00:00');   -- 2020-03-26 04:00:00

二、时间比较

（一）to_days（）

返回从0000年至当前日期的总天数

select to_days(now());   --737875

例1：查昨天的数据

SELECT * FROM 表名 WHERE TO_DAYS(NOW()) – TO_DAYS(时间字段名) <= 1

其中now()，以’YYYY-MM-DD HH:MM:SS’格式返回当前时间

例2：查7天内的数据

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

其中DATE_SUB()，时间相减函数

DATE_ADD()，时间相加

CURDATE() ，以’YYYY-MM-DD’或‘YYYYMMDD‘’格式返回当前日期值(根据返回值所处上下文是字符串或数字)

CURTIME() ，以’HH:MM:SS’或HHMMSS格式返回当前时间值

例3：查本月的数据

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

例4：查上个月的数据

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m' ) , date_format( 时间字段名, ‘%Y%m' ) ) =1

其中，PERIOD_DIFF(P1,P2) ，返回在时期P1和P2之间月数(P1和P2的格式YYMM或YYYYMM)

例5：查本季度

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

更多实例

（二）DATEDIFF( )

DATEDIFF(startdate,enddate)返回两个日期之间的天数

SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate  --1

SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate -- -1

三、返回时间或部分

（一）返回当前时间

now（） 返回当前时间

select now(), sleep(3), now(); 

sysdate() 动态获取系统时间

select sysdate(), sleep(3), sysdate();

（二）EXTRACT

获取返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

语法：EXTRACT(unit FROM date)

date 参数是合法的日期表达式。

unit 参数可以取：

 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(YEAR_MONTH FROM '2017-01-02 23:11:45');  --201701