《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列三：时间类型-阿里云开发者社区

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

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

日期时间类型

1、日期时间类型

1.1、Date类型

1.2、Timestamp(p)类型

1.3、Timestamp(p) with time zone类型

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

2、Interval类型

2.1、Interval year(p) to month类型

2.2、Interval day(p) to second类型

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

日期时间类型

1、日期时间类型

1.1、Date类型

1.2、Timestamp(p)类型

1.3、Timestamp(p) with time zone类型

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

2、Interval类型

2.1、Interval year(p) to month类型

2.2、Interval day(p) to second类型

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像