4.2.7. 常量

本章主要介绍统一SQL对Oracle数据库的伪列转换到其他数据库中存在的一些差异点；具体支持字面量边界可参考统一SQL使用边界规范

4.2.7.1. LightDB-Oracle

4.2.7.1.1. INTERVAL类型

INTERVAL类型用来指定一段时间。统一SQL支持两种类型的间隔字面量：INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND。

INTERVAL YEAR TO MONTH

语法

/*SQL 日期格式*/
INTERVAL '[+|-] years-months' YEAR [(precision)] TO MONTH

/*ISO 日期格式*/
INTERVAL 'P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]]' YEAR [(precision)] TO MONTH

描述

用来指定以年和月为单位的一段时间。

参数解释

参数	说明
years-months	years 表示年，取整数值，范围为 [0，178000000]。months 表示月，取整数值，范围为 [0 ， 11]。注意值中不允许有空格。
precision	表示 YEAR 元素的精度，默认值为 2，取值范围为 [0,9]
P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]]	ISO 日期格式，范围见下列，注意值中不允许有空格。

years 表示年数，取整数值，范围为 [0,178000000]。
months 表示月数，取整数值，范围为 [0,11]。
days 表示日数，取整数值，范围为 [0,366]。
hours 表示秒数，取整数值，范围为 [0,59]。
minutes 表示分钟数，取整数值，范围为 [0,59]。
seconds 表示秒数，取整数值，范围为 [0,59]。
frac_secs 表示秒的精度，取整数值，范围为 [0,999999999]。

示例

CREATE TABLE unisql_interval_ym_table (
    col1 INTERVAL YEAR(1) TO MONTH,
    col2 INTERVAL YEAR(2) TO MONTH,
    col3 INTERVAL YEAR(3) TO MONTH
);

-- 转换前Oracle SQL:
INSERT INTO unisql_interval_ym_table(col1,col2,col3) VALUES
(
    INTERVAL '2-3' YEAR TO MONTH,
    INTERVAL '-21-3' YEAR(2) TO MONTH,
    INTERVAL 'P3Y11M366DT23H59M59.1234S' YEAR(2) TO MONTH
);
SELECT col1,col2,col3 FROM unisql_interval_ym_table;
COL1|COL2 |COL3|
----+-----+----+
2-3 |-21-3|3-11|

-- 转换后LightDB-Oracle SQL:
INSERT INTO unisql_interval_ym_table (col1,col2,col3) VALUES (INTERVAL '2-3' YEAR TO MONTH,INTERVAL '-21-3' YEAR TO MONTH,INTERVAL 'P3Y11M366DT23H59M59.1234S' YEAR TO MONTH);
SELECT col1,col2,col3 FROM unisql_interval_ym_table;
col1          |col2             |col3           |
--------------+-----------------+---------------+
2 years 3 mons|-21 years -3 mons|3 years 11 mons|

INTERVAL DAY TO SECOND

语法

INTERVAL '[+ | -] days hours:minutes:seconds[.frac_secs]' DAY [(precision)] TO SECOND [(fractional_seconds_precision)]

描述

用来指定以天和具体时间为单位的一段时间。

参数解释

参数	说明
days hours：minutes：seconds[.frac_secs]	SQL日期格式，依次表示天数、时、分、秒。注意值中时、分、秒不允许有空格。
precision	表示 DAY 元素的精度，默认值为 2，取值范围为 [0,9]
fractional_seconds_precision	表示 SECOND 元素小数部分的精度，默认值为 6，取值范围为 [0,9]

days 表示天，取整数值，范围为 [0,999999999]。
hours 表示小时，取整数值，范围为 [0,23]。
minutes 表示分钟，取整数值，范围为 [0,59]。
seconds 表示秒，取整数值，范围为 [0,59]。

示例

CREATE TABLE unisql_interval_ds_table (
    col1 INTERVAL DAY(3) TO SECOND(9),
    col2 INTERVAL DAY(1) TO SECOND(9)
);
-- 转换前Oracle SQL:
INSERT INTO unisql_interval_ds_table(col1,col2)
VALUES
(
    INTERVAL '1 12:30:15.123456789' DAY(2) TO SECOND(9),
    INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6)
);
SELECT col1,col2 FROM unisql_interval_ds_table;
COL1                |COL2             |
--------------------+-----------------+
1 12:30:15.123456789|-4 5:45:30.987654|

