Practical MySQL & MariaDB

MySQL 日期算术：DATE_ADD、DATE_SUB 与 INTERVAL（实用示例）

函式
目次

1. 介绍

MySQL 是一种广泛用于 Web 应用和数据库管理的关系型数据库管理系统（RDBMS）。在其众多功能中，日期操作 是开发者每天都会使用的最重要的功能之一。例如，涉及日期的场景数不胜数，如设置循环提醒、提取特定时间范围内的数据以及计算日程安排等。

尤其是日期的加减运算使用频率极高。MySQL 提供了 DATE_ADD、DATE_SUB 等便利函数，以及用于指定时间段的 INTERVAL 子句。通过利用这些功能，你可以在保持代码简洁的同时，高效地完成复杂的日期计算。

本文将以初学者友好的方式讲解 MySQL 中的日期加减运算。我们将覆盖从基础用法到高级示例的全部内容，并提供可在实际项目中使用的实用技巧。同时，还会解释常见问题和需要注意的重要点，即使你对日期处理不太自信，也能轻松学习。

阅读完本文后，你将掌握 MySQL 日期操作所需的知识和实用技巧，并能够将其应用到自己的项目中。

2. MySQL 日期操作所需的基础知识

在 MySQL 中处理日期时，首先需要了解日期/时间数据类型及其基本概念。本节将说明进行 MySQL 日期操作前必须掌握的基础知识。

日期/时间数据类型

MySQL 提供了多种用于处理日期和时间的数据类型。了解每种类型的特性并作出恰当选择非常重要。

  1. DATE
  • 存储日历日期（年、月、日）。
  • 格式：YYYY-MM-DD
  • 示例：2025-01-01
  • 当只需要日期时使用（例如生日、创建日期）。
  1. DATETIME
  • 存储日期和时间。
  • 格式：YYYY-MM-DD HH:MM:SS
  • 示例：2025-01-01 12:30:45
  • 当需要同时记录日期和时间时使用（例如事件时间戳）。
  1. TIMESTAMP
  • 基于 UTC（协调世界时）存储日期/时间，并支持时区转换。
  • 格式：YYYY-MM-DD HH:MM:SS
  • 示例：2025-01-01 12:30:45
  • 需要时区感知数据或用于系统日志时使用。
  1. TIME
  • 存储时间值。
  • 格式：HH:MM:SS
  • 示例：12:30:45
  • 当只需要纯时间值时使用（例如营业时间）。
  1. YEAR
  • 仅存储年份。
  • 格式：YYYY
  • 示例：2025
  • 当只管理年级别数据时使用。

使用日期/时间类型时的重要注意事项

  • 时区设置 MySQL 默认使用服务器的时区设置。但如果你的应用使用不同的时区，可能会导致数据不一致。必要时请显式设置时区。
  • 处理无效日期 默认情况下，MySQL 在指定无效日期（例如 2025-02-30）时会抛出错误。但根据服务器配置，它可能会被转换为 NULL 或默认值，因此务必检查并确认你的设置。

日期操作的常见使用场景

  1. 提取特定时间段的数据
  • 示例：检索过去一周的销售数据。
  1. 日程计算
  • 示例：在用户注册后 30 天发送通知。
  1. 管理到期日期
  • 示例：管理优惠券的到期时间。

基础小结

MySQL 为处理日期和时间提供了强大的工具。通过了解日期/时间数据类型并合理选择，你可以提升数据库设计和查询效率。接下来，我们将深入探讨 DATE_ADD 函数，作为进行日期操作的具体方法。

3. DATE_ADD 函数：基础与实用用法

The MySQL DATE_ADD function 是一个方便的工具，用于向日期添加指定的间隔。本节将从基础用法到实际示例进行全面讲解。

什么是 DATE_ADD？

DATE_ADD 将 INTERVAL 子句中指定的时长添加到给定的日期，并返回一个新日期。它是简化日期计算的基础工具。

基本语法：

DATE_ADD(date, INTERVAL value unit)
  • date : 要操作的日期或日期时间。
  • value : 要添加的数值。
  • unit : 间隔单位（例如 DAY、MONTH、YEAR、HOUR、MINUTE、SECOND）。

