MySQL函数：`ADDTIME()`与`SUBTIME()`对时间值进行加减操作。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 时间的加减运算：`ADDTIME()` 与 `SUBTIME()` 的深度剖析

大家好，今天我们来深入探讨 MySQL 中用于时间值加减运算的两个重要函数：ADDTIME() 和 SUBTIME()。这两个函数在处理时间相关的数据时非常有用，能够方便地进行时间段的增加或减少，适用于各种业务场景，例如计算事件的截止时间、调整任务的开始时间等。

`ADDTIME()` 函数：时间值的增加

ADDTIME() 函数用于将指定的时间或日期时间值加上一个时间间隔。其基本语法如下：

ADDTIME(expr, expr2)

其中：

expr：可以是时间或日期时间类型的表达式，表示要进行加法运算的时间值。
expr2：是一个时间表达式，表示要加上的时间间隔。

expr2 可以有多种格式，例如：

'HH:MM:SS'
'HH:MM:SS.fraction' (fraction 表示秒的小数部分)
'D HH:MM:SS'
'D HH:MM:SS.fraction'

D 表示天数，可以为负数。

示例 1：给当前时间加上 1 小时 30 分钟

SELECT ADDTIME(NOW(), '1:30:00');

这条 SQL 语句会返回当前时间加上 1 小时 30 分钟后的日期时间值。

示例 2：给指定时间加上 2 天

SELECT ADDTIME('2023-10-27 10:00:00', '2 0:0:0');

这条 SQL 语句会返回 2023-10-29 10:00:00。

示例 3：给时间值加上负的时间间隔

虽然 ADDTIME() 主要用于增加时间，但 expr2 也可以是负值，从而实现时间的减少。

SELECT ADDTIME('10:00:00', '-0:30:00');

这条 SQL 语句会返回 09:30:00。

示例 4：使用列值进行加减

假设我们有一个名为 events 的表，包含以下字段：

id (INT, PRIMARY KEY)
start_time (DATETIME)
duration (TIME)

我们可以使用 ADDTIME() 来计算每个事件的结束时间：

SELECT id, start_time, duration, ADDTIME(start_time, duration) AS end_time
FROM events;

这个查询会返回每个事件的 id、start_time、duration 和计算出的 end_time。

示例 5：秒的小数部分

SELECT ADDTIME('10:00:00', '0:0:0.5'); -- 加上 0.5 秒
SELECT ADDTIME('2023-10-27 10:00:00', '0 0:0:0.5'); -- 日期时间类型也支持

注意事项：

如果 expr 或 expr2 为 NULL，则 ADDTIME() 返回 NULL。
ADDTIME() 不会修改原始数据，它只是返回一个新的时间值。
expr2 的格式必须正确，否则可能会导致错误或意外的结果。
ADDTIME 可以与 DATE 类型的值一起使用,结果是 DATETIME 类型。

`SUBTIME()` 函数：时间值的减少

SUBTIME() 函数与 ADDTIME() 函数类似，但用于将指定的时间或日期时间值减去一个时间间隔。其基本语法如下：

SUBTIME(expr, expr2)

其中：

expr：可以是时间或日期时间类型的表达式，表示要进行减法运算的时间值。
expr2：是一个时间表达式，表示要减去的时间间隔。

expr2 的格式与 ADDTIME() 函数相同。

示例 1：从当前时间减去 30 分钟

SELECT SUBTIME(NOW(), '0:30:00');

这条 SQL 语句会返回当前时间减去 30 分钟后的日期时间值。

示例 2：从指定时间减去 1 天

SELECT SUBTIME('2023-10-27 10:00:00', '1 0:0:0');

这条 SQL 语句会返回 2023-10-26 10:00:00。

示例 3：使用列值进行减法

假设我们有一个名为 tasks 的表，包含以下字段：

id (INT, PRIMARY KEY)
deadline (DATETIME)
preparation_time (TIME)

我们可以使用 SUBTIME() 来计算任务准备的开始时间：

SELECT id, deadline, preparation_time, SUBTIME(deadline, preparation_time) AS start_time
FROM tasks;

这个查询会返回每个任务的 id、deadline、preparation_time 和计算出的 start_time。

示例 4：秒的小数部分

SELECT SUBTIME('10:00:00', '0:0:0.25'); -- 减去 0.25 秒
SELECT SUBTIME('2023-10-27 10:00:00', '0 0:0:0.25'); -- 日期时间类型也支持

示例 5：从 DATE 类型的值减去时间

SELECT SUBTIME('2023-10-27', '10:00:00');

这将返回 ‘2023-10-26 14:00:00’。结果是 DATETIME 类型。

注意事项：

如果 expr 或 expr2 为 NULL，则 SUBTIME() 返回 NULL。
SUBTIME() 不会修改原始数据，它只是返回一个新的时间值。
expr2 的格式必须正确，否则可能会导致错误或意外的结果。
SUBTIME 可以与 DATE 类型的值一起使用,结果是 DATETIME 类型。

`ADDTIME()` 与 `SUBTIME()` 的比较

特性	`ADDTIME()`	`SUBTIME()`
功能	将时间间隔加到时间值上	从时间值中减去时间间隔
语法	`ADDTIME(expr, expr2)`	`SUBTIME(expr, expr2)`
`expr` 类型	`TIME` 或 `DATETIME`	`TIME` 或 `DATETIME`
`expr2` 类型	时间表达式 (例如 ‘HH:MM:SS’, ‘D HH:MM:SS’)	时间表达式 (例如 ‘HH:MM:SS’, ‘D HH:MM:SS’)
返回值类型	`TIME` 或 `DATETIME` (取决于 `expr`)	`TIME` 或 `DATETIME` (取决于 `expr`)
对 NULL 的处理	如果 `expr` 或 `expr2` 为 `NULL`，则返回 `NULL`	如果 `expr` 或 `expr2` 为 `NULL`，则返回 `NULL`

