一 查询当日数据
1 select * from v2_goods_base_price as v2 where date(v2.created_at) = curdate();
2 select * from v2_goods_base_price as v2
where year(v2.created_at)=year(now())
and month(v2.created_at)=month(now())
and day(v2.created_at)=day(now());
3 select* from v2_goods_base_price as v2 where TO_DAYS(v2.created_at) = TO_DAYS(NOW());
二 查询N天内的数据
SELECT * from v2_goods_base_price as v2
where v2.created_at > '****-**-**' and v2.created_at <= date_add('****-**-**', INTERVAL N day);
三 查询当前周数据
1 SELECT * FROM v2_goods_base_price as v2
where MONTH(v2.created_at)=MONTH(NOW()) and year(v2.created_at)=year(now());
2 select * FROM v2_goods_base_price as v2
where date_format(v2.created_at,'%Y-%m')=date_format(now(),'%Y-%m');
3 select * FROM v2_goods_base_price as v2
where MONTH(v2.created_at)=MONTH(NOW()) and year(v2.created_at)=year(now());
4 select * FROM v2_goods_base_price as v2
where month(v2.created_at) =
month(curdate())
四 查询上周的数据
1 select * FROM v2_goods_base_price as v2
where WEEKOFYEAR(v2.created_at)=WEEKOFYEAR(DATE_SUB(now(),INTERVAL 1 week));
2 select * FROM v2_goods_base_price as v2
where v2.created_at>=date_add(now(),interval -(8 + weekday(now())) day)
and v2.created_at<=date_add(now(),interval -(1 + weekday(now())) day);
五 查询当前月数据
1 select
* FROM v2_goods_base_price as v2
where MONTH(v2.created_at)=MONTH(NOW()) and year(v2.created_at)=year(now());
2 select * FROM v2_goods_base_price as v2
where date_format(v2.created_at,'%Y-%m')=date_format(now(),'%Y-%m');
3 select * FROM v2_goods_base_price as v2
where MONTH(v2.created_at)=MONTH(NOW()) and year(v2.created_at)=year(now());
4 select * FROM v2_goods_base_price as v2
where month(v2.created_at) =
month(curdate());
六 查询上月的数据
select
* FROM v2_goods_base_price as v2
where date_format(v2.created_at,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
七 查询近N个月数据
select * FROM v2_goods_base_price as v2
where v2.created_at between date_sub(now(),interval N month) and now();
八 查询当前季度数据
SELECT * FROM v2_goods_base_price as v2
WHERE quarter( v2.created_at ) = quarter( curdate( ))
九 查询上N个季度的数据
select * FROM v2_goods_base_price as v2
where QUARTER(created_at)=QUARTER(DATE_SUB(now(),interval N QUARTER));
十 查询当前年份数据
SELECT * FROM v2_goods_base_price as v2
WHERE year( v2.created_at ) = year( curdate( ))
十一 查询前N年的数据
select
* FROM v2_goods_base_price as v2
where year(v2.created_at)=year(date_sub(now(),interval N year));
十二 查询某月某年数据
Select * from 表名 where year(date) =‘2017’;
Select * from 表名 where month(date) =‘03’;
最后
以上就是光亮月饼最近收集整理的关于Mysql 常用的时间查询(周,月,季,年)的全部内容,更多相关Mysql内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复