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