learning_record_doc/数据库/mysql/根据时间查询SQL/MySQL 中按年、季度、月、周、天等查询.md

MySQL 中按年、季度、月、周、天进行查询

1、查询当天的数据

SELECT * FROM <table_name> WHERE TO_DAYS(datetime_col) = TO_DAYS(NOW());

2、查询本周数据 (上周日到本周六)

SELECT * FROM <table_name> WHERE YEARWEEK(DATE_FORMAT(<datetime_col>, '%Y-%m-%d' )) = YEARWEEK(NOW());

3、 查询近 7 天的数据, 不包括当天

SELECT * FROM <table_name> WHERE TO_DAYS(NOW()) - TO_DAYS(<datetime_col>) <= '7' AND TO_DAYS(NOW()) - TO_DAYS(<datetime_col>) > 0;

4、 查上周的数据

SELECT * FROM <table_name> WHERE YEARWEEK(date_format(<datetime_col>, '%Y-%m-%d')) = YEARWEEK(now())- 1;

-- 或

SELECT * FROM <table_name> WHERE date_format(<datetime_col>, '%Y-%m')= date_format(DATE_SUB(curdate(), INTERVAL 1 WEEK), '%Y-%m');

5、查询本月的数据

SELECT * FROM <table_name> WHERE DATE_FORMAT(<datetime_col>, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

6、查询上个月的数据

SELECT * FROM <table_name> WHERE date_format(<datetime_col>, '%Y-%m')= date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH), '%Y-%m');

7、查询近 30 天的数据

SELECT * FROM <table_name> WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(<datetime_col>);

8、查询某个季度（所有这个季度）的数据

SELECT * FROM <table_name> WHERE QUARTER(<datetime_col>) = QUARTER(NOW());

9、查询本年本季度的数据

SELECT * FROM <table_name> WHERE QUARTER(<datetime_col>) = QUARTER(NOW()) and YEAR(<datetime_col>) = YEAR(NOW());

10、查询上季度的数据

SELECT * FROM <table_name> WHERE QUARTER(<datetime_col>)+4*YEAR(<datetime_col>) = YEAR(NOW())+QUARTER(NOW())-1;

11、查询距现在 6 个月的数据

SELECT * FROM <table_name> WHERE <datetime_col> BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();

12、查询本年的数据

SELECT * FROM log WHERE YEAR(<datetime_col>) = YEAR(NOW());

13、查询上年的数据

SELECT * FROM log WHERE YEAR(<datetime_col>) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));