可用的 INTERVAL 单位

您可以在 DATE_ADD 中使用以下 INTERVAL 单位。

UnitDescription
SECONDSeconds
MINUTEMinutes
HOURHours
DAYDays
WEEKWeeks
MONTHMonths
YEARYears

基本示例

以下是 DATE_ADD 的具体示例。

  1. 计算 1 天后 :
    SELECT DATE_ADD('2025-01-01', INTERVAL 1 DAY);

结果: 2025-01-02

  1. 计算 1 个月后 :
    SELECT DATE_ADD('2025-01-01', INTERVAL 1 MONTH);

结果: 2025-02-01

  1. 计算 1 年后 :
    SELECT DATE_ADD('2025-01-01', INTERVAL 1 YEAR);

结果: 2026-01-01

  1. 计算 3 小时后 :
    SELECT DATE_ADD('2025-01-01 12:00:00', INTERVAL 3 HOUR);

结果: 2025-01-01 15:00:00

  1. 添加多个单位（使用嵌套） :
    SELECT DATE_ADD(DATE_ADD('2025-01-01', INTERVAL 1 DAY), INTERVAL 3 HOUR);

结果: 2025-01-02 03:00:00

动态日期计算

您也可以将 DATE_ADD 应用于动态计算的日期。

  1. 计算今天起 7 天后的日期 :
    SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY);

结果: 今天起 7 天后的日期。

  1. 计算现在起 30 分钟后的时间 :
    SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);

结果: 现在起 30 分钟后的日期时间。

实际示例

  1. 计算保修到期日：从购买日期起计算 30 天的保修期。
    SELECT DATE_ADD(purchase_date, INTERVAL 30 DAY) AS expiry_date
FROM orders;
  1. 发送预订提醒：在活动日期前 3 天计算并发送提醒。
    SELECT DATE_ADD(event_date, INTERVAL -3 DAY) AS reminder_date
FROM events;
  1. 计算日志保留截止时间：计算今天起前 90 天的日期，并删除早于该日期的日志。
    DELETE FROM logs
WHERE log_date < DATE_ADD(CURDATE(), INTERVAL -90 DAY);

注意事项

  • 处理无效日期：如果 DATE_ADD 的结果成为无效日期，MySQL 可能返回 NULL。请确保输入数据格式有效。
  • 时区影响：如果使用时区，像 CURRENT_TIMESTAMP 和 NOW() 之类的函数可能会受到影响。

DATE_ADD 是 MySQL 日期操作中最基础且强大的工具之一。掌握它后，您将能够高效地处理大量日期计算。接下来，我们将在下一节详细介绍 DATE_SUB 函数。

4. DATE_SUB Function: Basics and Practical Usage

DATE_SUB 是 MySQL 中与 DATE_ADD 对应的函数。它用于从日期中减去指定的间隔。本节将解释其基础知识并提供可在实际工作中使用的示例。

什么是 DATE_SUB？

DATE_SUB 将 INTERVAL 子句中指定的时长从给定的日期或日期时间中减去，并返回一个新日期。它是进行日期减法的便利工具。

基本语法：

DATE_SUB(date, INTERVAL value unit)
  • date : 要操作的日期或日期时间。
  • value : 要减去的数值。
  • unit : 间隔单位（例如 DAY、MONTH、YEAR、HOUR、MINUTE、SECOND）。

基本示例

以下是 DATE_SUB 的具体示例。

  1. 计算 1 天前 :
    SELECT DATE_SUB('2025-01-01', INTERVAL 1 DAY);

结果: 2024-12-31

  1. 计算 1 个月前 :
    SELECT DATE_SUB('2025-01-01', INTERVAL 1 MONTH);

结果: 2024-12-01

  1. 计算 1 年前 :
    SELECT DATE_SUB('2025-01-01', INTERVAL 1 YEAR);

结果: 2024-01-01

  1. 提前 3 小时 :
    SELECT DATE_SUB('2025-01-01 12:00:00', INTERVAL 3 HOUR);

