395
技術社區[雲棲]
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) 最後更新：2017-10-29 00:04:01