SQL 年月日加减法|极客教程

SQL 年月日加减法，你需要在给定日期的基础上加上或减去若干天、月或年。以员工 CLARK 的 HIREDATE 为例，你希望计算出 6 个不同的日期：CLARK 入职前后 5 天的日期，CLARK 入职前后 5 个月的日期，以及 CLARK 入职前后 5 年的日期。CLARK 的 HIREDATE 是“09-JUN-1981”（1981 年 6 月 9 日），你想得到如下所示的结果集。

SQL 年月日加减法问题描述

你需要在给定日期的基础上加上或减去若干天、月或年。以员工 CLARK 的 HIREDATE 为例，你希望计算出 6 个不同的日期：CLARK 入职前后 5 天的日期，CLARK 入职前后 5 个月的日期，以及 CLARK 入职前后 5 年的日期。CLARK 的 HIREDATE 是“09-JUN-1981”（1981 年 6 月 9 日），你想得到如下所示的结果集。

HD_MINUS_5D HD_PLUS_5D  HD_MINUS_5M HD_PLUS_5M  HD_MINUS_5Y HD_PLUS_5Y
----------- ----------- ----------- ----------- ----------- -----------
04-JUN-1981 14-JUN-1981 09-JAN-1981 09-NOV-1981 09-JUN-1976 09-JUN-1986
12-NOV-1981 22-NOV-1981 17-JUN-1981 17-APR-1982 17-NOV-1976 17-NOV-1986
18-JAN-1982 28-JAN-1982 23-AUG-1981 23-JUN-1982 23-JAN-1977 23-JAN-1987

SQL 年月日加减法解决方案

DB2
支持针对日期的加法和减法运算，但不论加上一个数还是减去一个数，后面都要指定对应的时间单位。

1 select hiredate -5 day   as hd_minus_5D,
2        hiredate +5 day   as hd_plus_5D,
3        hiredate -5 month as hd_minus_5M,
4        hiredate +5 month as hd_plus_5M,
5        hiredate -5 year  as hd_minus_5Y,
6        hiredate +5 year  as hd_plus_5Y
7   from emp
8  where deptno = 10

Oracle
若要加上或减去若干天，使用加法或减法即可。若要加减若干个月或年，则需要使用 ADD_MONTHS 函数。

1 select hiredate-5                 as hd_minus_5D,
2        hiredate+5                 as hd_plus_5D,
3        add_months(hiredate,-5)    as hd_minus_5M,
4        add_months(hiredate,5)     as hd_plus_5M,
5        add_months(hiredate,-5*12) as hd_minus_5Y,
6        add_months(hiredate,5*12)  as hd_plus_5Y
7   from emp
8  where deptno = 10

PostgreSQL
使用加减法，并使用 INTERVAL 关键字指定要加上或者减去的时间单位。在指定 INTERVAL 值的时候，必须使用英文单引号。

1 select hiredate - interval '5 day'   as hd_minus_5D,
2        hiredate + interval '5 day'   as hd_plus_5D,
3        hiredate - interval '5 month' as hd_minus_5M,
4        hiredate + interval '5 month' as hd_plus_5M,
5        hiredate - interval '5 year'  as hd_minus_5Y,
6        hiredate + interval '5 year'  as hd_plus_5Y
7   from emp
8  where deptno=10

MySQL
使用加减法，并使用 INTERVAL 关键字指定要加上或者减去的时间单位。不同于上述 PostgreSQL 的解决方案，指定 INTERVAL 值不必使用英文单引号。

1 select hiredate - interval 5 day   as hd_minus_5D,
2        hiredate + interval 5 day   as hd_plus_5D,
3        hiredate - interval 5 month as hd_minus_5M,
4        hiredate + interval 5 month as hd_plus_5M,
5        hiredate - interval 5 year  as hd_minus_5Y,
6        hiredate + interval 5 year  as hd_plus_5Y
7   from emp
8  where deptno=10

除此之外，还可以使用 DATE_ADD 函数，如下所示。

1 select date_add(hiredate,interval -5 day)   as hd_minus_5D,
2        date_add(hiredate,interval  5 day)   as hd_plus_5D,
3        date_add(hiredate,interval -5 month) as hd_minus_5M,
4        date_add(hiredate,interval  5 month) as hd_plus_5M,
5        date_add(hiredate,interval -5 year)  as hd_minus_5Y,
6        date_add(hiredate,interval  5 year)  as hd_plus_5DY
7   from emp
8  where deptno=10

SQL Server
使用 DATEADD 函数在给定日期值的基础上加上或者减去若个时间单位。

1 select dateadd(day,-5,hiredate)   as hd_minus_5D,
2        dateadd(day,5,hiredate)    as hd_plus_5D,
3        dateadd(month,-5,hiredate) as hd_minus_5M,
4        dateadd(month,5,hiredate)  as hd_plus_5M,
5        dateadd(year,-5,hiredate)  as hd_minus_5Y,
6        dateadd(year,5,hiredate)   as hd_plus_5Y
7   from emp
8  where deptno = 10

SQL 年月日加减法扩展知识

Oracle 的解决方案利用了一个技巧：执行日期运算时，整数值代表天数。然而，这仅适用于 DATE 类型的运算。Oracle 9i 数据库引入了 TIMESTAMP 类型。对于这种新的日期数据类型，需要使用 PostgreSQL 解决方案中的 INTERVAL。如果要把 TIMESTAMP 类型传递给诸如 ADD_MONTHS 这样的旧式日期函数的话，还有一件事要注意：TIMESTAMP 值里面可能包含了精确到秒的数据，旧式的日期函数会忽略它们。
SQL 的 ISO 标准语法里规定了 INTERVAL 关键字以及紧随其后的字符串常量。该标准要求 INTERVAL 值必须位于英文单引号内。PostgreSQL ( 和 Oracle 9i 数据库及其后续版本 ) 遵循了该标准。MySQL 则不支持英文单引号，略微偏离了标准。