-- 转换后LightDB-Oracle SQL:
INSERT INTO unisql_interval_ds_table (col1,col2) VALUES (INTERVAL '1 12:30:15.123456789' DAY TO SECOND(6),INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6));
SELECT col1,col2 FROM unisql_interval_ds_table;
col1                 |col2                    |
---------------------+------------------------+
1 day 12:30:15.123457|-4 days +05:45:30.987654|

警告

下面列举的是一些在使用间隔字面量过程中会遇到的部分疑问点，在使用数据库时，如果对数据完全一致要求较高，现有的函数处理能力不能满足业务需求，建议进行业务SQL改写或者对结果进行进一步处理

目标数据库对显示间隔字面量与源库Oracle不一致，但是语义保持一致，不考虑时区、数据库设置的影响；

精度、时间格式暂时不做范围限制，具体是否错误由执行层保证；

因为需求原因，时间间隔字面量只限制在插入表中值去使用；

Oracle的INTERVAL DAY TO SECOND字面量的SECOND 元素小数部分的精度，默认值为 6，取值范围为 [0,9]，但是目标库相关SECOND 元素小数部分的精度的最大值为6，所以目标数据库字面量值超过该精度会自动进行处理后截断，出现与源数据库数据不一致的情况。

4.2.7.2. PostgreSQL

4.2.7.2.1. INTERVAL类型

INTERVAL类型用来指定一段时间。统一SQL支持两种类型的间隔字面量：INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND。

INTERVAL YEAR TO MONTH

语法

/*SQL 日期格式*/
INTERVAL '[+|-] years-months' YEAR [(precision)] TO MONTH

/*ISO 日期格式*/
INTERVAL 'P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]]' YEAR [(precision)] TO MONTH

描述

用来指定以年和月为单位的一段时间。

参数解释

参数	说明
years-months	years 表示年，取整数值，范围为 [0，178000000]。months 表示月，取整数值，范围为 [0 ， 11]。注意值中不允许有空格。
precision	表示 YEAR 元素的精度，默认值为 2，取值范围为 [0,9]
P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]]	ISO 日期格式，范围见下列，注意值中不允许有空格。

years 表示年数，取整数值，范围为 [0,178000000]。
months 表示月数，取整数值，范围为 [0,11]。
days 表示日数，取整数值，范围为 [0,366]。
hours 表示秒数，取整数值，范围为 [0,59]。
minutes 表示分钟数，取整数值，范围为 [0,59]。
seconds 表示秒数，取整数值，范围为 [0,59]。
frac_secs 表示秒的精度，取整数值，范围为 [0,999999999]。

示例

CREATE TABLE unisql_interval_ym_table (
    col1 INTERVAL YEAR(1) TO MONTH,
    col2 INTERVAL YEAR(2) TO MONTH,
    col3 INTERVAL YEAR(3) TO MONTH
);

-- 转换前Oracle SQL:
INSERT INTO unisql_interval_ym_table(col1,col2,col3) VALUES
(
    INTERVAL '2-3' YEAR TO MONTH,
    INTERVAL '-21-3' YEAR(2) TO MONTH,
    INTERVAL 'P3Y11M366DT23H59M59.1234S' YEAR(2) TO MONTH
);
SELECT col1,col2,col3 FROM unisql_interval_ym_table;
COL1|COL2 |COL3|
----+-----+----+
2-3 |-21-3|3-11|

-- 转换后PostgreSQL SQL:
INSERT INTO unisql_interval_ym_table (col1,col2,col3) VALUES (INTERVAL '2-3' YEAR TO MONTH,INTERVAL '-21-3' YEAR TO MONTH,INTERVAL 'P3Y11M366DT23H59M59.1234S' YEAR TO MONTH);
SELECT col1,col2,col3 FROM unisql_interval_ym_table;
col1          |col2             |col3           |
--------------+-----------------+---------------+
2 years 3 mons|-21 years -3 mons|3 years 11 mons|

