mysql 常见时间条件查询

来源：互联网发布：张无忌万安寺知乎编辑：程序博客网时间：2024/05/20 22:38

查询今天的信息记录：
select * from `article` where to_days(`add_time`) = to_days(now());

查询昨天的信息记录：
select * from `article` where to_days(now()) – to_days(`add_time`) <= 1;

查询近7天的信息记录：
select * from `article` where date_sub(curdate(), INTERVAL 7 DAY) <= date(`add_time`);

查询近30天的信息记录：
select * from `article` where date_sub(curdate(), INTERVAL 30 DAY) <= date(`add_time`);

查询本月的信息记录：
select * from `article` where date_format(`add_time`, ‘%Y%m') = date_format(curdate() , ‘%Y%m');

查询上一月的信息记录：

select * from `article` where period_diff(date_format(now() , ‘%Y%m') , date_format(`add_time`, ‘%Y%m')) =1;

查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

#查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

#查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

#查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year))

0 0