结果: 2025-01-01 09:00:00

  1. 减去多个单位（使用嵌套） :
    SELECT DATE_SUB(DATE_SUB('2025-01-01', INTERVAL 1 DAY), INTERVAL 3 HOUR);

结果: 2024-12-31 21:00:00

动态日期计算

您也可以基于动态日期使用 DATE_SUB。

  1. 计算今天之前的 7 天 :
    SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY);

结果: 今天之前的 7 天的日期。

  1. 计算现在之前的 30 分钟 :
    SELECT DATE_SUB(NOW(), INTERVAL 30 MINUTE);

结果: 现在之前的 30 分钟的日期时间。

实际示例

  1. 检索过去 30 天的数据 :
    SELECT * 
FROM orders
WHERE order_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY);
  1. 删除超过 90 天的旧数据 :
    DELETE FROM logs
WHERE log_date < DATE_SUB(CURDATE(), INTERVAL 90 DAY);
  1. 提醒通知 : 在事件开始前 1 天设置提醒。
    SELECT event_name, DATE_SUB(event_date, INTERVAL 1 DAY) AS reminder_date
FROM events;
  1. 班次计算 : 计算班次开始时间前 3 小时。
    SELECT DATE_SUB(shift_start, INTERVAL 3 HOUR) AS preparation_time
FROM work_shifts;

注意事项

  • 处理无效日期 : 如果减法结果变为无效日期，MySQL 可能返回 NULL。验证原始日期很重要。
  • 时区影响 : 如果服务器时区设置不同，DATE_SUB 的结果可能会意外偏移。需要时使用 CONVERT_TZ()。

DATE_SUB 与 DATE_ADD 同样重要，用于 MySQL 日期操作。通过保持减法逻辑简洁，可提升数据管理和分析的效率。在下一节中，我们将解释 5. INTERVAL 子句细节及其用法。

5. INTERVAL 子句细节及其用法

INTERVAL 子句用于 MySQL 日期/时间操作，通常与 DATE_ADD 和 DATE_SUB 一起使用，以添加或减去时间段。本节将详细解释 INTERVAL 子句，从基础到高级用法。

INTERVAL 子句基础

INTERVAL 子句指定要向目标日期添加或减去的时间量。它常与 DATE_ADD 和 DATE_SUB 一起使用，使得日期操作简洁而强大。

基本语法：

SELECT date + INTERVAL value unit;
SELECT date - INTERVAL value unit;
  • date : 要操作的日期或日期时间。
  • value : 要添加或减去的数值。
  • unit : 操作的单位（例如 DAY、MONTH、YEAR、HOUR、MINUTE、SECOND）。

可用单位

以下单位可在 INTERVAL 子句中使用。

UnitDescription
SECONDSeconds
MINUTEMinutes
HOURHours
DAYDays
WEEKWeeks
MONTHMonths
QUARTERQuarters
YEARYears
SECOND_MICROSECONDSeconds and microseconds
MINUTE_MICROSECONDMinutes and microseconds
MINUTE_SECONDMinutes and seconds
HOUR_MICROSECONDHours and microseconds
HOUR_SECONDHours and seconds
HOUR_MINUTEHours and minutes
DAY_MICROSECONDDays and microseconds
DAY_SECONDDays and seconds
DAY_MINUTEDays and minutes
DAY_HOURDays and hours
YEAR_MONTHYears and months

基本示例

  1. 向日期添加 1 天 :
    SELECT '2025-01-01' + INTERVAL 1 DAY;

结果: 2025-01-02

  1. 从日期时间减去 3 小时 :
    SELECT '2025-01-01 12:00:00' - INTERVAL 3 HOUR;

结果: 2025-01-01 09:00:00

  1. 从当前日期时间减去 10 分钟 :
    SELECT NOW() - INTERVAL 10 MINUTE;
  1. 从月末计算下个月的第一天 :
    SELECT LAST_DAY('2025-01-01') + INTERVAL 1 DAY;

结果: 下个月的第一天。

高级示例

  1. 日期范围计算 计算过去 30 天的范围。
    SELECT *