INTERVAL DAY TO SECOND

语法

INTERVAL '[+ | -] days hours:minutes:seconds[.frac_secs]' DAY [(precision)] TO SECOND [(fractional_seconds_precision)]

描述

用来指定以天和具体时间为单位的一段时间。

参数解释

参数	说明
days hours：minutes：seconds[.frac_secs]	SQL日期格式，依次表示天数、时、分、秒。注意值中时、分、秒不允许有空格。
precision	表示 DAY 元素的精度，默认值为 2，取值范围为 [0,9]
fractional_seconds_precision	表示 SECOND 元素小数部分的精度，默认值为 6，取值范围为 [0,9]

days 表示天，取整数值，范围为 [0,999999999]。
hours 表示小时，取整数值，范围为 [0,23]。
minutes 表示分钟，取整数值，范围为 [0,59]。
seconds 表示秒，取整数值，范围为 [0,59]。

示例

CREATE TABLE unisql_interval_ds_table (
    col1 INTERVAL DAY(3) TO SECOND(9),
    col2 INTERVAL DAY(1) TO SECOND(9)
);
-- 转换前Oracle SQL:
INSERT INTO unisql_interval_ds_table(col1,col2)
VALUES
(
    INTERVAL '1 12:30:15.123456789' DAY(2) TO SECOND(9),
    INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6)
);
SELECT col1,col2 FROM unisql_interval_ds_table;
COL1                |COL2             |
--------------------+-----------------+
1 12:30:15.123456789|-4 5:45:30.987654|

-- 转换后PostgreSQL SQL:
INSERT INTO unisql_interval_ds_table (col1,col2) VALUES (INTERVAL '1 12:30:15.123456789' DAY TO SECOND(6),INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6));
SELECT col1,col2 FROM unisql_interval_ds_table;
col1                 |col2                    |
---------------------+------------------------+
1 day 12:30:15.123457|-4 days +05:45:30.987654|

警告

下面列举的是一些在使用间隔字面量过程中会遇到的部分疑问点，在使用数据库时，如果对数据完全一致要求较高，现有的函数处理能力不能满足业务需求，建议进行业务SQL改写或者对结果进行进一步处理

目标数据库对显示间隔字面量与源库Oracle不一致，但是语义保持一致，不考虑时区、数据库设置的影响；

精度、时间格式暂时不做范围限制，具体是否错误由执行层保证；

因为需求原因，时间间隔字面量只限制在插入表中值去使用；

Oracle的INTERVAL DAY TO SECOND字面量的SECOND 元素小数部分的精度，默认值为 6，取值范围为 [0,9]，但是目标库相关SECOND 元素小数部分的精度的最大值为6，所以目标数据库字面量值超过该精度会自动进行处理后截断，出现与源数据库数据不一致的情况。

4.2.7.3. Tdsql-Oracle

4.2.7.3.1. INTERVAL类型

INTERVAL类型用来指定一段时间。统一SQL支持两种类型的间隔字面量：INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND。

INTERVAL YEAR TO MONTH

语法

/*SQL 日期格式*/
INTERVAL '[+|-] years-months' YEAR [(precision)] TO MONTH

/*ISO 日期格式*/
INTERVAL 'P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]]' YEAR [(precision)] TO MONTH

描述

用来指定以年和月为单位的一段时间。

参数解释

参数	说明
years-months	years 表示年，取整数值，范围为 [0，178000000]。months 表示月，取整数值，范围为 [0 ， 11]。注意值中不允许有空格。
precision	表示 YEAR 元素的精度，默认值为 2，取值范围为 [0,9]
P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]]	ISO 日期格式，范围见下列，注意值中不允许有空格。

years 表示年数，取整数值，范围为 [0,178000000]。
months 表示月数，取整数值，范围为 [0,11]。
days 表示日数，取整数值，范围为 [0,366]。
hours 表示秒数，取整数值，范围为 [0,59]。
minutes 表示分钟数，取整数值，范围为 [0,59]。
seconds 表示秒数，取整数值，范围为 [0,59]。
frac_secs 表示秒的精度，取整数值，范围为 [0,999999999]。

