KingbaseES 时间类型数据和oracle时间类型的区别

阅读原文时间：2023年07月08日阅读：3

Oracle日期时间类型有两类，一类是日期时间类型，包括Date， Timestamp with time zone， Timestamp with local time zone。另一类是Interval类型，主要有Interval year to month 和Interval day to second两种。

KingbaseES也有类似的两类。其中的日期时间类型包括Timestamp with time zone， Timestamp without time zone，Date，Time with time zone , Time without time zone五种。Interval类型就是Interval。

KingbaseES扩展了Date数据类型，在兼容oracle模式时date的值是“年月日时分秒”与oracle一致，在pg模式下则为“年月日”。

Date 类型：

Oracle的Date类型包括年、月、日、时、分、秒六个字段，时间跨度是公元前4712年1月1日~公元9999年12月31日。
KingbaseES在兼容oracle模式下可以直接使用date数据类型或者使用 Timestamp(0) without time zone 类型来对应。时间跨度从公元前4713年~公元294276年。

1、Oracle date

SQL> create table o_test(value date);
Table created.
SQL> insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24:mi:ss'));
1 row created.

SQL> insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24:mi:ss') - interval '1' day);
insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24:mi:ss') - interval '1' day) *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

SQL> insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:mi:ss'));
1 row created.
SQL> insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:mi:ss') + interval '1' day);
insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:mi:ss') + interval '1' day)
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

SQL> select to_char(value, 'syyyy/mm/dd hh24:mi:ss') from o_test;

TO_CHAR(VALUE,'SYYYY/MM/DDHH24:MI:SS')

-4712/01/01 00:30:45
9999/12/31 12:30:45

2、KingbaseES Date

KingbaseES date 类型实际就是 Timestamp(0) without time zone

test=# create table test1(value timestamp(0) without time zone, value1 date);
CREATE TABLE
test=# insert into test1 values('4712-01-01 00:30:45BC','4712-01-01 00:30:45BC');
INSERT 0 1
test=# insert into test1 values(to_timestamp('4712-01-01 00:30:45BC','yyyy-mm-dd hh24:mi:ssBC') - interval '1 day',to_timestamp('4712-01-01 00:30:45BC','yyyy-mm-dd hh24:mi:ssBC') - interval '1 day');
INSERT 0 1
test=# insert into test1 values('9999-12-31 23:59:59','9999-12-31 23:59:59');
INSERT 0 1
test=# insert into test1 values(to_timestamp('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss') + interval '1 day',to_timestamp('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss') + interval '1 day');
INSERT 0 1

test=# select * from test1;
value | value1
------------------------+------------------------
4712-01-01 00:30:45 BC | 4712-01-01 00:30:45 BC
4713-12-31 00:30:45 BC | 4713-12-31 00:30:45 BC
9999-12-31 23:59:59 | 9999-12-31 23:59:59
10000-01-01 23:59:59 | 10000-01-01 23:59:59
(4 行记录)

在Timestamp(p) 数据类型上两种数据库基本一致，除了毫秒精度上的区别。

Oracle 毫秒的精度最大为9位，默认为6位。数据中的毫秒数如果小于精度，会在有效数据后自动以0补足位数。
KingbaseES 毫秒精度最大为6位，默认为6位，当指定精度超过6位时表依然会创建但是会有警告提示并将精度自动修正为6位。在兼容oracle模式时会在末尾补0，pg模式不会补足。

1、Oracle Timestamp

SQL> create table o_test(value timestamp);

