日期加减与时间差

日期加减和时间差计算是数据处理中最常用的日期操作之一。

📊 支持情况

1. 日期加法

MySQL DATE_ADD / DATE_SUB

-- 加1天
SELECT DATE_ADD('2024-01-14', INTERVAL 1 DAY);           -- 2024-01-15
SELECT '2024-01-14' + INTERVAL 1 DAY;                    -- 2024-01-15

-- 加1周
SELECT DATE_ADD('2024-01-14', INTERVAL 1 WEEK);          -- 2024-01-21

-- 加1月
SELECT DATE_ADD('2024-01-14', INTERVAL 1 MONTH);         -- 2024-02-14

-- 加3个月
SELECT DATE_ADD('2024-01-14', INTERVAL 3 MONTH);         -- 2024-04-14

-- 加1年
SELECT DATE_ADD('2024-01-14', INTERVAL 1 YEAR);          -- 2025-01-14

-- 减法
SELECT DATE_SUB('2024-01-14', INTERVAL 1 DAY);           -- 2024-01-13
SELECT DATE_SUB('2024-01-14', INTERVAL 1 MONTH);         -- 2023-12-14

Oracle ADD_MONTHS

-- 加1月
SELECT ADD_MONTHS(DATE '2024-01-14', 1) FROM dual;       -- 2024-02-14

-- 加12个月
SELECT ADD_MONTHS(DATE '2024-01-14', 12) FROM dual;      -- 2025-01-14

-- 减法
SELECT ADD_MONTHS(DATE '2024-01-14', -1) FROM dual;      -- 2023-12-14

加减指定天数

-- MySQL
SELECT '2024-01-14' + 7;         -- 2024-01-21（天数）
SELECT '2024-01-14' - 7;         -- 2024-01-07

-- Oracle
SELECT DATE '2024-01-14' + 7 FROM dual;    -- 2024-01-21
SELECT DATE '2024-01-14' - 7 FROM dual;    -- 2024-01-07

-- Hive / MaxCompute
SELECT date_add('2024-01-14', 7);          -- 2024-01-21
SELECT date_sub('2024-01-14', 7);          -- 2024-01-07

加减时间单位

-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR);           -- 加1小时
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);        -- 加30分钟
SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND);         -- 加1秒

-- Oracle
SELECT SYSTIMESTAMP + INTERVAL '1' HOUR FROM dual;
SELECT SYSTIMESTAMP + INTERVAL '30' MINUTE FROM dual;
SELECT SYSTIMESTAMP + INTERVAL '1' SECOND FROM dual;

2. 时间差计算

MySQL DATEDIFF / TIMESTAMPDIFF

-- 天数差
SELECT DATEDIFF('2024-01-20', '2024-01-14');  -- 6

-- 月份差
SELECT TIMESTAMPDIFF(MONTH, '2024-01-14', '2024-04-14');  -- 3

-- 年份差
SELECT TIMESTAMPDIFF(YEAR, '2022-01-14', '2024-01-14');   -- 2

-- 小时差
SELECT TIMESTAMPDIFF(HOUR, '2024-01-14 10:00', '2024-01-14 18:00');  -- 8

-- 分钟差
SELECT TIMESTAMPDIFF(MINUTE, '2024-01-14 10:00', '2024-01-14 10:30');  -- 30

-- 秒差
SELECT TIMESTAMPDIFF(SECOND, '2024-01-14 10:00:00', '2024-01-14 10:00:30');  -- 30

Oracle 月份差

-- MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(DATE '2024-04-14', DATE '2024-01-14') FROM dual;  -- 3

-- 天数差
SELECT TO_DATE('2024-01-20') - TO_DATE('2024-01-14') FROM dual;  -- 6

-- 时间差（小时/分钟/秒）
SELECT (SYSDATE - DATE '2024-01-14') * 24 FROM dual;        -- 小时差
SELECT (SYSDATE - DATE '2024-01-14') * 24 * 60 FROM dual;   -- 分钟差
SELECT (SYSDATE - DATE '2024-01-14') * 24 * 60 * 60 FROM dual;  -- 秒差

-- NUMTODSINTERVAL
SELECT EXTRACT(DAY FROM (SYSDATE - DATE '2024-01-14')) FROM dual;

Hive / MaxCompute

-- 天数差
SELECT datediff('2024-01-20', '2024-01-14');  -- 6

-- 月份差
SELECT months_between('2024-04-14', '2024-01-14');  -- 3.0

-- 日期加减
SELECT date_add('2024-01-14', 7);   -- 2024-01-21
SELECT date_sub('2024-01-14', 7);   -- 2024-01-07

ClickHouse

-- 日期差
SELECT date_diff('day', toDate('2024-01-14'), toDate('2024-01-20'));  -- 6

-- 日期加减
SELECT toDate('2024-01-14') + interval 7 day;    -- 2024-01-21
SELECT toDate('2024-01-14') - interval 1 month;  -- 2023-12-14

-- 时间差
SELECT date_diff('hour', toDateTime('2024-01-14 10:00'), toDateTime('2024-01-14 18:00'));  -- 8

3. 复杂日期计算

计算月末日期

-- MySQL LAST_DAY
SELECT LAST_DAY('2024-01-14');  -- 2024-01-31

-- 加上30天（跨月）
SELECT LAST_DAY(DATE_ADD('2024-01-14', INTERVAL 30 DAY));  -- 2024-02-29

-- Oracle
SELECT LAST_DAY(TO_DATE('2024-01-14', 'YYYY-MM-DD')) FROM dual;

