test:表名
CreateDateTime:时间字段名
1. 获取当日的数据
select * from test where to_days(CreateDateTime)= to_days(now())
2. 获取昨日的数据
select * from test where to_days(now())-todays(CreateDateTime) <=1
3. 获取当月的数据
select * from test where date_format(CreateDateTime, ‘%Y%m’ ) = date_format( curdate() , ‘%Y%m’ )
4. 查询上一个月的数据
select * from test where period_diff(date_format(now(),’%Y%m’),date_format(CreateDateTime,’%Y%m’)) =1
5. 获取当季度的数据
select * from test where quarter(CreateDateTime)=quarter(now())
6. 获取上一个季度的数据
select * from test where quarter(CreateDateTime)=quarter(date_sub(now(),interval 1 quarter))
7. 查询当天某一时间段的数据
select * from test where UNIX_TIMESTAMP( CreateDateTime) BETWEEN UNIX_TIMESTAMP(CURDATE())+3600*10 AND UNIX_TIMESTAMP(CURDATE())+3600*20
mysql 查询当日,昨日,当月,当季度,上一季度和当天某一时间段的数据
最新推荐文章于 2024-07-17 01:10:25 发布