Mysql 常用时间函数

前一天

date_sub(curdate(),interval 1 day) 

明天(后一天)

date_sub(curdate(),interval -1 day) 

前一个月

date_sub(curdate(),interval 1 month) 

下个月

date_sub(curdate(),interval -1 month) 

去年

date_sub(curdate(),interval 1 year) 

明年

date_sub(curdate(),interval -1 year) 

2018-08-11 12:20:30

SELECT NOW()

2018-08-11

CURDATE()

12:20:30

CURTIME()

相反的还有 add函数：

set @dt = now();
 
select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

Unix 时间戳转换：

# 1533956241 (s)
SELECT UNIX_TIMESTAMP(); 
 
# 1533916800
SELECT UNIX_TIMESTAMP('2018-08-11'); 
 
# 1533961800
SELECT UNIX_TIMESTAMP('2018-08-11 12:30:00'); 
 
# 2018-08-11 10:57:21
SELECT FROM_UNIXTIME(1533956241);
 
# 2018-08-11 00:00:00
SELECT FROM_UNIXTIME(1533916800); 
 
# 2018-08-11 12:30:00
SELECT FROM_UNIXTIME(1533961800); 
 
# 2018 11th August 12:30:00 2018
SELECT FROM_UNIXTIME(1533961800, '%Y %D %M %h:%i:%s %x');

字符串转换为日期:

str_to_date(str, format)
 
select str_to_date('08/11/2018', '%m/%d/%Y'); -- 2018-08-11
 
select str_to_date('08/11/08' , '%m/%d/%y'); -- 2018-08-11
 
select str_to_date('08.11.2008', '%m.%d.%Y'); -- 2018-08-11
 
select str_to_date('08:00:30', '%h:%i:%s'); -- 08:00:30
 
select str_to_date('08.11.2018 08:00:30', '%m.%d.%Y %h:%i:%s');

时间函数加减：

datediff(date1,date2), timediff(time1,time2)
 
select datediff('2018-08-08', '2008-08-01'); 
# 返回 7 (天)
 
select datediff('2018-08-01', '2008-08-08');
# 返回 -7
  
select timediff('2018-08-08 08:08:08', '2018-08-08 00:00:00'); 
# 返回time差值，08:08:08
 
select timediff('08:08:08', '00:00:00'); 
# 返回 08:08:08