mysql-按时间查询

最新推荐文章于 2025-07-11 22:04:55 发布

原创最新推荐文章于 2025-07-11 22:04:55 发布 · 1.8k 阅读

4 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种使用SQL进行日期筛选的方法，包括今天、昨天、本周、上周、本月、上个月、近7天、近30天、本季度、上季度、本年及去年的数据查询技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以下使用的字符串time为自定义名字的字段
-- 今天
select*from time_test where to_days(time)=to_days(now());

-- 昨天
select*from time_test where datediff(time,now())=-1;

-- 本周
select * from time_test where yearweek(date_format(time,'%Y-%m-%d'))=yearweek(now());

-- 上周
select * from time_test where yearweek(date_format(time,'%Y-%m-%d'))=yearweek(now())-1;

-- 本月
select * from time_test where date_format(time,'%Y%m')=date_format(curtime(),'%Y%m') order by time;

-- 上个月
select * from time_test where
period_diff(
date_format(now(),'%Y%m') , date_format(time,'%Y%m')
)=1 order by time

-- 近7天
select * from time_test where date_sub(curdate(),interval 7 day) <=date(time) order by time

-- 近30天
select * from time_test where date_sub(curtime(),interval 30 day) <= date(time) order by time

-- 本季度
select * from time_test where quarter(time)=quarter(now()) order by time

-- 上季度
select * from time_test where quarter(time)=quarter(
date_sub( now(),interval 1 quarter )
)

-- 本年
select * from time_test where year(time)=year(now()) order by time;

-- 去年
select * from time_test where year(time)=year(date_sub(now(),interval 1 year)) order by time;