MYSQL 时间查询

最新推荐文章于 2024-07-24 16:28:55 发布

看，月亮在跳舞

最新推荐文章于 2024-07-24 16:28:55 发布

阅读量3.2k

点赞数 2

分类专栏： MYSQL 文章标签： mysql

本文链接：https://blog.csdn.net/kk_gods/article/details/109000548

版权

MYSQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

SELECT CURDATE() AS 当前日期;
SELECT NOW() AS 当前时间;

SELECT DATE_FORMAT(CURDATE(),'%w') AS 当前周几;
SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS 本周一;

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY) 本周第一天(周天);

SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') AS 本周末;

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY) 本周最后一天（周六）;

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY) 上周第一天(周天);

select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY) 上周最后一天（周六）;

SELECT DATE_FORMAT(CURDATE(),'%Y-%m-01 00:00:00') AS 本月第一天;

select date_add(curdate(), interval - day(curdate()) + 1 day) 本月第一天;
select last_day(curdate()) 本月最后一天;
select date_add(curdate()-day(curdate())+1,interval -1 month) 上月第一天;
select last_day(date_sub(now(),interval 1 month)) 上月最后一天;
select date_add(curdate()-day(curdate())+1,interval 1 month) 下月第一天;
select last_day(date_sub(now(),interval -1 month)) 下月最后一天;
select day(last_day(curdate())) 本月天数;

select date_sub(curdate(), interval 1 month) 上月今天的当前日期;
select unix_timestamp(date_sub(now(),interval 1 month)) 上月今天的当前时间（时间戳）;
select datediff(curdate(), date_sub(curdate(), interval 1 month)) 获取当前时间与上个月之间的天数;

SELECT * FROM report WHERE create_time BETWEEN DATE_FORMAT(CURDATE(),'%Y-%m-01 00:00:00') AND NOW(); -- 本月数据
SELECT * FROM report WHERE DATE_FORMAT(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m'); -- 本月数据

SELECT QUARTER(CURDATE()) AS 当前季度;
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),'%Y-%m-'),'01') AS 本季度第一天;
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month) AS 本季度最后一天;
select * from report where QUARTER(create_time)=QUARTER(now()); -- 本季度数据

select date_sub(curdate(), interval dayofyear(curdate())-1 day) 本年第一天;
SELECT curdate() - INTERVAL(dayofyear(curdate()) - 1) DAY 本年第一天;
select concat(year(curdate()),'-12-31') 本年最后一天;
select date_sub(curdate(), interval dayofyear(curdate()) day) 上年最后一天;
SELECT (curdate() - INTERVAL(dayofyear(curdate()) - 1) DAY) + INTERVAL 1 YEAR 下年第一天;