FROM orders
WHERE order_date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE();
  1. 计算下个季度的开始日期
    SELECT '2025-01-01' + INTERVAL 1 QUARTER;

结果: 下个季度的开始日期。

  1. 时区感知时间计算 获取在服务器默认时区下计算的后一小时的日期时间。
    SELECT CONVERT_TZ(NOW(), 'SYSTEM', '+09:00') + INTERVAL 1 HOUR;
  1. 执行更复杂的计算 计算从今天起两周后的日期，然后获取该月份的最后一天。
    SELECT LAST_DAY(CURDATE() + INTERVAL 14 DAY);

注意事项

  1. 注意结果类型 INTERVAL 计算的结果会保留原始类型（DATE 或 DATETIME）。请正确处理数据类型。
  2. 月末行为 在月末进行加减时，MySQL 可能会调整到下个月的末尾或开始。例如，将 1 个月加到 2025-01-31 会得到 2025-02-28（非闰年）。
  3. 时区影响 如果服务器和客户端配置了不同的时区，日期时间计算可能会出现意外的偏移。

INTERVAL 子句是简化 MySQL 日期操作的关键工具。凭借其灵活性，您可以轻松处理复杂的日期计算。在下一节中，我们将解释 6. 实用示例。

6. 实用示例

使用 MySQL 的 DATE_ADD 函数和 INTERVAL 子句可以简化复杂的日期计算和动态处理。本节介绍了在实际工作中有用的多个实用示例。

1. 在特定期间内提取数据

在数据分析和报告中，常常需要提取特定时间范围内的记录。

示例 1：检索过去 30 天的销售数据

SELECT *
FROM sales
WHERE sale_date >= CURDATE() - INTERVAL 30 DAY;

说明：提取今天起前 30 天（含）之后的所有数据。

示例 2：检索上个月的数据

SELECT *
FROM orders
WHERE order_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE();

说明：获取上个月的订单数据。

2. 设置事件提醒

您可以根据事件的开始日期或截止日期设置提醒通知。

示例：在事件开始前 3 天发送通知

SELECT event_name, DATE_SUB(event_date, INTERVAL 3 DAY) AS reminder_date
FROM events;

说明：计算事件日期前 3 天的日期，并将其设为通知日期。

3. 管理到期日期

基于注册日期计算到期日期在许多应用中都有使用。

示例 1：计算注册后 30 天的到期日期

SELECT user_id, registration_date, DATE_ADD(registration_date, INTERVAL 30 DAY) AS expiry_date
FROM users;

说明：计算注册日期后 30 天的到期日期。

示例 2：删除已过期的数据

DELETE FROM sessions
WHERE expiry_date < NOW();

说明：根据当前日期时间删除已过期的会话数据。

4. 班次与排班计算

根据工作排班的开始或结束时间进行时间调整的示例。

示例：计算班次开始前 1 小时的时间

SELECT shift_id, shift_start, DATE_SUB(shift_start, INTERVAL 1 HOUR) AS preparation_time
FROM shifts;

说明：计算班次开始前 1 小时的时间，并将其显示为准备时间。

5. 组合多个 INTERVAL 的计算

在计算中组合多个时间段的示例。

示例 1：获取从今天起一个月后的月份的最后一天

SELECT LAST_DAY(CURDATE() + INTERVAL 1 MONTH);

说明：计算今天起一个月后所在月份的最后一天。

示例 2：在注册后 1 年的基础上再加 3 个月的宽限期

SELECT user_id, DATE_ADD(DATE_ADD(registration_date, INTERVAL 1 YEAR), INTERVAL 3 MONTH) AS final_deadline
FROM users;

说明：在注册日期上先加 1 年，再加 3 个月，计算出最终截止日期。

6. 数据清理与优化

通过删除旧数据来优化数据库的示例。

示例：删除过去 90 天未登录的用户

DELETE FROM user_activity
WHERE last_login < CURDATE() - INTERVAL 90 DAY;

解释：删除最近登录时间早于今天起90天以上的用户。

注释

  • 验证计算结果：特别注意月末和闰年等边界情况。
  • 确保数据类型一致性：指定正确的日期/时间类型（DATE、DATETIME 等）有助于防止错误。
  • 考虑时区：在跨时区操作时，使用 CONVERT_TZ() 函数。

