MYSQL中常见的时间处理

use test;


select getdate()
select sysdate();
select now();
select  current_timestamp
select  current_timestamp()


select now(),sysdate(),sleep(1),now(),sysdate()



select date_format(now(),'%Y%m%d%H%i%s')
select time_format(now(),'%Y%m%d%H%i%s')


select str_to_date('01/21/2019', '%m/%d/%Y'); -- 2019-01-21
select str_to_date('01/21/19' , '%m/%d/%y'); -- 2019-01-21
select str_to_date('01.21.2019', '%m.%d.%Y'); -- 2019-01-21
select str_to_date('14:27:30', '%H:%i:%s'); -- 14:27:30
select str_to_date('02:27:30', '%h:%i:%s'); -- 02:27:30
select str_to_date('11:27:30', '%h:%i:%s'); -- 11:27:30
select str_to_date('01.21.2019 14:27:30', '%m.%d.%Y %H:%i:%s'); -- 2019-01-21 14:27:30


select time_to_sec('02:46:05'); -- 9965
select sec_to_time(9965); -- '02:46:05'



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

-- MySQL 为日期增加一个时间间隔：date_add()



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



select to_days('0000-00-00'); -- 0
select to_days('2019-01-21'); -- 737445
select from_days(737445); -- 2019-01-21
select from_days(0); -- 0000-00-00