Oracle兼容-语法-DATETIME INTERVAL加减运算

1. 语法

1. DATETIME_expr [+|-] INTERVAL expr type

2. 定义和用法

GreatSQL支持对 DATETIME 类型数据进行 INTERVAL 加减运算。

若位于另一端的表达式是一个日期或日期时间值，则 INTERVAL expr type 只允许在 + 操作符的两端。对于 – 操作符，INTERVAL expr type 只允许在其右端，因为从一个时间间隔中提取一个日期或日期时间值无意义。

  1. 在Oracle中 INTERVAL 可作为字段类型建表等操作中使用，但GreatSQL不存在该类型，因此目前不支持 INTERVAL 作为字段类型使用。
  2. 目前支持范围仅为语法中日期值或时间值与 INTERVAL 加减操作，鉴于目前 GreatSQL 中 INTERVAL 语法实现方式，在Oracle语法中 INTERVAL 内对精度的设置例如：YEAR(3)，目前在兼容语法中精度值不具有实际使用意义。

3. 示例

说明： 目前GreatSQL中SYSDATE、NOW、SYSTIMESTAMP 等时间类型与Oracle返回时间值存在差异，因此下面示例中以固定年月时间演示 INTERVAL 与时间值加减语法操作后在 GreatSQL 中返回与Oracle返回做对比。

    1. '2000-01-01 00:00:00' + INTERVAL '80' SECOND
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00' + INTERVAL '80' SECOND   FROM DUAL;
3. +----------------------------------------------+
4. | '2000-01-01 00:00:00' + INTERVAL '80' SECOND |
5. +----------------------------------------------+
6. | 2000-01-01 00:01:20                          |
7. +----------------------------------------------+

9. -- Oracle
10. -- 先统一设置日期格式
11. SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
12. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '80' SECOND   FROM DUAL;

14. TO_DATE('2000-01-01 00:00:00') + INTERVAL '80' SECOND
15. -------------------
16. 2000-01-01 00:01:20
    1. '2000-01-01 00:00:00' - INTERVAL '80' SECOND
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00' - INTERVAL '80' SECOND   FROM DUAL;
3. +----------------------------------------------+
4. | '2000-01-01 00:00:00' - INTERVAL '80' SECOND |
5. +----------------------------------------------+
6. | 1999-12-31 23:58:40                          |
7. +----------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' SECOND   FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' SECOND
12. -------------------
13. 1999-12-31 23:58:40
    1. '2000-01-01 00:00:00' + INTERVAL '80' SECOND(2)
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00' + INTERVAL '80' SECOND(2)   FROM DUAL;
3. +-------------------------------------------------+
4. | '2000-01-01 00:00:00' + INTERVAL '80' SECOND(2) |
5. +-------------------------------------------------+
6. | 2000-01-01 00:01:20                             |
7. +-------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '80' SECOND(2)   FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  + INTERVAL '80' SECOND(2)
12. -------------------
13. 2000-01-01 00:01:20
    1. '2000-01-01 00:00:00' - INTERVAL '80' SECOND(2)
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00' - INTERVAL '80' SECOND(2)   FROM DUAL;
3. +-------------------------------------------------+
4. | '2000-01-01 00:00:00' - INTERVAL '80' SECOND(2) |
5. +-------------------------------------------------+
6. | 1999-12-31 23:58:40                             |
7. +-------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' SECOND(2)   FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' SECOND(2)
12. -------------------
13. 1999-12-31 23:58:40
    1. '2000-01-01 00:00:00' + INTERVAL '80' MINUTE
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '80' MINUTE   FROM DUAL;
3. +-----------------------------------------------+
4. | '2000-01-01 00:00:00'  + INTERVAL '80' MINUTE |
5. +-----------------------------------------------+
6. | 2000-01-01 01:20:00                           |
7. +-----------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '80' MINUTE   FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  + INTERVAL '80' MINUTE
12. -------------------
13. 2000-01-01 01:20:00
    1. '2000-01-01 00:00:00' - INTERVAL '80' MINUTE
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '80' MINUTE   FROM DUAL;
3. +-----------------------------------------------+
4. | '2000-01-01 00:00:00'  - INTERVAL '80' MINUTE |
5. +-----------------------------------------------+
6. | 1999-12-31 22:40:00                           |
7. +-----------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' MINUTE   FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' MINUTE
12. -------------------
13. 1999-12-31 22:40:00
    1. '2000-01-01 00:00:00' + INTERVAL '15:30' MINUTE TO SECOND
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '15:30' MINUTE TO SECOND FROM DUAL;
3. +------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  + INTERVAL '15:30' MINUTE TO SECOND |
5. +------------------------------------------------------------+
6. | 2000-01-01 00:15:30                                        |
7. +------------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '15:30' MINUTE TO SECOND FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  + INTERVAL '15:30' MINUTE TO SECOND
12. -------------------
13. 2000-01-01 00:15:30
    1. '2000-01-01 00:00:00' - INTERVAL '15:30' MINUTE TO SECOND
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '15:30' MINUTE TO SECOND FROM DUAL;
3. +------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  - INTERVAL '15:30' MINUTE TO SECOND |
5. +------------------------------------------------------------+
6. | 1999-12-31 23:44:30                                        |
7. +------------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '15:30' MINUTE TO SECOND FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '15:30' MINUTE TO SECOND
12. -------------------
13. 1999-12-31 23:44:30
    1. '2000-01-01 00:00:00' + INTERVAL '09:08:07' HOUR to SECOND
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '09:08:07'  HOUR to SECOND FROM DUAL;
3. +--------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  + INTERVAL '09:08:07'  HOUR to SECOND |
5. +--------------------------------------------------------------+
6. | 2000-01-01 09:08:07                                          |
7. +--------------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '09:08:07'  HOUR to SECOND FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  + INTERVAL '09:08:07'  HOUR to SECOND
12. -------------------
13. 2000-01-01 09:08:07
    1. '2000-01-01 00:00:00' - INTERVAL '09:08:07' HOUR to SECOND
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '09:08:07'  HOUR to SECOND FROM DUAL;
3. +--------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  - INTERVAL '09:08:07'  HOUR to SECOND |
5. +--------------------------------------------------------------+
6. | 1999-12-31 14:51:53                                          |
7. +--------------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '09:08:07'  HOUR to SECOND FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '09:08:07'  HOUR to SECOND
12. -------------------
13. 1999-12-31 14:51:53