通过使用 DATE_ADD 和 INTERVAL 子句，您可以在实际工作中高效地简化日期操作。接下来，我们将详细说明 7. 注释与最佳实践。

7. 注释与最佳实践

MySQL 中的日期操作非常有用，但在实际工作中使用时，需要了解若干关键点和最佳实践。本节将说明需要注意的事项以及如何高效使用日期操作。

注释

1. 数据类型一致性

  • 问题：在 MySQL 中，日期/时间操作使用的类型（DATE、DATETIME、TIMESTAMP 等）不同，结果可能会有所差异。
  • 示例：
    SELECT DATE_ADD('2025-01-01', INTERVAL 1 DAY); -- Valid operation
SELECT DATE_ADD('Invalid Date', INTERVAL 1 DAY); -- Returns NULL for an invalid date
  • 对策：事先验证输入数据的格式是否正确，并选择合适的数据类型。

2. 处理无效日期

  • 问题：涉及月末或闰年的计算可能产生棘手的边界情况。
  • 示例：
    SELECT DATE_ADD('2025-01-31', INTERVAL 1 MONTH); -- Result: 2025-02-28

在这种情况下，MySQL 会自动调整，但结果可能与您的预期不同。

  • 对策：处理月末日期时，使用 LAST_DAY() 函数进行验证。
    SELECT LAST_DAY('2025-01-31') + INTERVAL 1 MONTH;

3. 时区考虑

  • 问题：如果服务器时区与应用时区不同，可能导致数据不一致或意外结果。
  • 示例：
    SELECT NOW(); -- Depends on the server timezone
  • 对策：显式设置时区。
    SELECT CONVERT_TZ(NOW(), 'SYSTEM', '+09:00'); -- Convert to Japan time

4. 复杂计算的性能

  • 问题：包含复杂日期计算的查询可能影响执行速度。
  • 对策：减少不必要的计算，并使用索引优化查询。

最佳实践

1. 使用标准格式

  • 保持日期格式的一致性。
  • 推荐格式：YYYY-MM-DD（DATE），YYYY-MM-DD HH:MM:SS（DATETIME）。
  • 示例：
    SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

2. 有效组合函数

  • 将 DATE_ADD/DATE_SUB 与 LAST_DAY 或 CURDATE 组合使用，可实现更灵活的计算。
  • 示例：
    SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

3. 谨慎规划动态日期操作

  • 处理动态日期时，将查询拆分并逐步处理。
  • 示例：
    SET @next_month = DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
SELECT LAST_DAY(@next_month);

4. 安全管理时区

  • 保持服务器和客户端之间的时区一致。
  • 示例：
    SET time_zone = '+09:00';

5. 在测试环境中验证

  • 提前在测试环境中验证复杂的日期计算以及跨时区的计算。

避免日期操作中的常见陷阱

日期操作是一项重要技能，可能对数据库设计和查询准确性产生重大影响。通过了解常见陷阱并遵循最佳实践，您可以避免问题，实现高效的数据处理。

8. 常见问题

以下是关于 MySQL 日期操作的常见问题（FAQ）。这些答案聚焦于初学者到中级用户常常疑惑的常见点。

Q1：DATE_ADD 与使用 + 运算符和 INTERVAL 的区别是什么？

A1：

  • DATE_ADD() 是专门用于日期计算的函数，能够更好地处理错误并进行类型转换。
  • + INTERVAL 更简洁，但在某些情况下可能会出现类型转换错误。

示例：使用 DATE_ADD：

SELECT DATE_ADD('2025-01-01', INTERVAL 1 DAY);

示例：使用 + 运算符和 INTERVAL：

SELECT '2025-01-01' + INTERVAL 1 DAY;

一般来说，推荐使用 DATE_ADD()。

Q2：DATE_ADD 能否一次添加多个间隔？

A2：
不能。DATE_ADD() 每次只能指定一个间隔。不过，你可以嵌套多个 DATE_ADD() 调用来实现相同的效果。

