6.4日期和时间函数

Super二楠

已于 2022-07-10 10:50:49 修改

阅读量72

点赞数

分类专栏： Mysql 文章标签：数据库 mysql

于 2022-07-10 10:46:36 首次发布

本文链接：https://blog.csdn.net/sinat_40225231/article/details/125703198

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

日期与时间函数

select CURRENT_DATE(),CURRENT_DATE,CURDATE();
select CURRENT_TIME(),CURRENT_TIME(),CURTIME();
select DAYNAME(UTC_DATE());

select DAYOFWEEK(NOW());
select WEEKDAY(NOW()+1);

select WEEK('2022-01-13'),WEEK('2022-01-13',0),WEEK('2022-01-13',1),WEEK('2022-01-13',2),WEEK('2022-01-13',3),WEEK('2022-01-13',4)
,WEEK('2022-01-13',5),WEEK('2022-01-13',6),WEEK('2022-01-13',7);

select WEEKOFYEAR('2018-02-20'),WEEKOFYEAR('2018-02-20',3);

select DAYOFYEAR('2020-07-09'),DAYOFMONTH('2022-07-09');

select YEAR(NOW()),QUARTER(NOW()) ,HOUR(now()),MINUTE(NOW()),SECOND(NOW());

select EXTRACT(YEAR from '2020-07-15'),EXTRACT(MONTH from '2020.05.15'),EXTRACT(DAY from '2020-06-18');

SELECT TIME('0515'),TIME_TO_SEC('0515'),TIME_TO_SEC('010515'),TIME_TO_SEC('05:15'),TIME_TO_SEC('00:05:15');

select SEC_TO_TIME(315);


-- 计算日期函数

select DATE_ADD('2022-07-15',INTERVAL 1 YEAR),DATE_ADD('2022.05.12',INTERVAL 1 MONTH),DATE_ADD('20220624',INTERVAL 2 DAY),DATE_ADD('2022-01-02',INTERVAL 1 QUARTER);
select DATE_ADD('2022-07-15',INTERVAL 1 HOUR),DATE_ADD('2022.05.12',INTERVAL 1 MINUTE),DATE_ADD('20220624',INTERVAL 2 SECOND);
select DATE_ADD('2022-07-15',INTERVAL '1-1' YEAR_MONTH),DATE_ADD('2022.05.12',INTERVAL '5.12000000' DAY_MICROSECOND),DATE_ADD('20220624',INTERVAL '05:12' HOUR_MINUTE);
select DATE_ADD('2022-07-15',INTERVAL '1:1:1' HOUR_SECOND),DATE_ADD('2022.05.12',INTERVAL '5:12' MINUTE_SECOND),DATE_ADD('20220624',INTERVAL '05.12' MINUTE_MICROSECOND);
select DATE_ADD('2022-07-15',INTERVAL '1 1:1' DAY_MINUTE),DATE_ADD('2022.05.12',INTERVAL '5 12' DAY_HOUR),DATE_ADD('20220624',INTERVAL '05.12' SECOND_MICROSECOND);

select DATE_ADD('2022-07-15',INTERVAL -1 YEAR),DATE_ADD('2022.05.12',INTERVAL -1 MONTH),DATE_ADD('20220624',INTERVAL -2 DAY),DATE_ADD('2022-01-02',INTERVAL -1 QUARTER);
select DATE_ADD('2022-07-15',INTERVAL -1 HOUR),DATE_ADD('2022.05.12',INTERVAL -1 MINUTE),DATE_ADD('20220624',INTERVAL -2 SECOND);
select DATE_ADD('2022-07-15',INTERVAL '-1--1' YEAR_MONTH),DATE_ADD('2022.05.12',INTERVAL '-5.-12000000' DAY_MICROSECOND),DATE_ADD('20220624',INTERVAL '-05:-12' HOUR_MINUTE);
select DATE_ADD('2022-07-15',INTERVAL '-1:-1:-1' HOUR_SECOND),DATE_ADD('2022.05.12',INTERVAL '-5:-12' MINUTE_SECOND),DATE_ADD('20220624',INTERVAL '-05.-12' MINUTE_MICROSECOND);


select DATE_SUB('2022-07-15',INTERVAL 1 YEAR),DATE_SUB('2022.05.12',INTERVAL 1 MONTH),DATE_SUB('20220624',INTERVAL 2 DAY),DATE_SUB('2022-01-02',INTERVAL 1 QUARTER);
select DATE_SUB('2022-07-15',INTERVAL 1 HOUR),DATE_SUB('2022.05.12',INTERVAL 1 MINUTE),DATE_SUB('20220624',INTERVAL 2 SECOND);
select DATE_SUB('2022-07-15',INTERVAL '1-1' YEAR_MONTH),DATE_SUB('2022.05.12',INTERVAL '5.12000000' DAY_MICROSECOND),DATE_SUB('20220624',INTERVAL '05:12' HOUR_MINUTE);
select DATE_SUB('2022-07-15',INTERVAL '1:1:1' HOUR_SECOND),DATE_SUB('2022.05.12',INTERVAL '5:12' MINUTE_SECOND),DATE_SUB('20220624',INTERVAL '05.12' MINUTE_MICROSECOND);

select DATEDIFF('20220705000000','2022-07-06 12:12:12'),DATEDIFF('20220705000000','20220706121212'),DATEDIFF('2022.07.05.000000','20220706121212')
,DATEDIFF('2022.07.05.000000','2022-07-05 23:59:59'),DATEDIFF('2022.07.05.000000','2022-07-06 23:59:59');

-- 日期格式化

select DATE_FORMAT(NOW(),'%m %s');