注意： GreatSQL中若 INTERVAL 后数据与 type 关键字指定不一致时返回NULL。

    1. '2000-01-01 00:00:00' + INTERVAL '09:08:07.666666' HOUR TO SECOND(7)
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '09:08:07.666666' HOUR TO SECOND(7) FROM DUAL;
3. +-----------------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  + INTERVAL '09:08:07.666666' HOUR TO SECOND(7) |
5. +-----------------------------------------------------------------------+
6. | NULL                                                                  |
7. +-----------------------------------------------------------------------+

9. -- 可使用GreatSQL兼容语法 HOUR TO MICROSECOND 替换
10. greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '09:08:07.666666' HOUR TO MICROSECOND(7) FROM DUAL;
11. +-----------------------------------------------------------------------+
12. | '2000-01-01 00:00:00'  + INTERVAL '09:08:07.666666' HOUR TO MICROSECOND(7) |
13. +----------------------------------------------------------------------+
14. | 2000-01-01 09:08:07.666666                                           |
15. +----------------------------------------------------------------------+

17. -- Oracle
18. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '09:08:07.666666' HOUR TO SECOND(7) FROM DUAL;
19. TO_DATE('2000-01-01 00:00:00')  + INTERVAL '09:08:07.666666' HOUR TO SECOND(7)
20. -------------------
21. 2000-01-01 09:08:07

注意： 在GreatSQL中若 INTERVAL 运算后数据与 type 关键字指定不一致时返回NULL。

    1. '2000-01-01 00:00:00' - INTERVAL '09:08:07.666666' HOUR TO SECOND(7)
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '09:08:07.666666' HOUR TO SECOND(7) FROM DUAL;
3. +-----------------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  - INTERVAL '09:08:07.666666' HOUR TO SECOND(7) |
5. +-----------------------------------------------------------------------+
6. | NULL                                                                  |
7. +-----------------------------------------------------------------------+

9. -- 可使用GreatSQL兼容语法 HOUR TO MICROSECOND 替换
10. greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '09:08:07.666666' HOUR TO MICROSECOND(7) FROM DUAL;
11. +----------------------------------------------------------------------------+
12. | '2000-01-01 00:00:00'  - INTERVAL '09:08:07.666666' HOUR TO MICROSECOND(7) |
13. +----------------------------------------------------------------------------+
14. | 1999-12-31 14:51:52.333334                                                 |
15. +----------------------------------------------------------------------------+

