《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列三：时间类型

2021-07-04 00:25:29

PostgreSQL是世界上功能最强大的开源数据库，在国内得到了越来越多机构和开发者的青睐和应用。随着PostgreSQL的应用越来越广泛，Oracle向PostgreSQL数据库的数据迁移需求也越来越多。数据库之间数据迁移的时候，首先遇到的，并且也是最重要的，就是数据类型之间的转换。下面根据自己的理解和测试，写了一些数据类型之间的差异以及迁移时的注意事项的文章，不足之处，尚请多多指教。

日期时间类型

Oracle日期时间类型有两类，一类是日期时间类型，包括Date， Timestamp with time zone， Timestamp with local time zone。另一类是Interval类型。主要有Interval year to month 和Interval day to second两种。PostgreSQL也有类似的两类。其中的日期时间类型包括Timestamp with time zone， Timestamp without time zone， Date，Time with time zone , Time without time zone五种。Interval类型就是Interval。它们之间的对应关系是什么呢?

1、日期时间类型

1.1、Date类型

Oracle的Date类型包括年、月、日、时、分、秒六个字段，时间跨度是公元前4712年1月1日~公元9999年12月31日。PostgreSQL中，可以使用Timestamp(0) without time zone来对应，时间跨度从公元前4713年~公元294276年。
所有Oracle的日期时间类型，时间跨度都是公元前4712年1月1日~公元9999年12月31日。而PostgreSQL的时间跨度远远超过Oracle的时间跨度。PostgreSQL中Timestamp的时间跨度远大于Oracle的，而Date类型的时间跨度比Timestamp的更大。所以在时间的极值方面不存在问题。

注意：PostgreSQL中的Date类型仅仅包含日期，不包括时、分、秒信息。因此不能使用Date类型来对应Oracle的Date类型。

Oracle Date

SQL> create table o_test(value date);

表已创建。

SQL> insert into o_test values(to_date('-4712-01-01 00:30:45', 'syyyy-mm-dd hh24:mi:ss'));

已创建 1 行。

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:s
s') - interval  '1' day)

    *
第 1 行出现错误:
ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0

SQL> insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:mi:ss'));

已创建 1 行。

