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');

 

日期/时间转换为字符串：

date_format(date,format), time_format(time,format)

select date_format('2018-08-11 12:30:00', '%Y %m %d %H%i%s');
# 2018 08 11 123000 

字符串转换为日期:

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