17. -- Oracle
18. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '09:08:07.666666' HOUR TO SECOND(7) FROM DUAL;
19. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '09:08:07.666666' HOUR TO SECOND(7)
20. -------------------
21. 1999-12-31 14:51:52
    1. '2000-01-01 00:00:00' + INTERVAL '2 1:3:4' DAY to SECOND
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '2 1:3:4' DAY to SECOND FROM DUAL;
3. +-----------------------------------------------------------+
4. | '2000-01-01 00:00:00'  + INTERVAL '2 1:3:4' DAY to SECOND |
5. +-----------------------------------------------------------+
6. | 2000-01-03 01:03:04                                       |
7. +-----------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '2 1:3:4' DAY to SECOND FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  + INTERVAL '2 1:3:4' DAY to SECOND
12. -------------------
13. 2000-01-03 01:03:04
    1. '2000-01-01 00:00:00' - INTERVAL '2 1:3:4' DAY to SECOND
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '2 1:3:4' DAY to SECOND FROM DUAL;
3. +-----------------------------------------------------------+
4. | '2000-01-01 00:00:00'  - INTERVAL '2 1:3:4' DAY to SECOND |
5. +-----------------------------------------------------------+
6. |  1999-12-29 22:56:56                                      |
7. +-----------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '2 1:3:4' DAY to SECOND FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '2 1:3:4' DAY to SECOND
12. -------------------
13. 1999-12-29 22:56:56
    1. '2000-01-01 00:00:00' + INTERVAL '11 10:09' DAY TO MINUTE
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '11 10:09' DAY TO MINUTE FROM DUAL;
3. +------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  + INTERVAL '11 10:09' DAY TO MINUTE |
5. +------------------------------------------------------------+
6. | 2000-01-12 10:09:00                                        |
7. +------------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '11 10:09' DAY TO MINUTE FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  + INTERVAL '11 10:09' DAY TO MINUTE
12. -------------------
13. 2000-01-12 10:09:00
    1. '2000-01-01 00:00:00' - INTERVAL '11 10:09' DAY TO MINUTE
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '11 10:09' DAY TO MINUTE FROM DUAL;
3. +------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  - INTERVAL '11 10:09' DAY TO MINUTE |
5. +------------------------------------------------------------+
6. | 1999-12-20 13:51:00                                         |
7. +------------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '11 10:09' DAY TO MINUTE FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '11 10:09' DAY TO MINUTE
12. -------------------
13. 1999-12-20 13:51:00
    1. '2000-01-01 00:00:00' + INTERVAL '100 10' DAY(3) TO HOUR
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
3. +-----------------------------------------------------------+
4. | '2000-01-01 00:00:00'  + INTERVAL '100 10' DAY(3) TO HOUR |
5. +-----------------------------------------------------------+
6. | 2000-04-11 10:00:00                                       |
7. +-----------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  + INTERVAL '100 10' DAY(3) TO HOUR
12. -------------------
13. 2000-04-11 10:00:00
    1. '2000-01-01 00:00:00' - INTERVAL '100 10' DAY(3) TO HOUR
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
3. +-----------------------------------------------------------+
4. | '2000-01-01 00:00:00'  + INTERVAL '100 10' DAY(3) TO HOUR |
5. +-----------------------------------------------------------+
6. | 1999-09-22 14:00:00                                      |
7. +-----------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '100 10' DAY(3) TO HOUR
12. -------------------
13. 1999-09-22 14:00:00
    1. '2000-01-01 00:00:00' + INTERVAL '123-2' YEAR(3) TO MONTH
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
3. +------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  + INTERVAL '123-2' YEAR(3) TO MONTH |
5. +------------------------------------------------------------+
6. | 2123-03-01 00:00:00                                        |
7. +------------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  + INTERVAL '123-2' YEAR(3) TO MONTH
12. -------------------
13. 2123-03-01 00:00:00
    1. '2000-01-01 00:00:00' - INTERVAL '123-2' YEAR(3) TO MONTH
1. -- GreatSQL
2. greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
3. +------------------------------------------------------------+
4. | '2000-01-01 00:00:00'  - INTERVAL '123-2' YEAR(3) TO MONTH |
5. +------------------------------------------------------------+
6. | 1876-11-01 00:00:00                                         |
7. +------------------------------------------------------------+

9. -- Oracle
10. SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
11. TO_DATE('2000-01-01 00:00:00')  - INTERVAL '123-2' YEAR(3) TO MONTH
12. -------------------
13. 1876-11-01 00:00:00
    1. '0001-01-01 00:00:00' - INTERVAL '123-2' YEAR(3) TO MONTH

注意：

    1. 对于极限年份的处理GreatSQL与Oracle不同。
    1. 当输入时间或日期值不满足与 INTERVAL 格式内容减运算时，GreatSQL返回NULL。
1. -- GreatSQL
2. greatsql> SELECT '0001-01-01 00:00:00'  -  INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
3. +-------------------------------------------------------------+
4. | '0001-01-01 00:00:00'  -  INTERVAL '123-2' YEAR(3) TO MONTH |
5. +-------------------------------------------------------------+
6. | NULL                                                        |
7. +-------------------------------------------------------------+

9. SQL> SELECT TO_DATE('0001-01-02 00:00:00')  - INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
10. TO_DATE('0001-01-02 00:00:00')  - INTERVAL '123-2' YEAR(3) TO MONTH
11. -------------------
12. 0123-11-02 00:00:00
    1. '0001-01-01 00:00:00' - INTERVAL '100 10' DAY(3) TO HOUR
1. -- GreatSQL
2. greatsql> SELECT '0001-01-01 00:00:00'  - INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
3. +-----------------------------------------------------------+
4. | '0001-01-01 00:00:00'  - INTERVAL '100 10' DAY(3) TO HOUR |
5. +-----------------------------------------------------------+
6. | 0000-00-00 14:00:00                                       |
7. +-----------------------------------------------------------+

9. SQL> SELECT TO_DATE('0001-01 00:00:00')  - INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
10. TO_DATE('0001-01-01 00:00:00')  - INTERVAL '100 10' DAY(3) TO HOUR
11. -------------------
12. 0000-09-22 14:00:00

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx