前一天
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函數(shù)：
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 時間戳轉(zhuǎn)換：
# 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');
字符串轉(zhuǎn)換為日期:
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');
時間函數(shù)加減：
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
