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

1. 语法

1. SELECT SYSDATE + 1 FROM DUAL;
2. SELECT SYSDATE - 1 FROM DUAL;
3. SELECT SYSDATE - SYSDATE FROM DUAL;

2. 定义和用法

在GreatSQL中支持对 DATETIME 类型数据做加减运算，例如增加1秒、1天，使用该运算行为模式需要切换到ORACLE模式下。

  1. 只支持加减运算，不支持其他运算类型。
  2. 对 TIME 类型运算前，会先被转换成 DATETIME 类型。
  3. 会先将时间戳的值转换为日期值，并将 NUMBER 算术日期时间和间隔表达式中的常量解释为天数。
  4. 年份范围为：[0,9999]，不支持公元前的年份，溢出时将返回NULL，并产生warning。
  5. 当计算结果包含无限循环小数时，默认只保留10位，即：div_precincrement 选项默认值(4) + 6 = 10。
  6. 对YEAR类型加减运算时，默认转换将失败而无法运算时，会返回NULL , 并产生warning。
1. greatsql> SET sql_mode = ORACLE;

3. -- 例1：多个查询结果集合并，或者数值作为查询条件时
4. greatsql> SELECT TO_DATE('00:00:00','HH24:MI:SS') -  TO_DATE('00:00:01','HH24:MI:SS') FROM DUAL
5. UNION ALL
6. SELECT TO_DATE('00:00:00','HH24:MI:SS') -  TO_DATE('00:00:01','HH24:MI:SS') FROM DUAL;
7. +----------------------------------------------------------------------+
8. | to_date('00:00:00','HH24:MI:SS') -  to_date('00:00:01','HH24:MI:SS') |
9. +----------------------------------------------------------------------+
10. |                                                        -0.0000115741 |
11. |                                                        -0.0000115741 |
12. +----------------------------------------------------------------------+

14. - 例2：在单一查询计算结果作为字符串展示的时候，保留小数位长度不再限制保留10位
15. greatsql> SELECT TO_DATE('00:00:00','HH24:MI:SS') -  TO_DATE('00:00:01','HH24:MI:SS');
16. +----------------------------------------------------------------------+
17. | to_date('00:00:00','HH24:MI:SS') -  to_date('00:00:01','HH24:MI:SS') |
18. +----------------------------------------------------------------------+
19. |                                                -0.000011574074074074 |
20. +----------------------------------------------------------------------+

DATETIME类型运算兼容矩阵

Operand & Operator DATETIME TIMESTAMP TIME NUMBER
DATETIME - - - -
+ - - - DATETIME
— DECIMAL DECIMAL DECIMAL DATETIME
- - - -
/ - - - -
TIMESTAMP - - - -
+ - - - TIMESTAMP
- DECIMAL DECIMAL DECIMAL TIMESTAMP
- - - -
/ - - - -
DATE
+ - - - DATETIME
- DECIMAL DECIMAL DECIMAL DATETIME
- - - -
/ - - - -
TIME - - -
+ - - - DATETIME
- DECIMAL DECIMAL DECIMAL DATETIME
- - - -
/ - - - -
NUMBER - - - -
+ DATETIME TIMESTAMP DATETIME NA
- DATETIME TIMESTAMP DATETIME NA
* - - - NA
/ - - - NA

上表是 DATETIME 类型算术运算的兼容矩阵，破折号代表不支持的操作。

3. 与Oracle差异说明

  1. 默认日期差异日期保留精度不一致，在GreatSQL中默认保留10位小数精度，而Oracle中根据不同的精度数据不一样。
  2. 时间范围最大与最小范围不一致。在GreatSQL中年份范围是 [0,9999]，而Oracle中的范围是 [-4713,9999]。
  3. 加减字符串运算行为不同，例如字符串’a’，在Oracle中转换失败将会报错，而在GreatSQL中将转换为 ‘0’ 并产生警告，之后再进行运算。
  4. 在GreatSQL中，TIMESTAMP - TIMESTAMP 返回的结果是 DECIMAL 类型， Oracle 中返回 INTERVAL 类型。

4. 示例

1. greatsql> SET sql_mode = ORACLE;

3. greatsql> SET TIMESTAMP = 1;

5. -- +1/2 等同于 +12小时
6. greatsql> SELECT SYSDATE +1/2 ;
7. +----------------------------+
8. | SYSDATE +1/2               |
9. +----------------------------+
10. | 1970-01-01 12:00:01.000000 |
11. +----------------------------+

13. -- 等同于 +1天
14. greatsql> SELECT SYSDATE +1  ;
15. +----------------------------+
16. | SYSDATE +1                 |
17. +----------------------------+
18. | 1970-01-02 00:00:01.000000 |
19. +----------------------------+

21. -- 等同于 -1 天
22. greatsql> SELECT SYSDATE -1  ;
23. +----------------------------+
24. | SYSDATE -1                 |
25. +----------------------------+
26. | 1969-12-31 00:00:01.000000 |
27. +----------------------------+

29. greatsql> SELECT SYSDATE - SYSDATE;
30. +-------------------+
31. | SYSDATE - SYSDATE |
32. +-------------------+
33. |                 0 |
34. +-------------------+

36. -- 将'a'转换成'0'后再运算，并产生warning
37. greatsql> SELECT  SYSDATE + 'a';
38. +----------------------------+
39. | SYSDATE + 'a'              |
40. +----------------------------+
41. | 1970-01-01 00:00:01.000000 |
42. +----------------------------+
43. 1 row in set, 1 warning (0.00 sec)

45. greatsql> SHOW WARNINGS;
46. +---------+------+---------------------------------------+
47. | Level   | Code | Message                               |
48. +---------+------+---------------------------------------+
49. | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
50. +---------+------+---------------------------------------+

52. -- 不支持 乘/除 运算，
53. greatsql> SELECT SYSDATE *  10;
54. ERROR 7038 (42000): in oracle_mode: date * LONGLONG not allowed

56. greatsql> SELECT SYSDATE / 10;
57. ERROR 7038 (42000): in oracle_mode: date / LONGLONG not allowed

59. greatsql> SELECT SYSDATE + SYSDATE;
60. ERROR 7038 (42000): in oracle_mode: date + DATETIME not allowed

62. greatsql> SELECT SYSDATE + 1000000000000;
63. +-------------------------+
64. | SYSDATE + 1000000000000 |
65. +-------------------------+
66. | NULL                    |
67. +-------------------------+
68. 1 row in set, 1 warning (0.00 sec)

70. greatsql> SHOW WARNINGS;
71. +---------+------+--------------------------------------------+
72. | Level   | Code | Message                                    |
73. +---------+------+--------------------------------------------+
74. | Warning | 1441 | Datetime function: datetime field overflow |
75. +---------+------+--------------------------------------------+

77. -- 无法对YEAR做运算
78. greatsql> SELECT CAST(2023 AS YEAR) + 1;
79. +------------------------+
80. | CAST(2023 AS YEAR) + 1 |
81. +------------------------+
82. | NULL                   |
83. +------------------------+
84. 1 row in set, 1 warning (0.00 sec)

86. greatsql> SHOW WARNINGS;
87. +---------+------+----------------------------------+
88. | Level   | Code | Message                          |
89. +---------+------+----------------------------------+
90. | Warning | 1292 | Incorrect datetime value: '2023' |
91. +---------+------+----------------------------------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx