《卸甲笔记》-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;

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

【.NET 6】使用EF Core 访问Oracle+Mysql+PostgreSQL并进行简单增改操作与性能比较 唠嗑一下。都在说去O或者开源，但是对于数据库选型来说，很多人却存在着误区。例如，去O，狭义上讲，是去Oracle数据库。但是从广义上来说，是去Oracle公司产品或者具有漂亮国垄断地位和需要商业授权的数据库产品。
关于PostgreSQL数据库兼容Oracle数据库闪回查询的实现方案 注：关于在PostgreSQL上面实现Oracle数据库的闪回功能(闪回查询 闪回表 闪回删除…)的这个想法已经有很长时间了，但是鉴于本人的能力 精力和身体条件 迟迟没有完成。期间也有很多的小伙伴跟我一起研究过这个功能，但是最终都因为各种各样的问题 没有做下去。Oracle数据库闪回功能跨越版本较大，功能也比较强大 在PostgreSQL数据库上实现，需要对数据库内核有很深入的理解 两大数据库不同的底层原理也终将影响各自的实现策略，PostgreSQL标记删除就地插入的特点和基于事务快照行可见性的特性是我们可以开发PostgreSQL闪回查询的大前提。本文主要介绍 实现闪回查询的 一种实现方案