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