如何在 MySQL 中进行基于 datetime 类型的时间范围查询并发送提前通知

在实际的业务开发中，我们经常需要根据某些时间点提前发送提醒通知，比如“提前一天”、“提前7天”、“提前两周”或者“提前一个月”。这些情况在数据库查询中尤其常见，尤其是当我们使用 datetime 类型字段时，如何准确地查询时间范围的数据是一项基础但关键的操作。本文将通过一个实际的场景，介绍如何使用 MySQL 的时间、日期函数来实现这种需求，并分享一些常用的日期函数的用法。

1. 问题背景

假设我们有一个 project 表，里面包含一个字段 trial_time，它的类型为 datetime，表示某个项目的到期时间。现在，我们需要提前向用户发送通知，分别通知他们项目在以下四个时间点即将到期：

• 提前一天（即明天到期）
• 提前7天
• 提前14天（两周）
• 提前30天（一个月）

我们需要通过 MySQL 查询出这些不同时间范围内的记录，并确保查询结果精确到 datetime 的时间点。

2. MySQL 的常用时间、日期函数

在实现这些需求之前，先来介绍一些常用的 MySQL 日期和时间处理函数，这些函数会帮助我们更好地处理日期范围：

• CURDATE(): 返回当前日期，格式为 YYYY-MM-DD 00:00:00，即当天的零点。
• NOW(): 返回当前的日期和时间，格式为 YYYY-MM-DD HH:MM:SS，包含时分秒。
• DATE_ADD(date, INTERVAL expr unit): 在指定的日期上添加时间间隔。例如，DATE_ADD(CURDATE(), INTERVAL 7 DAY) 返回7天后的日期。
• DATE_SUB(date, INTERVAL expr unit): 从指定的日期中减去时间间隔。与 DATE_ADD 类似。
• BETWEEN ... AND ...: 用于查询某个时间段之间的数据，包括边界值。通常用来简洁地表示时间范围查询。
• DATE_FORMAT(date, format): 以特定格式返回日期。例如：DATE_FORMAT(NOW(), '%Y-%m-%d') 只返回当前日期，忽略时分秒。

3. 具体实现方案

我们将分别用 BETWEEN 来查询那些符合“明天到期”、“提前7天”、“提前14天”、“提前30天”的数据。

1. 查询 明天到期 的数据

要查询明天即将到期的数据，我们可以利用 CURDATE() 获取当前的日期，然后通过 INTERVAL 加上 1 天来得到明天的 0 点到后天的 0 点。我们可以使用 BETWEEN 来限定查询范围：

SELECT * 
FROM project 
WHERE trial_time BETWEEN CURDATE() + INTERVAL 1 DAY 
                   AND CURDATE() + INTERVAL 2 DAY - INTERVAL 1 SECOND;

• 解析：这条 SQL 语句查询 trial_time 在明天 00:00:00 到后天 00:00:00 之前（即明天 23:59:59）的所有数据。通过 INTERVAL 1 SECOND 确保精确到秒。

2. 查询 提前7天到期 的数据

同理，要查询7天后到期的项目，时间范围是从当前日期的第7天到第8天的开始时间。

SELECT * 
FROM project 
WHERE trial_time BETWEEN CURDATE() + INTERVAL 7 DAY 
                   AND CURDATE() + INTERVAL 8 DAY - INTERVAL 1 SECOND;

• 解析：此语句会查找那些 trial_time 在7天后的这一天内到期的所有数据。

3. 查询 提前两周（14天）到期 的数据

如果需要提前两周通知用户项目即将到期，则可以将查询条件调整为当前日期起的第14天：

SELECT * 
FROM project 
WHERE trial_time BETWEEN CURDATE() + INTERVAL 14 DAY 
                   AND CURDATE() + INTERVAL 15 DAY - INTERVAL 1 SECOND;

• 解析：这条 SQL 语句会返回当前日期起第14天（两周）到期的数据。

4. 查询 提前一个月（30天）到期 的数据

最后，如果我们需要提前一个月通知用户项目即将到期，则可以使用类似的方法：

SELECT * 
FROM project 
WHERE trial_time BETWEEN CURDATE() + INTERVAL 30 DAY 
                   AND CURDATE() + INTERVAL 31 DAY - INTERVAL 1 SECOND;

• 解析：这条语句会查找那些在30天后到期的项目。

4. 常见问题

问题1：为什么要使用 INTERVAL 1 SECOND？

在 BETWEEN 中，时间的结束范围通常是开区间，因此我们需要通过减去一秒来确保查询精确到某天的结束时刻（即23:59:59），而不是跨入下一天的 00:00:00。

问题2：为什么使用 CURDATE() 而不是 NOW()？

CURDATE() 返回的是当天的零点（YYYY-MM-DD 00:00:00），适合日期范围的查询。而 NOW() 返回的是当前的日期和时间（包括时分秒），如果使用 NOW() 会导致查询不精确。

问题3：是否可以直接用 >= 和 < 而不是 BETWEEN？

当然可以。BETWEEN 是一种更加直观的表示方式，但是使用 >= 和 < 也完全可以实现同样的效果。比如，查询明天的数据可以写成：

SELECT * 
FROM project 
WHERE trial_time >= CURDATE() + INTERVAL 1 DAY 
  AND trial_time < CURDATE() + INTERVAL 2 DAY;

版权声明：本文为原创文章，版权归 全栈开发技术博客 所有。

本文链接：https://www.lvtao.net/database/mysql-date.html

转载时须注明出处及本声明