实际应用场景举例

1. 计算订单预计送达时间：

假设你有一个电商平台，需要根据订单的下单时间和预计配送时间计算订单的预计送达时间。

SELECT
    order_id,
    order_time,
    delivery_time,
    ADDTIME(order_time, delivery_time) AS estimated_delivery_time
FROM
    orders;

2. 计算会议的结束时间：

如果你需要根据会议的开始时间和持续时间计算会议的结束时间，可以使用以下 SQL 语句：

SELECT
    meeting_id,
    start_time,
    duration,
    ADDTIME(start_time, duration) AS end_time
FROM
    meetings;

3. 计算任务的剩余时间：

假设你有一个任务管理系统，需要计算每个任务的剩余时间。

SELECT
    task_id,
    deadline,
    NOW(),
    SUBTIME(deadline, NOW()) AS remaining_time
FROM
    tasks
WHERE
    deadline > NOW();

4. 计算员工的加班时间：

假设你有一个考勤系统，需要计算员工的加班时间。

SELECT
    employee_id,
    end_time,
    official_work_hours,
    SUBTIME(end_time, official_work_hours) AS overtime
FROM
    attendance_records
WHERE
    end_time > official_work_hours;

5. 延迟任务的执行时间:

在某些调度系统中，可能需要延迟任务的执行时间。可以使用 ADDTIME 轻松实现。

UPDATE tasks SET scheduled_time = ADDTIME(scheduled_time, '0:15:00') WHERE task_id = 123; -- 将任务 123 的执行时间延迟 15 分钟。

6. 计算时间段的开始时间:

假设已知时间段的结束时间和持续时间，可以使用 SUBTIME 计算开始时间。

SELECT SUBTIME('2023-11-08 18:00:00', '2:30:00'); --  计算 2023-11-08 18:00:00 前 2 小时 30 分钟的时间

结合其他函数使用

ADDTIME() 和 SUBTIME() 还可以与其他 MySQL 函数结合使用，以实现更复杂的时间计算。

示例 1：结合 DATE_FORMAT() 格式化输出

SELECT DATE_FORMAT(ADDTIME(NOW(), '2:00:00'), '%Y-%m-%d %H:%i:%s');

这条 SQL 语句会返回当前时间加上 2 小时后的日期时间值，并将其格式化为 YYYY-MM-DD HH:MM:SS 的字符串。

示例 2：结合 TIMESTAMPDIFF() 计算时间差

SELECT TIMESTAMPDIFF(MINUTE, NOW(), ADDTIME(NOW(), '1:00:00'));

这条 SQL 语句会返回当前时间到当前时间加 1 小时之间的分钟数差，结果应该是 60。

示例 3：结合 CURDATE() 获取当前日期并添加时间

SELECT ADDTIME(CURDATE(), '8:00:00');

这条 SQL 语句会返回今天的日期加上 8 小时后的日期时间值，也就是今天的 8 点。

示例 4：结合 DATE_ADD() 和 DATE_SUB() （虽然 ADDTIME 和 SUBTIME 更适合时间值的加减）

虽然 ADDTIME 和 SUBTIME 更适合处理时间值的加减，但 DATE_ADD 和 DATE_SUB 更适合处理日期值的加减。不过，也可以将它们结合使用。

SELECT DATE_ADD(NOW(), INTERVAL '1:30' HOUR_MINUTE); -- 相当于 ADDTIME(NOW(), '1:30:00')
SELECT DATE_SUB(NOW(), INTERVAL '0:45' MINUTE_SECOND); -- 相当于 SUBTIME(NOW(), '0:45:00')

虽然结果相同，但使用 ADDTIME 和 SUBTIME 在处理纯时间值时通常更简洁。

性能考量

ADDTIME() 和 SUBTIME() 函数的性能通常是可以接受的，但对于大量数据的处理，还是需要注意一些优化技巧。

避免在 WHERE 子句中使用函数： 如果需要在 WHERE 子句中使用 ADDTIME() 或 SUBTIME()，尽量避免直接对列进行函数操作，这样可以防止索引失效。例如，不要写 WHERE ADDTIME(date_column, '1:00:00') > NOW()，而应该尝试改写成 WHERE date_column > SUBTIME(NOW(), '1:00:00')。
预先计算时间间隔： 如果时间间隔是固定的，可以将其预先计算好，然后在 SQL 语句中直接使用计算结果，避免重复计算。
使用合适的索引： 确保相关的列上建立了合适的索引，以提高查询效率。

总结

ADDTIME() 和 SUBTIME() 函数是 MySQL 中用于时间值加减运算的两个重要工具。它们能够方便地进行时间段的增加或减少，适用于各种业务场景。通过掌握这两个函数的使用方法和注意事项，我们可以更有效地处理时间相关的数据，并构建更强大的应用程序。理解这两个函数，可以帮助你更优雅的处理时间，从而提升开发效率。

MySQL 时间的加减运算：ADDTIME() 与 SUBTIME() 的深度剖析

ADDTIME() 函数：时间值的增加

SUBTIME() 函数：时间值的减少

ADDTIME() 与 SUBTIME() 的比较