示例：添加多个间隔：

SELECT DATE_ADD(DATE_ADD('2025-01-01', INTERVAL 1 DAY), INTERVAL 1 MONTH);

Q3：如何在 MySQL 中更改日期格式？

A3：
使用 DATE_FORMAT() 函数将日期/时间格式化为你想要的形式。

示例：格式转换：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_date;

结果：2025-01-01 12:30:45

Q4：如果对月末日期加 1 个月会怎样？

A4：
MySQL 会自动调整，结果为下个月的最后一天。

示例：

SELECT DATE_ADD('2025-01-31', INTERVAL 1 MONTH);

结果：2025-02-28

这种行为很方便，但请确认它是否符合你的业务逻辑。

Q5：如何进行时区感知的日期计算？

A5：
使用 MySQL 的 CONVERT_TZ() 函数来指定时区。

示例：从 UTC 转换为日本时间：

SELECT CONVERT_TZ('2025-01-01 12:00:00', 'UTC', '+09:00') AS japan_time;

Q6：在 DATE_ADD 中指定无效日期会怎样？

A6：
如果指定了无效日期（例如 2025-02-30），MySQL 会返回 NULL。

示例：

SELECT DATE_ADD('2025-02-30', INTERVAL 1 DAY);

结果：NULL

建议在执行之前先对输入进行验证。

Q7：如何基于过去或未来的日期设置条件？

A7：
使用 DATE_ADD 或 DATE_SUB 计算参考日期，然后在 WHERE 子句中使用该结果。

示例：检索过去 30 天的数据：

SELECT * 
FROM sales
WHERE sale_date >= CURDATE() - INTERVAL 30 DAY;

Q8：使用多个时区时的最佳实践是什么？

A8：

  • 将所有 datetime 数据存储为 UTC。
  • 在客户端需要时再转换为相应的时区。

示例：在 UTC 中保存数据：

SET time_zone = '+00:00';

Q9：可以在 MySQL 中分别处理日期和时间吗？

A9：
可以。使用 DATE() 或 TIME() 提取日期或时间部分。

示例 1：仅提取日期：

SELECT DATE(NOW());

示例 2：仅提取时间：

SELECT TIME(NOW());

Q10：可以使用 MySQL INTERVAL 子句不支持的单位吗？

A10：
MySQL 只支持固定的一组 INTERVAL 单位。如果需要其他单位，请先将其转换为相应的支持单位。

9. Summary

在 MySQL 中进行日期操作是数据库设计和运维的关键技能。通过使用 DATE_ADD、DATE_SUB 和 INTERVAL 子句，你可以轻松且高效地完成复杂的日期计算。

Key Takeaways

  1. 日期/时间类型基础：
  • 在 MySQL 中，需要正确使用 DATE、DATETIME 和 TIMESTAMP 等日期/时间类型。
  1. DATE_ADD 与 DATE_SUB：
  • 便捷的函数，用于在日期上进行加减操作，语法简洁且灵活。
  1. INTERVAL 子句：
  • 使用年、月、日、小时等单位进行高效的日期计算，是必备工具。
  1. 实用示例：
  • 适用于提取过去/未来数据、管理过期时间、设置提醒等多种场景。
  1. 注意事项与最佳实践：
  • 统一使用 UTC 存储时间，避免时区混乱。
  • 在显示或计算时根据需要进行时区转换。

  • 对输入进行验证，防止无效日期导致的 NULL 结果。

  • 了解数据类型一致性、时区影响以及月末调整等考虑因素非常重要。

  1. FAQ :
  • 为初学者常见的问题和难点提供了具体的解决方案。

Next Steps

  • Apply what you learned :
  • 在自己的项目中尝试使用 DATE_ADD 和 DATE_SUB 来练习日期操作。
  • Design with timezones in mind :
  • 在跨多个时区运行的数据库中，准确的时区管理和日期计算至关重要。
  • Further optimization :
  • 设计高效的查询并使用索引，以性能为导向来提升数据操作。

掌握 MySQL 日期操作将大幅提升管理和分析数据的效率。请将本文作为参考，并在工作中积极运用这些实用技能。