Table created.
SQL> insert into o_test values(to_timestamp('2021-10-1 12:30:50.123', 'yyyy-mm-dd hh24:mi:ss.ff'));
1 row created.
SQL> insert into o_test values(to_timestamp('2021-10-1 12:30:50.123456', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 row created.
SQL> insert into o_test values(to_timestamp('2021-10-1 12:30:50.123456789', 'yyyy-mm-dd hh24:mi:ss.ff9'));
1 row created.

SQL> select * from o_test;

VALUE

01-OCT-21 12.30.50.123000 PM
01-OCT-21 12.30.50.123456 PM
01-OCT-21 12.30.50.123457 PM

Oracle Timestamp 指定精度

SQL> create table o_test1(value timestamp(9));
Table created.
SQL> insert into o_test1 values(to_timestamp('2021-10-1 11:30:50.123456789', 'yyyy-mm-dd hh24:mi:ss.ff9'));
1 row created.
SQL> insert into o_test1 values(to_timestamp('2021-10-1 11:30:50.123', 'yyyy-mm-dd hh24:mi:ss.ff'));
1 row created.
SQL> select * from o_test1;

VALUE

01-OCT-21 11.30.50.123456789 AM
01-OCT-21 11.30.50.123000000 AM

2、KingbaseES Timestamp without time zone

test=# create table test1(value timestamp without time zone);
CREATE TABLE
test=# insert into test1 values('2021-10-1 12:30:45.123456');
INSERT 0 1
test=# insert into test1 values('2021-10-1 12:30:45.123456789');
INSERT 0 1
test=# insert into test1 values('2021-10-1 12:30:45.123');
INSERT 0 1
兼容oracle模式
test=# select * from test1;

value

2021-10-01 12:30:45.123456
2021-10-01 12:30:45.123457
2021-10-01 12:30:45.123000

兼容pg模式
test=# select * from test1;

value

2021-10-01 12:30:45.123456
2021-10-01 12:30:45.123457
2021-10-01 12:30:45.123

KingbaseES Timestamp without time zone_指定精度_

test=# create table test2(value timestamp(9) without time zone);
警告: 将TIMESTAMP(9)减少到最大允许值,6
第1行create table test2(value timestamp(9) without time zone);
^
警告: 将TIMESTAMP(9)减少到最大允许值,6
警告: 将TIMESTAMP(9)减少到最大允许值,6
CREATE TABLE

test=# create table test2(value timestamp(3) without time zone);
CREATE TABLE
test=# insert into test2 values('2021-10-1 12:30:45.123456789');
INSERT 0 1
test=# insert into test2 values('2021-10-1 12:30:45.123');
INSERT 0 1
test=# insert into test2 values('2021-10-1 12:30:45.1');
INSERT 0 1
test=# select * from test2;

value

2021-10-01 12:30:45.123
2021-10-01 12:30:45.123
2021-10-01 12:30:45.100

带时区的时间类型，两种数据库之间的区别: Oracle会保存输入的的时区，而KingbaseES是把数据自动转换成为数据库时区对应的时间 (时区值由kingbase.conf中的timezone参数设定，可以使用set timezone来进行更改)。在使用时需要注意这个变化。

1、Oracle

SQL> create table o_test( value timestamp with time zone);
Table created.
SQL> insert into o_test values(systimestamp);
1 row created.
SQL> insert into o_test values(to_timestamp_tz('2012-12-31 12:30:50.123456 +10:00', 'yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'));
1 row created.
SQL> select * from o_test;

VALUE

14-OCT-21 11.33.12.639156 AM +08:00
31-DEC-12 12.30.50.123456 PM +10:00

2、KingbaseES

test=# create table test3(value timestamp with time zone);
CREATE TABLE
test=# insert into test3 values(current_timestamp);
INSERT 0 1
test=# insert into test3 values('2021-12-31 12:30:50.123456+10');
INSERT 0 1
test=# insert into test3 values('2021-12-31 12:30:50.123456 PST');
INSERT 0 1
test=#
test=# select * from test3;

value

2021-10-14 11:31:10.820010+08
2021-12-31 10:30:50.123456+08
2022-01-01 04:30:50.123456+08

Oracle的Interval类型主要分为Interval year to month 和 Interval day to second两类。分别对应间隔为年月和间隔为日、时、分、秒的情况。

KingbaseES的interval``提供了比较丰富的扩展：interval[**_fields_**][(**_p_**)] ，interval类型有一个附加选项，它可以通过写下面之一的短语来限制存储的fields的集合：YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,YEAR TO MONTH,DAY TO HOUR,DAY TO MINUTE, DAY TO SECOND,HOUR TO MINUTE,HOUR TO SECOND,MINUTE TO SECOND

注意如果fields和p被指定，fields必须包括SECOND，因为精度只应用于秒。

1、Oracle

Interval year(p) to month类型

Oracle的Interval year(p) to month类型表示年月的时间间隔。year, month一起使用的时候，写法类似于Interval '12-11' year to month。其中的年份根据精度p来决定，p值为1~9，默认为2。月份只能是0~11之间的值。单独使用month时精度也是控制年份的。

SQL> create table test(value interval year(3) to month);
Table created.
SQL> insert into test values(interval '100-5' year to month);
insert into test values(interval '100-5' year to month) *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into test values(interval '100-5' year(3) to month);
1 row created.
SQL> insert into test values(interval '1299' month);
insert into test values(interval '1299' month)
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> insert into test values(interval '1299' month(3));
1 row created.

SQL> select * from test;

VALUE

+100-05
+108-03

2、KingbaseES

KingbaseES 可以直接使用interval类型的数据，使用上比较灵活。
test=# create table test4(value interval);
CREATE TABLE
test=# insert into test4 values('100-5');
INSERT 0 1
test=# insert into test4 values('90-12');
错误: 间隔字段超出范围: "90-12"
第1行insert into test4 values('90-12');
^
test=# insert into test4 values('90-11');
INSERT 0 1
test=# insert into test4 values('90-0');
INSERT 0 1
test=# insert into test4 values('10000 year');
INSERT 0 1
test=# insert into test4 values('12000 month');
INSERT 0 1
test=# insert into test4 values('1000 year 12000 month');
INSERT 0 1
test=# insert into test4 values('80000 hour');
INSERT 0 1
test=# select * from test4;