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

---

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

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00' + INTERVAL '80' SECOND   FROM DUAL;
+----------------------------------------------+
| '2000-01-01 00:00:00' + INTERVAL '80' SECOND |
+----------------------------------------------+
| 2000-01-01 00:01:20                          |
+----------------------------------------------+

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

TO_DATE('2000-01-01 00:00:00') + INTERVAL '80' SECOND
-------------------
2000-01-01 00:01:20

• 2. '2000-01-01 00:00:00' - INTERVAL '80' SECOND

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00' - INTERVAL '80' SECOND   FROM DUAL;
+----------------------------------------------+
| '2000-01-01 00:00:00' - INTERVAL '80' SECOND |
+----------------------------------------------+
| 1999-12-31 23:58:40                          |
+----------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' SECOND   FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' SECOND
-------------------
1999-12-31 23:58:40

• 3. '2000-01-01 00:00:00' + INTERVAL '80' SECOND(2)

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00' + INTERVAL '80' SECOND(2)   FROM DUAL;
+-------------------------------------------------+
| '2000-01-01 00:00:00' + INTERVAL '80' SECOND(2) |
+-------------------------------------------------+
| 2000-01-01 00:01:20                             |
+-------------------------------------------------+        
        
-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '80' SECOND(2)   FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  + INTERVAL '80' SECOND(2) 
-------------------
2000-01-01 00:01:20

• 4. '2000-01-01 00:00:00' - INTERVAL '80' SECOND(2)

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00' - INTERVAL '80' SECOND(2)   FROM DUAL;
+-------------------------------------------------+
| '2000-01-01 00:00:00' - INTERVAL '80' SECOND(2) |
+-------------------------------------------------+
| 1999-12-31 23:58:40                             |
+-------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' SECOND(2)   FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' SECOND(2) 
-------------------
1999-12-31 23:58:40

• 5. '2000-01-01 00:00:00' + INTERVAL '80' MINUTE

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '80' MINUTE   FROM DUAL;
+-----------------------------------------------+
| '2000-01-01 00:00:00'  + INTERVAL '80' MINUTE |
+-----------------------------------------------+
| 2000-01-01 01:20:00                           |
+-----------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '80' MINUTE   FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  + INTERVAL '80' MINUTE
-------------------
2000-01-01 01:20:00

• 6. '2000-01-01 00:00:00' - INTERVAL '80' MINUTE

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '80' MINUTE   FROM DUAL;
+-----------------------------------------------+
| '2000-01-01 00:00:00'  - INTERVAL '80' MINUTE |
+-----------------------------------------------+
| 1999-12-31 22:40:00                           |
+-----------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' MINUTE   FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  - INTERVAL '80' MINUTE
-------------------
1999-12-31 22:40:00

• 7. '2000-01-01 00:00:00' + INTERVAL '15:30' MINUTE TO SECOND

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '15:30' MINUTE TO SECOND FROM DUAL;
+------------------------------------------------------------+
| '2000-01-01 00:00:00'  + INTERVAL '15:30' MINUTE TO SECOND |
+------------------------------------------------------------+
| 2000-01-01 00:15:30                                        |
+------------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '15:30' MINUTE TO SECOND FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  + INTERVAL '15:30' MINUTE TO SECOND
-------------------
2000-01-01 00:15:30

• 8. '2000-01-01 00:00:00' - INTERVAL '15:30' MINUTE TO SECOND

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '15:30' MINUTE TO SECOND FROM DUAL;
+------------------------------------------------------------+
| '2000-01-01 00:00:00'  - INTERVAL '15:30' MINUTE TO SECOND |
+------------------------------------------------------------+
| 1999-12-31 23:44:30                                        |
+------------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '15:30' MINUTE TO SECOND FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  - INTERVAL '15:30' MINUTE TO SECOND
-------------------
1999-12-31 23:44:30

