3.2.7. 常量
本章主要介绍统一SQL对Oracle数据库的伪列转换到其他数据库中存在的一些 差异点
;具体支持字面量边界可参考 统一SQL使用边界规范
3.2.7.1. LightDB-Oracle
3.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,所以目标数据库字面量值超过该精度会自动进行处理后截断,出现与源数据库数据不一致的情况。
3.2.7.2. PostgreSQL
3.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,所以目标数据库字面量值超过该精度会自动进行处理后截断,出现与源数据库数据不一致的情况。
3.2.7.3. Tdsql-Oracle
3.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,所以目标数据库字面量值超过该精度会自动进行处理后截断,出现与源数据库数据不一致的情况。
3.2.7.4. OceanBase-Oracle
3.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 |