Mysql常用时间函数

225 阅读1分钟

当前时间

select now() -- 2021-03-27 20:34:13
select now()+0 -- 返回YYYYMMDDHHmmss 20210327203437

时间戳和timestamp切换

select unix_timestamp(now()); -- 当前秒级时间戳 1616848612
select from_unixtime(1525263383, '%Y-%m-%d %H:%i:%s'); -- 将时间戳转化为timestamp 2018-05-02 20:16:23
select from_unixtime(1525263383); -- 将时间戳转化为timestamp 2018-05-02 20:16:23
select unix_timestamp('2018-05-02 20:24:10'); -- 将timestamp转化为时间戳  

计算时间差 date_sub 和 date_add

select date_sub(now(),interval 1 day); -- 天
select date_sub(now(),interval 1 year); -- 获取一年前的今天的日期
select date_sub(now(),interval 1 week); -- 获取一周前的日期
select date_sub(now(),interval 1 month); -- 获取一个月前的日期

select date_add(now(),interval 1 day); 
select date_add(now(),interval 1 year);
select date_add(now(),interval 1 week);
select date_add(now(),interval 1 month);

提取时间中年月日时分秒

SELECT EXTRACT(YEAR FROM '2017-05-15 10:37:14.123456'); 2017 
SELECT EXTRACT(MONTH FROM '2017-05-15 10:37:14.123456');  5 
SELECT EXTRACT(DAY FROM '2017-05-15 10:37:14.123456'); 15 
SELECT EXTRACT(HOUR FROM '2017-05-15 10:37:14.123456'); 10
SELECT EXTRACT(MINUTE FROM '2017-05-15 10:37:14.123456');  37 
SELECT EXTRACT(SECOND FROM '2017-05-15 10:37:14.123456'); 14 
SELECT EXTRACT(MICROSECOND FROM '2017-05-15 10:37:14.123456'); 123456