示例

CREATE TABLE unisql_interval_ym_table (
    col1 INTERVAL YEAR(1) TO MONTH,
    col2 INTERVAL YEAR(2) TO MONTH,
    col3 INTERVAL YEAR(3) TO MONTH
);

-- 转换前Oracle SQL:
INSERT INTO unisql_interval_ym_table(col1,col2,col3) VALUES
(
    INTERVAL '2-3' YEAR TO MONTH,
    INTERVAL '-21-3' YEAR(2) TO MONTH,
    INTERVAL 'P3Y11M366DT23H59M59.1234S' YEAR(2) TO MONTH
);
SELECT col1,col2,col3 FROM unisql_interval_ym_table;
COL1|COL2 |COL3|
----+-----+----+
2-3 |-21-3|3-11|

-- 转换后TDSQL-Oracle模式 SQL:
INSERT INTO unisql_interval_ym_table (col1,col2,col3) VALUES (INTERVAL '2-3' YEAR TO MONTH,INTERVAL '-21-3' YEAR TO MONTH,INTERVAL 'P3Y11M366DT23H59M59.1234S' YEAR TO MONTH);
SELECT col1,col2,col3 FROM unisql_interval_ym_table;
col1          |col2             |col3           |
--------------+-----------------+---------------+
2 years 3 mons|-21 years -3 mons|3 years 11 mons|

INTERVAL DAY TO SECOND

语法

INTERVAL '[+ | -] days hours:minutes:seconds[.frac_secs]' DAY [(precision)] TO SECOND [(fractional_seconds_precision)]

描述

用来指定以天和具体时间为单位的一段时间。

参数解释

参数	说明
days hours：minutes：seconds[.frac_secs]	SQL日期格式，依次表示天数、时、分、秒。注意值中时、分、秒不允许有空格。
precision	表示 DAY 元素的精度，默认值为 2，取值范围为 [0,9]
fractional_seconds_precision	表示 SECOND 元素小数部分的精度，默认值为 6，取值范围为 [0,9]

days 表示天，取整数值，范围为 [0,999999999]。
hours 表示小时，取整数值，范围为 [0,23]。
minutes 表示分钟，取整数值，范围为 [0,59]。
seconds 表示秒，取整数值，范围为 [0,59]。

示例

CREATE TABLE unisql_interval_ds_table (
    col1 INTERVAL DAY(3) TO SECOND(9),
    col2 INTERVAL DAY(1) TO SECOND(9)
);
-- 转换前Oracle SQL:
INSERT INTO unisql_interval_ds_table(col1,col2)
VALUES
(
    INTERVAL '1 12:30:15.123456789' DAY(2) TO SECOND(9),
    INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6)
);
SELECT col1,col2 FROM unisql_interval_ds_table;
COL1                |COL2             |
--------------------+-----------------+
1 12:30:15.123456789|-4 5:45:30.987654|

-- 转换后TDSQL-Oracle模式 SQL:
INSERT INTO unisql_interval_ds_table (col1,col2) VALUES (INTERVAL '1 12:30:15.123456789' DAY TO SECOND(6),INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6));
SELECT col1,col2 FROM unisql_interval_ds_table;
col1                 |col2                    |
---------------------+------------------------+
1 day 12:30:15.123457|-4 days +05:45:30.987654|

警告

下面列举的是一些在使用间隔字面量过程中会遇到的部分疑问点，在使用数据库时，如果对数据完全一致要求较高，现有的函数处理能力不能满足业务需求，建议进行业务SQL改写或者对结果进行进一步处理

目标数据库对显示间隔字面量与源库Oracle不一致，但是语义保持一致，不考虑时区、数据库设置的影响；

精度、时间格式暂时不做范围限制，具体是否错误由执行层保证；

因为需求原因，时间间隔字面量只限制在插入表中值去使用；

Oracle的INTERVAL DAY TO SECOND字面量的SECOND 元素小数部分的精度，默认值为 6，取值范围为 [0,9]，但是目标库相关SECOND 元素小数部分的精度的最大值为6，所以目标数据库字面量值超过该精度会自动进行处理后截断，出现与源数据库数据不一致的情况。

