PostgreSQL Oracle 兼容性之 - timestamp 与 numeric 的运算

标签

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)