mysql 根据时间_mysql通过时间查询数据

1、查看年数据

SELECT * FROM milk_parent_reserve WHERE DATE_FORMAT( create_date, '%Y' ) = DATE_FORMAT( '2019-04-01', '%Y' )

2、查看月数据

SELECT * FROM milk_parent_reserve WHERE DATE_FORMAT( create_date, '%Y-%m' ) = DATE_FORMAT( '2019-04-01', '%Y-%m' )

3、查看日数据

SELECT * FROM milk_parent_reserve WHERE DATE_FORMAT( create_date, '%Y-%m-%d' ) = DATE_FORMAT( '2019-04-07', '%Y-%m-%d' )

4、查询当月数据

SELECT * FROM user_event WHERE DATE_FORMAT(create_time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')

5、获取本周数据

SELECT * FROM user_event WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW());

6、获取上周数据

SELECT * FROM user_event WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1;

7、查询当前6个月内的数据

SELECT * FROM user_event WHERE create_time BETWEEN DATE_SUB(NOW(),interval 6 month) and NOW()

8、查询上个月数据

SELECT * FROM user_event WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')