digoal
2017-10-24
PostgreSQL , Oracle , 兼容性 , timestamp , numeric , float8 , orafce
Oracle里面支持时间戳与数字的加减，数字默认单位为天。
PostgreSQL 支持时间戳与interval类型进行加减。日期支持与整型做加减。
为了兼容Oracle（时间戳与数字加减），我们可以复写操作符来实现时间戳与数字的加减。
1、自定义几个函数，用于时间戳与数字的加减。
postgres=# create or replace function timestamp_add_num(timestamp, float8) returns timestamp as $$
select $1 + ($2||' day')::interval;
$$ language sql strict immutable;
CREATE FUNCTION
postgres=# create or replace function timestamptz_add_num(timestamptz, float8) returns timestamptz as $$
select $1 + ($2||' day')::interval;
$$ language sql strict immutable;
CREATE FUNCTION
postgres=# create or replace function num_add_timestamp(float8, timestamp) returns timestamp as $$
select $2 + ($1||' day')::interval;
$$ language sql strict immutable;
CREATE FUNCTION
postgres=# create or replace function num_add_timestamptz(float8, timestamptz) returns timestamptz as $$
select $2 + ($1||' day')::interval;
$$ language sql strict immutable;
CREATE FUNCTION
postgres=# create or replace function timestamp_min_num(timestamp, float8) returns timestamp as $$
select $1 - ($2||' day')::interval;
$$ language sql strict immutable;
CREATE FUNCTION
postgres=# create or replace function timestamptz_min_num(timestamptz, float8) returns timestamptz as $$
select $1 - ($2||' day')::interval;
$$ language sql strict immutable;
CREATE FUNCTION
2、复写操作符
postgres=# create operator + (procedure = timestamp_add_num, leftarg=timestamp, rightarg=float8);
CREATE OPERATOR
postgres=# create operator + (procedure = timestamptz_add_num, leftarg=timestamptz, rightarg=float8);
CREATE OPERATOR
postgres=# create operator + (procedure = num_add_timestamp, leftarg=float8, rightarg=timestamp);
CREATE OPERATOR
postgres=# create operator + (procedure = num_add_timestamptz, leftarg=float8, rightarg=timestamptz);
CREATE OPERATOR
postgres=# create operator - (procedure = timestamp_min_num, leftarg=timestamp, rightarg=float8);
CREATE OPERATOR
postgres=# create operator - (procedure = timestamptz_min_num, leftarg=timestamptz, rightarg=float8);
CREATE OPERATOR
3、验证测试
postgres=# select now()+1;
?column?
-------------------------------
2017-10-25 20:03:39.256659+08
(1 row)
postgres=# select now()+1.1;
?column?
-------------------------------
2017-10-25 22:27:40.925673+08
(1 row)
postgres=# select now()-1.1;
?column?
-------------------------------
2017-10-23 18:35:04.419078+08
(1 row)
postgres=# select 1.1+now();
?column?
-------------------------------
2017-10-25 23:23:08.842953+08
(1 row)
postgres=# select 1.1+now()::timestamp;
?column?
----------------------------
2017-10-25 23:23:13.318669
(1 row)
orafce是PostgreSQL社区推出的一个Oracle兼容包，包含了大量Oracle兼容函数，package。
create extension orafce;
实现oracle.date类型与数值的加减
postgres=# create or replace function sysdate_add_num(oracle.date, float8) returns timestamp as $$
select $1 + ($2||' day')::interval;
$$ language sql strict immutable;
CREATE FUNCTION
postgres=# create or replace function num_add_sysdate(float8, oracle.date) returns timestamp as $$
select $2 + ($1||' day')::interval;
$$ language sql strict immutable;
CREATE FUNCTION
postgres=# create or replace function sysdate_min_num(oracle.date, float8) returns timestamp as $$
select $1 - ($2||' day')::interval;
$$ language sql strict immutable;
CREATE FUNCTION
postgres=# create operator + (procedure = sysdate_add_num, leftarg=oracle.date, rightarg=float8);
CREATE OPERATOR
postgres=# create operator + (procedure = num_add_sysdate, leftarg=float8, rightarg=oracle.date);
CREATE OPERATOR
postgres=# create operator - (procedure = sysdate_min_num, leftarg=oracle.date, rightarg=float8);
CREATE OPERATOR
例子
postgres=# SELECT ORACLE.SYSDATE() , ORACLE.SYSDATE() + 1/12.0 FROM dual;
sysdate | ?column?
---------------------+---------------------
2017-11-08 09:45:04 | 2017-11-08 11:45:04
(1 row)
postgres=# SELECT ORACLE.SYSDATE() , ORACLE.SYSDATE() + 1/12.0::float8 FROM dual;
sysdate | ?column?
---------------------+---------------------
2017-11-08 09:45:15 | 2017-11-08 11:45:15
(1 row)
postgres=# SELECT ORACLE.SYSDATE() , ORACLE.SYSDATE() + 1/12.0 FROM dual;
sysdate | ?column?
---------------------+---------------------
2017-11-08 09:45:21 | 2017-11-08 11:45:21
(1 row)
postgres=# SELECT ORACLE.SYSDATE() , ORACLE.SYSDATE() - 1/12.0 FROM dual;
sysdate | ?column?
---------------------+---------------------
2017-11-08 09:45:24 | 2017-11-08 07:45:24
(1 row)
postgres=# SELECT ORACLE.SYSDATE() , 1/12.0 + oracle.sysdate() FROM dual;
sysdate | ?column?
---------------------+---------------------
2017-11-08 09:45:34 | 2017-11-08 11:45:34
(1 row)
postgres=# SELECT ORACLE.SYSDATE() , 1/24.0 + oracle.sysdate() FROM dual;
sysdate | ?column?
---------------------+---------------------
2017-11-08 09:46:19 | 2017-11-08 10:46:19
(1 row)
postgres=# alter role all set orafce.timezone=-8;
ALTER ROLE
postgres=# show timezone;
TimeZone
----------
PRC
(1 row)
postgres=# \q
postgres@iZbp13nu0s9j3x3op4zpd4Z-> psql
psql (10.0)
Type "help" for help.
postgres=# select oracle.sysdate() , now();
sysdate | now
---------------------+-------------------------------
2017-11-08 18:07:16 | 2017-11-08 18:07:16.421318+08
(1 row)
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
