mysql 查询当日 昨日 当月 当季度 上一季度和当天某一时间段的数据

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