4.2.7.4. OceanBase-Oracle

4.2.7.4.1. INTERVAL类型

INTERVAL类型用来指定一段时间。统一SQL支持两种类型的间隔字面量：INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND。

INTERVAL YEAR TO MONTH

语法

INTERVAL '[+|-] years-months' YEAR [(precision)] TO MONTH

描述

用来指定以年和月为单位的一段时间。

参数解释

参数	说明
years-months	years 表示年，取整数值，范围为 [0，999999999]。months 表示月，取整数值，范围为 [0 ， 11]。注意值中不允许有空格。
precision	表示 YEAR 元素的精度，默认值为 2，取值范围为 [0,9]

示例

-- 转换前Oracle SQL:
select
    INTERVAL '2-3' YEAR(1) TO MONTH,
    INTERVAL '+19-11' YEAR TO MONTH,
    INTERVAL '-21-3' YEAR(2)TO MONTH,
    INTERVAL '123456789-11' YEAR(9) TO MONTH
from dual;
INTERVAL'2-3'YEAR(1)TOMONTH|INTERVAL'19-11'YEARTOMONTH|INTERVAL'-21-3'YEAR(2)TOMONTH|INTERVAL'123456789-11'YEAR(9)TOMONTH|
---------------------------+--------------------------+-----------------------------+------------------------------------+
2-3                        |19-11                     |-21-3                        |123456789-11                        |

-- 转换后OceanBase-Oracle SQL:
select
    INTERVAL '2-3' YEAR(1) TO MONTH,
    INTERVAL '+19-11' YEAR TO MONTH,
    INTERVAL '-21-3' YEAR(2)TO MONTH,
    INTERVAL '123456789-11' YEAR(9) TO MONTH
from dual;
INTERVAL'2-3'YEAR(1)TOMONTH|INTERVAL'+19-11'YEARTOMONTH|INTERVAL'-21-3'YEAR(2)TOMONTH|INTERVAL'123456789-11'YEAR(9)TOMONTH|
---------------------------+---------------------------+-----------------------------+------------------------------------+
2-3                        |19-11                      |-21-3                        |123456789-11                        |

INTERVAL DAY TO SECOND

语法

INTERVAL '[+ | -] days hours:minutes:seconds[.frac_secs]' DAY [(precision)] TO SECOND [(fractional_seconds_precision)]

描述

用来指定以天和具体时间为单位的一段时间。

参数解释

参数	说明
days hours：minutes：seconds[.frac_secs]	SQL日期格式，依次表示天数、时、分、秒。注意值中不允许有空格。
precision	表示 DAY 元素的精度，默认值为 2，取值范围为 [0,9]
fractional_seconds_precision	表示 SECOND 元素小数部分的精度，默认值为 6，取值范围为 [0,9]

days 表示天，取整数值，范围为 [0,999999999]。
hours 表示小时，取整数值，范围为 [0,23]。
minutes 表示分钟，取整数值，范围为 [0,59]。
seconds 表示秒，取整数值，范围为 [0,59]。

示例

-- 转换前Oracle SQL:
SELECT
    INTERVAL '+123456789 12:30:15.123456789' DAY(9) TO SECOND(9),
    INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6)
FROM dual;
INTERVAL'+12345678912:30:15.123456789'DAY(9)TOSECOND(9)|INTERVAL'-405:45:30.987654'DAYTOSECOND(6)|
-------------------------------------------------------+-----------------------------------------+
123456789 12:30:15.123456789                           |-4 5:45:30.987654                        |

-- 转换后OceanBase-Oracle SQL:
SELECT
    INTERVAL '+123456789 12:30:15.123456789' DAY(9) TO SECOND(9),
    INTERVAL '-4 05:45:30.987654' DAY TO SECOND(6)
FROM dual;
INTERVAL'+12345678912:30:15.123456789'DAY(9)TOSECOND(9)|INTERVAL'-405:45:30.987654'DAYTOSECOND(6)|
-------------------------------------------------------+-----------------------------------------+
123456789 12:30:15.123456789                           |-4 5:45:30.987654                        |