• 9. '2000-01-01 00:00:00' + INTERVAL '09:08:07' HOUR to SECOND

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '09:08:07'  HOUR to SECOND FROM DUAL;
+--------------------------------------------------------------+
| '2000-01-01 00:00:00'  + INTERVAL '09:08:07'  HOUR to SECOND |
+--------------------------------------------------------------+
| 2000-01-01 09:08:07                                          |
+--------------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '09:08:07'  HOUR to SECOND FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  + INTERVAL '09:08:07'  HOUR to SECOND
-------------------
2000-01-01 09:08:07

• 10. '2000-01-01 00:00:00' - INTERVAL '09:08:07' HOUR to SECOND

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '09:08:07'  HOUR to SECOND FROM DUAL;
+--------------------------------------------------------------+
| '2000-01-01 00:00:00'  - INTERVAL '09:08:07'  HOUR to SECOND |
+--------------------------------------------------------------+
| 1999-12-31 14:51:53                                          |
+--------------------------------------------------------------+

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

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

• 11. '2000-01-01 00:00:00' + INTERVAL '09:08:07.666666' HOUR TO SECOND(7)

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '09:08:07.666666' HOUR TO SECOND(7) FROM DUAL;
+-----------------------------------------------------------------------+
| '2000-01-01 00:00:00'  + INTERVAL '09:08:07.666666' HOUR TO SECOND(7) |
+-----------------------------------------------------------------------+
| NULL                                                                  |
+-----------------------------------------------------------------------+

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

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

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

• 12. '2000-01-01 00:00:00' - INTERVAL '09:08:07.666666' HOUR TO SECOND(7)

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '09:08:07.666666' HOUR TO SECOND(7) FROM DUAL;
+-----------------------------------------------------------------------+
| '2000-01-01 00:00:00'  - INTERVAL '09:08:07.666666' HOUR TO SECOND(7) |
+-----------------------------------------------------------------------+
| NULL                                                                  |
+-----------------------------------------------------------------------+

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

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '09:08:07.666666' HOUR TO SECOND(7) FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  - INTERVAL '09:08:07.666666' HOUR TO SECOND(7)
-------------------
1999-12-31 14:51:52

• 13. '2000-01-01 00:00:00' + INTERVAL '2 1:3:4' DAY to SECOND

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '2 1:3:4' DAY to SECOND FROM DUAL;
+-----------------------------------------------------------+
| '2000-01-01 00:00:00'  + INTERVAL '2 1:3:4' DAY to SECOND |
+-----------------------------------------------------------+
| 2000-01-03 01:03:04                                       |
+-----------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '2 1:3:4' DAY to SECOND FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  + INTERVAL '2 1:3:4' DAY to SECOND
-------------------
2000-01-03 01:03:04

• 14. '2000-01-01 00:00:00' - INTERVAL '2 1:3:4' DAY to SECOND

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '2 1:3:4' DAY to SECOND FROM DUAL;
+-----------------------------------------------------------+
| '2000-01-01 00:00:00'  - INTERVAL '2 1:3:4' DAY to SECOND |
+-----------------------------------------------------------+
|  1999-12-29 22:56:56                                      |
+-----------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '2 1:3:4' DAY to SECOND FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  - INTERVAL '2 1:3:4' DAY to SECOND
-------------------
1999-12-29 22:56:56

• 15. '2000-01-01 00:00:00' + INTERVAL '11 10:09' DAY TO MINUTE

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '11 10:09' DAY TO MINUTE FROM DUAL;
+------------------------------------------------------------+
| '2000-01-01 00:00:00'  + INTERVAL '11 10:09' DAY TO MINUTE |
+------------------------------------------------------------+
| 2000-01-12 10:09:00                                        |
+------------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '11 10:09' DAY TO MINUTE FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  + INTERVAL '11 10:09' DAY TO MINUTE
-------------------
2000-01-12 10:09:00

• 16. '2000-01-01 00:00:00' - INTERVAL '11 10:09' DAY TO MINUTE

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '11 10:09' DAY TO MINUTE FROM DUAL;
+------------------------------------------------------------+
| '2000-01-01 00:00:00'  - INTERVAL '11 10:09' DAY TO MINUTE |
+------------------------------------------------------------+
| 1999-12-20 13:51:00                                         |
+------------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '11 10:09' DAY TO MINUTE FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  - INTERVAL '11 10:09' DAY TO MINUTE
-------------------
1999-12-20 13:51:00