SQL> insert into o_test values(to_date('9999-12-31 12:30:45', 'yyyy-mm-dd hh24:m
i: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)

  *
第 1 行出现错误:
ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0

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

TO_CHAR(VALUE,'SYYYY
--------------------
-4712/01/01 00:30:45
 9999/12/31 12:30:45

PostgreSQL Timestamp(0) without time zone

postgres=# create table p_test1(value timestamp(0)  without time zone);
CREATE TABLE
postgres=# insert into p_test1 values('4712-01-01 00:30:45BC');
INSERT 0 1
postgres=# insert into p_test1 values(to_timestamp('4712-01-01 00:30:45BC','yyyy-mm-dd hh24:mi:ssBC') - interval '1 day');
INSERT 0 1
postgres=# insert into p_test1 values('9999-12-31 23:59:59');
INSERT 0 1
postgres=# insert into p_test1 values(to_timestamp('9999-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss') + interval '1 day');
INSERT 0 1
postgres=# select * from p_test1;
         value
------------------------
 4712-01-01 00:30:45 BC
 4713-12-31 00:30:45 BC
 9999-12-31 23:59:59
 10000-01-01 23:59:59
(4 行记录)

1.2、Timestamp(p)类型

Oracle的Timestamp包含年、月、日、时、分、秒、毫秒。其中最后的毫秒的精度最大为9位，默认为6位。基本上等同于PostgresQL的Timestamp without time zone。后者同样包含年、月、日、时、分、秒、毫秒。最后的毫秒精度最大为6位，默认为6位。精度方面，PostgreSQL比Oracle稍差。但是实际应用中，毫秒的6位精度已经足够。

Oracle数据中的毫秒数如果小于精度，会在有效数据后自动以0补足位数。PostgreSQL的毫秒数如果小于精度，不会在末尾补0。

Oracle Timestamp 默认精度

SQL> create table o_test(value timestamp);

表已创建。

SQL> insert into o_test values(to_timestamp('2012-12-31 12:30:50.123456', 'yyyy-
mm-dd hh24:mi:ss.ff6'));

已创建 1 行。

SQL> insert into o_test values(to_timestamp('2012-12-31 12:30:50.123456789', 'yy
yy-mm-dd hh24:mi:ss.ff9'));

已创建 1 行。

SQL> insert into o_test values(to_timestamp('2012-12-31 12:30:50.123', 'yyyy-mm-
dd hh24:mi:ss.ff'));

已创建 1 行。

SQL> select * from o_test;

VALUE
---------------------------------------------------------------------------
31-12月-12 12.30.50.123456 下午
31-12月-12 12.30.50.123457 下午
31-12月-12 12.30.50.123000 下午

PostgreSQL Timestamp without time zone 默认精度

postgres=# create table p_test1(value timestamp without time zone);
CREATE TABLE
postgres=# insert into p_test1 values('2012-12-31 12:30:45.123456');
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:45.123456789');
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:45.123');
INSERT 0 1
postgres=# select * from p_test1;
           value
----------------------------
 2012-12-31 12:30:45.123456
 2012-12-31 12:30:45.123457
 2012-12-31 12:30:45.123
(3 行记录)

Oracle Timestamp 指定精度

SQL> create table o_test1(value timestamp(9));

表已创建。

SQL> insert into o_test1 values(to_timestamp('2012-12-31 12:30:50.123456789', 'y
yyy-mm-dd hh24:mi:ss.ff9'));

已创建 1 行。

SQL> insert into o_test1 values(to_timestamp('2012-12-31 12:30:50.123', 'yyyy-mm
-dd hh24:mi:ss.ff'));

已创建 1 行。

SQL> select * from o_test1;

VALUE
---------------------------------------------------------------------------
31-12月-12 12.30.50.123456789 下午
31-12月-12 12.30.50.123000000 下午

PostgreSQL Timestamp without time zone 指定精度

postgres=# create table p_test2(value timestamp(3) without time zone);
CREATE TABLE
postgres=# insert into p_test2 values('2012-12-31 12:30:45.123456789');
INSERT 0 1
postgres=# insert into p_test2 values('2012-12-31 12:30:45.123');
INSERT 0 1
postgres=# insert into p_test2 values('2012-12-31 12:30:45.1');
INSERT 0 1
postgres=# select * from p_test2;
          value
-------------------------
 2012-12-31 12:30:45.123
 2012-12-31 12:30:45.123
 2012-12-31 12:30:45.1
(3 行记录)

1.3、Timestamp(p) with time zone类型

Oracle的Timestamp(p) with time zone类型，是在数据库的字段里面保存本字段相关的时区信息。功能方面基本上等同于PostgreSQL的Timestamp(p) with time zone。不同之处在于：Oracle会在数据库字段中存储数据所在的时区，而PostgreSQL却是把数据的时区自动转换成为数据库的时区(初始值由Postgresql.conf中的timezone定义，可以使用set timezone来进行更改)。

数据精度方面，和 1.2、Timestamp(p)类型 相同。

Oracle Timestamp with time zone

SQL> create table o_test( value timestamp with time zone);

表已创建。

SQL> insert into o_test values(systimestamp);

已创建 1 行。

SQL> insert into o_test values(to_timestamp_tz('2012-12-31 12:30:50.123456 +10:0
0', 'yyyy-mm-dd hh24:mi:ss.ff tzh:tzm'));

已创建 1 行。

SQL> select * from o_test;

VALUE
---------------------------------------------------------------------------
30-6月 -16 02.16.47.836000 下午 +08:00
31-12月-12 12.30.50.123456 下午 +10:00

PostgreSQL Timestamp(p) with time zone

postgres=# create table p_test1(value timestamp with time zone);
CREATE TABLE
postgres=# insert into p_test1 values(current_timestamp);
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:50.123456+10');
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:50.123456 PST');
INSERT 0 1
postgres=# select * from p_test1;
             value
-------------------------------
 2016-06-30 15:35:18.345153+08
 2012-12-31 10:30:50.123456+08
 2013-01-01 04:30:50.123456+08
(3 行记录)

1.4、Timestamp(p) with local time zone类型

Oracle的Timestamp(p) with local time zone类型，是在数据库的字段中不保存本字段的时区信息。而是使用数据库的time zone信息。当将数据写入数据库的时候，会自动将数据转换为数据库的时区写入。当取得数据的时候，会将数据转化为用户Session所在的时区进行显示。这种数据类型也对应于PostgreSQL的Timestamp with time zone。

Oracle Tiimestamp with local time zone

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL>  create table o_test( value timestamp with local time zone);

表已创建。

SQL> insert into o_test values(systimestamp);

已创建 1 行。

SQL> insert into o_test values(to_timestamp_tz('2012-12-31 12:30:50.123456 +10:0
0', 'yyyy-mm-dd hh24:mi:ss:ff tzh:tzm'));

已创建 1 行。

SQL> select * from o_test;

VALUE
---------------------------------------------------------------------------
01-7月 -16 09.49.20.860000 上午
31-12月-12 10.30.50.123456 上午

SQL> alter session set TIME_ZONE='-5:00';

会话已更改。

SQL> select * from o_test;

VALUE
---------------------------------------------------------------------------
30-6月 -16 08.49.20.860000 下午
30-12月-12 09.30.50.123456 下午

PostgreSQL Timestamp(p) with time zone

postgres=# show timezone;
    TimeZone
----------------
 Asia/Hong_Kong
(1 行记录)

postgres=# create table p_test1(value timestamp with time zone);
CREATE TABLE
postgres=# insert into p_test1 values(current_timestamp);
INSERT 0 1
postgres=# insert into p_test1 values('2012-12-31 12:30:50.123456 +10:00');
INSERT 0 1
postgres=# select * from p_test1;
             value
-------------------------------
 2016-07-01 10:09:05.958953+08
 2012-12-31 10:30:50.123456+08
(2 行记录)

postgres=# set timezone to '-5';
SET
postgres=# select * from p_test1;
             value
-------------------------------
 2016-06-30 21:09:05.958953-05
 2012-12-30 21:30:50.123456-05
(2 行记录)

2、Interval类型

Oracle的Interval 类型表示时间的间隔。分为Interval year(p) to month 和 Interval day(p) to second两类。分别对应间隔为年月和间隔为日、时、分、秒的情况。PostgreSQL的数据类型中，也有Interval 数据类型。

2.1、Interval year(p) to month类型

Oracle的Interval year(p) to month类型表示年月的时间间隔。year, month一起使用的时候，写法类似于Interval '12-11' year to month。其中的年份根据精度p来决定，p值为1~4，默认为2。月份只能是0~11之间的值。其实year, month都可以单独使用。单独使用时，年份同样是根据精度p来决定。PostgreSQL的Interval中，有类似的用法。而且用法比Oracle还灵活。

Oracle Interval year(p) to month

SQL> create table o_test(value interval year to month);

表已创建。

SQL> insert into  o_test values(interval '100-5' year(3) to month);
insert into  o_test values(interval '100-5' year(3) to month)
                           *
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小

SQL> insert into  o_test values(interval '10-5' year to month);

已创建 1 行。

SQL> insert into o_test values(interval '10-12' year to month);
insert into o_test values(interval '10-12' year to month)
                                   *
第 1 行出现错误:
ORA-01843: 无效的月份

SQL> insert into  o_test values(interval '30' year );

已创建 1 行。

SQL> insert into  o_test values(interval '1199' month);

已创建 1 行。

SQL> select * from o_test;

VALUE
---------------------------------------------------------------------------
+10-05
+30-00
+99-11

PostgreSQL Interval

postgres=# create table p_test1(value interval);
CREATE TABLE
postgres=# insert into p_test1 values('100-5');
INSERT 0 1
postgres=# insert into p_test1 values('90-12');
错误:  间隔字段超出范围: "90-12"
第1行insert into p_test1 values('90-12');
                                ^
postgres=# insert into p_test1 values('90-11');
INSERT 0 1
postgres=# insert into p_test1 values('90-0');
INSERT 0 1
postgres=# insert into p_test1 values('10000 year');
INSERT 0 1
postgres=# insert into p_test1 values('12000 month');
INSERT 0 1
postgres=# insert into p_test1 values('1000 year 12000 month');
INSERT 0 1
postgres=# select * from p_test1;
      value
------------------
 100 years 5 mons
 90 years 11 mons
 90 years
 10000 years
 1000 years
 2000 years
(6 行记录)

2.2、Interval day(p) to second类型

Oracle的Interval day(p) to second类型表示日、时、分、秒的时间间隔。一起使用的时候，写法类似于Interval '12 5:6:7.8' day to second。其中的日期由精度p来决定，p值为0~9，默认为2。小时为0~23，分钟为0~59, 秒为0~59。其实日、时、分、秒也都可以单独使用。单独使用时，日期，时、分、秒的值都可以使用精度p来控制。
PostgreSQL的Interval中，也有类似的用法，而且用法比Oracle灵活的多。
Oracle Interval day(p) to second

SQL> create table o_test(value interval day(3) to second);

表已创建。

SQL> insert into o_test values('1000 1:23:45');
insert into o_test values('1000 1:23:45')
                          *
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小

SQL> insert into o_test values('999 1:23:45');

已创建 1 行。

SQL> insert into o_test values('100 24:50:45');
insert into o_test values('100 24:50:45')
                          *
第 1 行出现错误:
ORA-01850: 小时值必须介于 0 和 23 之间

SQL> insert into o_test values('100 23:60:45');
insert into o_test values('100 23:60:45')
                          *
第 1 行出现错误:
ORA-01851: 分钟值必须介于 0 和 59 之间

SQL> insert into o_test values('100 23:40:75');
insert into o_test values('100 23:40:75')
                          *
第 1 行出现错误:
ORA-01852: 秒值必须介于 0 和 59 之间

SQL> insert into o_test values(interval '2399' hour);

已创建 1 行。

SQL> insert into o_test values(interval '2400' hour);
insert into o_test values(interval '2400' hour)
                                   *
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小

SQL> insert into o_test values(interval '2400' hour(4));

已创建 1 行。

SQL> insert into o_test values(interval '800000' minute);
insert into o_test values(interval '800000' minute)
                                   *
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小

SQL> insert into o_test values(interval '800000' minute(6));

已创建 1 行。

SQL> insert into o_test values(interval '80000000' second);
insert into o_test values(interval '80000000' second)
                                   *
第 1 行出现错误:
ORA-01873: 间隔的前导精度太小

SQL> insert into o_test values(interval '80000000' second(8));

已创建 1 行。

SQL> select * from o_test;

VALUE
---------------------------------------------------------------------------
+999 01:23:45.000000
+099 23:00:00.000000
+100 00:00:00.000000
+555 13:20:00.000000
+925 22:13:20.000000

PostgreSQL Interval

postgres=# create table p_test1(value interval);
CREATE TABLE
postgres=# insert into p_test1 values('1000 1:23:45');
INSERT 0 1
postgres=# insert into p_test1 values('100 50:23:45');
INSERT 0 1
postgres=# insert into p_test1 values('100 50:60:45');
错误:  间隔字段超出范围: "100 50:60:45"
第1行insert into p_test1 values('100 50:60:45');
                                ^
postgres=# insert into p_test1 values('1000 day');
INSERT 0 1
postgres=# insert into p_test1 values('80000 hour');
INSERT 0 1
postgres=# insert into p_test1 values('8000000 minute');
INSERT 0 1
postgres=# insert into p_test1 values('800000000 second');
INSERT 0 1
postgres=# select * from p_test1;
       value
--------------------
 1000 days 01:23:45
 100 days 50:23:45
 1000 days
 80000:00:00
 133333:20:00
 222222:13:20
(6 行记录)

Oracle的日期时间类型向PostgreSQL的数据迁移过程相对来说简单一些。由于PostgreSQL的数据类型的极值超越Oracle，因此，数据迁移过程中，只要根据Oracle的数据精度，在PostgreSQL中选择正确的数据类型。并留意一下二者SQL写法的不同，应该就能够完整正确的迁移过来。

参考文档：

PostgreSQL 9.4.4 中文手册：日期/时间类型

       http://www.postgres.cn/docs/9.4/datatype-datetime.html

Database SQL Language Reference：Data Types

       http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i45441

码农公寓