Mysql日期查询：今天、昨天、近7天、本周、上周、近30天、本月、上一月、近6月、本季度、上季度、本年、上一年、指定日期范围

数据库表结构:

建表及数据相关SQL语句：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for date_test
-- ----------------------------
DROP TABLE IF EXISTS `date_test`;
CREATE TABLE `date_test`  (
  `id` int NOT NULL,
  `check_date` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of date_test
-- ----------------------------
INSERT INTO `date_test` VALUES (1, '2023-03-26');
INSERT INTO `date_test` VALUES (2, '2023-03-25');
INSERT INTO `date_test` VALUES (3, '2023-03-27');
INSERT INTO `date_test` VALUES (4, '2023-03-30');
INSERT INTO `date_test` VALUES (5, '2023-02-28');
INSERT INTO `date_test` VALUES (6, '2023-07-07');
INSERT INTO `date_test` VALUES (7, '2023-03-13');
INSERT INTO `date_test` VALUES (8, '2022-12-09');
INSERT INTO `date_test` VALUES (9, '2023-12-09');

SET FOREIGN_KEY_CHECKS = 1;

查询相关SQL语句：

-- 查询当前时间
SELECT NOW() AS '当前时间';

-- to_days函数：返回从0000年（公元1年）至当前日期的总天数
SELECT TO_DAYS(NOW());

-- 查询今天
SELECT * FROM date_test AS t
WHERE TO_DAYS(t.check_date) = TO_DAYS(NOW());

-- 查询昨天
SELECT * FROM date_test AS t
WHERE TO_DAYS(NOW()) - TO_DAYS(t.check_date) = 1;


-- 查询今天的日期
SELECT CURDATE() AS '当前日期';

-- 查询近7天
SELECT * FROM date_test AS t
WHERE DATE(t.check_date) >= DATE_SUB(CURDATE(),INTERVAL 7 DAY);

-- yearweek函数：返回一个表示给定日期所在年份和第几周的数字。
-- YEARWEEK(date[,mode])函数默认西方周日当做一周的起始,需要改成国人的使用习惯，用周一作为一周起始
-- 这里设置YEARWEEK的第二个参数mode为1即可。（mode参数的更多取值请查阅相关资料）
SELECT YEARWEEK(NOW(),1);

-- 查询本周
SELECT * FROM date_test AS t
WHERE YEARWEEK(t.check_date, 1) = YEARWEEK(NOW(),1);

-- 查询上周
SELECT * FROM date_test AS t
WHERE YEARWEEK(NOW(),1) - YEARWEEK(t.check_date, 1) = 1;


-- 查询近30天
SELECT * FROM date_test AS t
WHERE DATE(t.check_date) >= DATE_SUB(CURDATE(),INTERVAL 30 DAY);

-- 查询本月
SELECT * FROM date_test AS t
WHERE DATE_FORMAT(t.check_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');

-- 查询上一月
-- PERIOD_DIFF()函数返回两个通过年月指定的时期之间相差的月数
SELECT * FROM date_test AS t
WHERE PERIOD_DIFF(DATE_FORMAT(CURDATE(),'%Y%m'), DATE_FORMAT(t.check_date,'%Y%m')) = 1;

-- 查询近6个月
SELECT * FROM date_test AS t
WHERE t.check_date BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW();

-- 查询本季度
-- QUARTER(date) 函数：返回年份日期的季度值，范围为 1 ~ 4
SELECT * FROM date_test AS t
WHERE QUARTER(t.check_date) = QUARTER(NOW());

-- 查询上季度
SELECT * FROM date_test AS t
WHERE  QUARTER(t.check_date) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER))
-- AND DATE_FORMAT(CURDATE(),'%Y%m') >= DATE_FORMAT(t.check_date,'%Y%m')
-- AND YEAR(NOW()) - YEAR(t.check_date) <= 1;

-- 查询本年
SELECT * FROM date_test AS t
WHERE YEAR(t.check_date) = YEAR(NOW());

-- 查询上一年
SELECT * FROM date_test AS t
WHERE YEAR(t.check_date) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

-- 查询指定日期范围
SELECT * FROM date_test AS t
WHERE t.check_date BETWEEN '2023-03-01' AND '2023-03-31';