Mysql 常用的时间查询（周，月，季，年）

来源：互联网发布：淘宝图片像素大小编辑：程序博客网时间：2024/05/23 15:06

一查询当日数据

1 select * from v2_goods_base_price as v2 where date(v2.created_at) = curdate();2 select * from v2_goods_base_price as v2where 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 v2where 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 v2where 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 v2where MONTH(v2.created_at)=MONTH(NOW()) and year(v2.created_at)=year(now());2 select * FROM v2_goods_base_price as v2where 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 v2where 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 v2where v2.created_at between date_sub(now(),interval N month) and now();

八查询当前季度数据

SELECT * FROM v2_goods_base_price as v2WHERE quarter( v2.created_at ) = quarter( curdate( ))

九查询上N个季度的数据

select * FROM v2_goods_base_price as v2where QUARTER(created_at)=QUARTER(DATE_SUB(now(),interval N QUARTER));

十查询当前年份数据

SELECT * FROM v2_goods_base_price as v2WHERE 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’；

0 0