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

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)类型

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

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

1、Oracle Timestamp

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 指定精度

?

1 2 3 4 5 6 7 8 9 10 11

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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指定精度

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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

三、Timestamp(p) with time zone

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

1、Oracle

?

1 2 3 4 5 6 7 8 9 10 11

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

四、interval

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时精度也是控制年份的。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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;        value        --------------------  100 years 5 mons  90 years 11 mons  90 years  10000 years  1000 years  2000 years  80000:00:00.000000