• 17. '2000-01-01 00:00:00' + INTERVAL '100 10' DAY(3) TO HOUR

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
+-----------------------------------------------------------+
| '2000-01-01 00:00:00'  + INTERVAL '100 10' DAY(3) TO HOUR |
+-----------------------------------------------------------+
| 2000-04-11 10:00:00                                       |
+-----------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  + INTERVAL '100 10' DAY(3) TO HOUR
-------------------
2000-04-11 10:00:00

• 18. '2000-01-01 00:00:00' - INTERVAL '100 10' DAY(3) TO HOUR

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
+-----------------------------------------------------------+
| '2000-01-01 00:00:00'  + INTERVAL '100 10' DAY(3) TO HOUR |
+-----------------------------------------------------------+
| 1999-09-22 14:00:00                                      |
+-----------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  - INTERVAL '100 10' DAY(3) TO HOUR
-------------------
1999-09-22 14:00:00

• 19. '2000-01-01 00:00:00' + INTERVAL '123-2' YEAR(3) TO MONTH

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  + INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
+------------------------------------------------------------+
| '2000-01-01 00:00:00'  + INTERVAL '123-2' YEAR(3) TO MONTH |
+------------------------------------------------------------+
| 2123-03-01 00:00:00                                        |
+------------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  + INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  + INTERVAL '123-2' YEAR(3) TO MONTH
-------------------
2123-03-01 00:00:00

• 20. '2000-01-01 00:00:00' - INTERVAL '123-2' YEAR(3) TO MONTH

-- GreatSQL
greatsql> SELECT '2000-01-01 00:00:00'  - INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
+------------------------------------------------------------+
| '2000-01-01 00:00:00'  - INTERVAL '123-2' YEAR(3) TO MONTH |
+------------------------------------------------------------+
| 1876-11-01 00:00:00                                         |
+------------------------------------------------------------+

-- Oracle
SQL> SELECT TO_DATE('2000-01-01 00:00:00')  - INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
TO_DATE('2000-01-01 00:00:00')  - INTERVAL '123-2' YEAR(3) TO MONTH
-------------------
1876-11-01 00:00:00

• 21. '0001-01-01 00:00:00' - INTERVAL '123-2' YEAR(3) TO MONTH

注意：

• 1. 对于极限年份的处理GreatSQL与Oracle不同。
• 2. 当输入时间或日期值不满足与 INTERVAL 格式内容减运算时，GreatSQL返回NULL。

-- GreatSQL
greatsql> SELECT '0001-01-01 00:00:00'  -  INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
+-------------------------------------------------------------+
| '0001-01-01 00:00:00'  -  INTERVAL '123-2' YEAR(3) TO MONTH |
+-------------------------------------------------------------+
| NULL                                                        |
+-------------------------------------------------------------+

SQL> SELECT TO_DATE('0001-01-02 00:00:00')  - INTERVAL '123-2' YEAR(3) TO MONTH   FROM DUAL;
TO_DATE('0001-01-02 00:00:00')  - INTERVAL '123-2' YEAR(3) TO MONTH
-------------------
0123-11-02 00:00:00

• 22. '0001-01-01 00:00:00' - INTERVAL '100 10' DAY(3) TO HOUR

-- GreatSQL
greatsql> SELECT '0001-01-01 00:00:00'  - INTERVAL '100 10' DAY(3) TO HOUR FROM DUAL;
+-----------------------------------------------------------+
| '0001-01-01 00:00:00'  - INTERVAL '100 10' DAY(3) TO HOUR |
+-----------------------------------------------------------+
| 0000-00-00 14:00:00                                       |
+-----------------------------------------------------------+

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

问题反馈

• 问题反馈 gitee (opens new window)

联系我们

扫码关注微信公众号