PostgreSQL Oracle 兼容性之 - timestamp + numeric

背景

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 兼容包扩展支持

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)  

orafce sysdate的时区对齐

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)

digoal’s 大量PostgreSQL文章入口