顺子の杂货铺
生命不息，折腾不止，且行且珍惜~
        找回密码
当前位置：順子の杂货铺 > BigData > 正文

11-日期加减与时间差

2026-01-18 分类：BigData / BLOG / Database / SQL 阅读(6) 评论(0)
DMIT VPS

日期加减与时间差

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

📊 支持情况

数据库DATE_ADDADD_MONTHSINTERVALDATEDIFFTIMESTAMPDIFF
MySQL✅–✅✅✅
Oracle–✅✅–✅
ClickHouse––✅––
Hologres✅–✅✅✅
MaxCompute––✅✅✅
Hive––✅✅✅

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;
赞(0)
未经允许不得转载：順子の杂货铺 » 11-日期加减与时间差
标签：
Avatar photo

shunzi

一半忧伤，一半明媚！喜欢看书，听歌，写字，打游戏，交朋友！
搬瓦工VPS

相关推荐

评论 抢沙发

Avatar photo

取消

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
吐血推荐

New

热门文章

热门标签

sql (48)mysql (22)oracle (21)python (15)Hive (13)MaxCompute (13)Hologres (13)ClickHouse (12)rm (6)password (5)port (5)install (4)linux (4)log (4)join (3)pos (3)google (3)vi (3)tab (3)redis (3)deepin (3)htaccess (2)master (2)centos (2)kangle (2)easypanel (2)list (2)django (2)plsql (2)docker (2)

网站统计

  • 日志总数：88
  • 评论总数：55
  • 标签总数：125
  • 最后更新：2026-01-18

分享创造快乐

联系我们联系我们