MySQL中按照时间条件进行查询

前言

在SQL查询中，经常会用到使用时间/日期作为查询条件，所以博主就列举了各种通过时间作为条件的例子

SQL模拟表

为方便下面SQL查询语句的验证，先提供一份模拟表

CREATE TABLE regedit (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  telephone varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '联系方式',
  create_time datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='模拟注册表'

INSERT INTO regedit (id, name, telephone, create_time) VALUES (1, '炎黄', '15011111111', '2023-07-11 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (2, '姬发', '15022222222', '2023-07-12 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (3, '嬴政', '15033333333', '2023-07-13 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (4, '刘邦', '15044444444', '2023-07-14 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (5, '刘秀', '15055555555', '2023-07-15 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (6, '司马炎', '15066666666', '2023-07-16 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (7, '司马睿', '15077777777', '2023-07-16 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (8, '杨坚', '15088888888', '2023-07-18 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (9, '李渊', '15099999999', '2023-07-18 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (10, '赵匡胤', '18911111111', '2023-07-20 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (11, '赵构', '18922222222', '2023-07-20 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (12, '铁木真', '18933333333', '2023-07-20 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (13, '朱元璋', '18944444444', '2023-07-23 09:00:00')
INSERT INTO regedit (id, name, telephone, create_time) VALUES (14, '努尔哈赤', '18955555555', '2023-07-23 09:00:00')

时间条件查询

查询数据库中存在的数据

【单位：分钟】

• 查询：10分钟前的数据

  SELECT * FROM regedit WHERE create_time BETWEEN date_add(now(), interval - 600 SECOND) AND NOW()

【单位：日】

• 查询：今天的数据

  SELECT * FROM regedit r WHERE DATE(r.create_time) = DATE(NOW())
  或
  SELECT * FROM regedit r WHERE TO_DAYS(r.create_time) = TO_DAYS(NOW())
  或
  SELECT * FROM regedit r WHERE DATEDIFF(r.create_time, NOW()) = 0

• 查询：未来第n天的数据

  SELECT * FROM regedit r WHERE DATEDIFF(r.create_time, NOW()) = n

• 查询：过去第n天的数据

  SELECT * FROM regedit r WHERE DATEDIFF(r.create_time, NOW()) = -n

• 查询：未来n天的所有数据

  SELECT * FROM regedit r WHERE DATEDIFF(r.create_time, NOW()) < n AND DATEDIFF(r.create_time, NOW()) >= 0

• 查询：过去n天的所有数据（包含当天）

  SELECT * FROM regedit r WHERE DATEDIFF(r.create_time, NOW()) <= 0 AND DATEDIFF(r.create_time, NOW()) > -n；

• 查询：过去n天的所有数据（不包含当天）

  SELECT * FROM regedit r WHERE DATEDIFF(r.create_time, NOW()) < 0 AND DATEDIFF(r.create_time, NOW()) > -n；

• 查询：大于前7天，小于当前时间的数据（前7天）

  SELECT * FROM regedit r WHERE DATE(r.create_time) BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
  或
  SELECT * FROM regedit r WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(r.create_time)；

• 查询：大于前7天的数据（近7天）

  SELECT * FROM regedit r WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(r.create_time)

• 查询：大于前30天，小于后30天的数据（近30天）

  SELECT * FROM regedit r WHERE DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= DATE(r.create_time)
  或
  SELECT * FROM regedit r WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(r.create_time)；

• 查询：明天的数据

  SELECT * FROM regedit r WHERE DATE(r.create_time) = DATE_ADD(CURDATE(), INTERVAL 1 DAY)
  或
  SELECT * FROM regedit r WHERE DATEDIFF(r.create_time, NOW()) = 0；

• 查询：昨天的数据

  SELECT * FROM regedit r WHERE DATE(r.create_time) = DATE_ADD(CURDATE(), INTERVAL -1 DAY)
  或
  SELECT * FROM regedit r WHERE DATEDIFF(r.create_time, NOW()) = -1；
  或
  SELECT * FROM regedit r WHERE TO_DAYS(NOW()) - TO_DAYS(r.create_time) <= 1；

• 查询：前天的数据

  SELECT * FROM regedit r WHERE DATE(r.create_time) = DATE_ADD(CURDATE(), INTERVAL -2 DAY)
  或
  SELECT * FROM regedit r WHERE DATEDIFF(r.create_time, NOW()) = -2

【单位：周】

• 查询：本周的数据

  SELECT * FROM regedit r WHERE WEEK(r.create_time) = WEEK(NOW())

【单位：月】

• 查询：本月的数据

  SELECT * FROM regedit r WHERE MONTH(r.create_time) = MONTH(NOW())
  或
  SELECT * FROM regedit r WHERE DATE_FORMAT(r.create_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')
  或
  SELECT * FROM regedit r WHERE DATE_FORMAT(r.create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')

• 查询：上一个月的数据

  SELECT * FROM regedit r WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(r.create_time, '%Y%m')) = 1

• 查询：距离当前现在6个月的数据

  SELECT * FROM regedit r WHERE r.create_time BETWEEN DATE_SUB(NOW(),interval 6 MONTH) AND NOW()

【单位：季度】

• 查询：本季度的数据

  SELECT * FROM regedit r WHERE QUARTER(r.create_time) = QUARTER(NOW())

• 查询：上季度的数据

  SELECT * FROM regedit r WHERE QUARTER(r.create_time) = QUARTER(DATE_SUB(NOW(), interval 1 QUARTER))

【单位：年】

• 查询：本年的数据

  SELECT * FROM regedit r WHERE YEAR(r.create_time) = YEAR(NOW())

• 查询：上一年的数据

  SELECT * FROM regedit r WHERE YEAR(r.create_time) = year(DATE_SUB(NOW(), interval 1 year))

查询存在并补齐数据

查询未来7天每天的统计数量，若没有数据则自动补 0

-- create_time：日期
-- week_num：星期几（周一：1、周二：2.....周日：7 ）
-- record_count：该日期的注册数量

SELECT 
  a.create_time,
  WEEKDAY(a.create_time)+1 AS week_num,
  IFNULL(b.count, 0) AS count
FROM (
  SELECT curdate() AS create_time
  UNION ALL
  SELECT date_sub(curdate(), interval -1 day) AS create_time
  UNION ALL
  SELECT date_sub(curdate(), interval -2 day) AS create_time
  UNION ALL
  SELECT date_sub(curdate(), interval -3 day) AS create_time
  UNION ALL
  SELECT date_sub(curdate(), interval -4 day) AS create_time
  UNION ALL
  SELECT date_sub(curdate(), interval -5 day) AS create_time
  UNION ALL
  SELECT date_sub(curdate(), interval -6 day) AS create_time
) a LEFT JOIN (
  SELECT
    DATE(create_time) AS createtime, COUNT(*) AS count 
  FROM regedit 
  -- WHERE id = 1
  GROUP BY DATE(create_time)
) b ON a.create_time = b.createtime
ORDER BY a.create_time ASC