计算季度开始/结束

-- MySQL - 季度开始
SELECT DATE_FORMAT('2024-03-15', '%Y-01-01');  -- Q1开始
SELECT MAKEDATE(YEAR('2024-03-15'), 1) + INTERVAL (QUARTER('2024-03-15') - 1) * 3 MONTH;

-- Oracle - 季度开始
SELECT TRUNC(TO_DATE('2024-03-15', 'YYYY-MM-DD'), 'Q') FROM dual;  -- 2024-01-01

计算周开始/结束

-- MySQL - 周开始（周一）
SELECT DATE_SUB('2024-01-14', INTERVAL WEEKDAY('2024-01-14') DAY);
-- 周开始（周日）
SELECT DATE_SUB('2024-01-14', INTERVAL DAYOFWEEK('2024-01-14') - 1 DAY);

-- Oracle - 周开始（周一）
SELECT NEXT_DAY(TRUNC(SYSDATE) - 7, 'MONDAY') FROM dual;

计算年龄

-- MySQL
SELECT
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users;

-- Oracle
SELECT
    name,
    birth_date,
    FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) AS age
FROM users;

4. 实战案例

场景1：会员到期计算

-- MySQL - 计算30天后到期
SELECT
    user_id,
    member_since,
    DATE_ADD(member_since, INTERVAL 1 YEAR) AS expire_date,
    DATEDIFF(DATE_ADD(member_since, INTERVAL 1 YEAR), CURDATE()) AS days_until_expire
FROM memberships
WHERE expire_date > CURDATE();

-- 即将过期（7天内）
SELECT *
FROM memberships
WHERE DATE_ADD(member_since, INTERVAL 1 YEAR) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

场景2：订单时间窗口

-- MySQL - 查询7天内的订单
SELECT *
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

-- 查询3个月内的订单
SELECT *
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

-- 查询本季度订单
SELECT *
FROM orders
WHERE order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01');

场景3：计算工龄

-- MySQL
SELECT
    emp_id,
    hire_date,
    TIMESTAMPDIFF(YEAR, hire_date, CURDATE()) AS years_of_service,
    TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS months_of_service
FROM employees;

场景4：周期性任务调度

-- MySQL - 下周一执行
SELECT DATE_ADD(
    CURDATE(),
    INTERVAL (8 - WEEKDAY(CURDATE())) DAY
) AS next_monday;

-- 下个月第一天
SELECT DATE_ADD(
    DATE_FORMAT(CURDATE(), '%Y-%m-01'),
    INTERVAL 1 MONTH
) AS next_month_first;

-- 本季度末
SELECT MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL (QUARTER(CURDATE()) * 3 - 1) MONTH + INTERVAL -1 DAY;

5. 时间差格式化

-- MySQL - 格式化为X天X小时X分X秒
SELECT
    TIMESTAMPDIFF(SECOND, start_time, end_time) AS total_seconds,
    CONCAT(
        FLOOR(TIMESTAMPDIFF(SECOND, start_time, end_time) / 86400), '天',
        MOD(FLOOR(TIMESTAMPDIFF(SECOND, start_time, end_time) / 3600), 24), '小时',
        MOD(FLOOR(TIMESTAMPDIFF(SECOND, start_time, end_time) / 60), 60), '分',
        MOD(TIMESTAMPDIFF(SECOND, start_time, end_time), 60), '秒'
    ) AS duration
FROM events;

⚠️ 注意事项

1. 月末日期边界

-- MySQL - 1月31日加1月
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);  -- 2024-02-29（自动调整）

-- Oracle
SELECT ADD_MONTHS(DATE '2024-01-31', 1) FROM dual;  -- 2024-02-29

2. 时区问题

-- MySQL 设置时区
SET time_zone = '+08:00';

-- Oracle 查看时区
SELECT SESSIONTIMEZONE FROM dual;
SELECT DBTIMEZONE FROM dual;

3. 闰年问题

-- 2024-02-29 加1年
SELECT DATE_ADD('2024-02-29', INTERVAL 1 YEAR);  -- 2025-02-28（自动调整）

-- 2024-02-29 加4年
SELECT DATE_ADD('2024-02-29', INTERVAL 4 YEAR);  -- 2028-02-29

📝 练习题

-- 建表
CREATE TABLE subscriptions (
    sub_id INT,
    user_id INT,
    start_date DATE,
    plan_months INT,
    status STRING
);

INSERT INTO subscriptions VALUES
(1, 101, '2024-01-01', 3, 'active'),
(2, 102, '2024-01-15', 6, 'active'),
(3, 103, '2023-10-01', 12, 'expired');

要求：

1. 计算每个订阅的到期日期
2. 计算每个订阅的剩余天数
3. 找出7天内到期的订阅
4. 计算每个订阅已使用的月份数

-- 参考答案（MySQL）
-- 1. 到期日期
SELECT
    sub_id,
    start_date,
    DATE_ADD(start_date, INTERVAL plan_months MONTH) AS expire_date
FROM subscriptions;

-- 2. 剩余天数
SELECT
    sub_id,
    DATEDIFF(DATE_ADD(start_date, INTERVAL plan_months MONTH), CURDATE()) AS days_left
FROM subscriptions;

-- 3. 即将到期
SELECT *
FROM subscriptions
WHERE DATE_ADD(start_date, INTERVAL plan_months MONTH)
      BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

-- 4. 已使用月份
SELECT
    sub_id,
    TIMESTAMPDIFF(MONTH, start_date, CURDATE()) AS used_months,
    plan_months
FROM subscriptions;