五月 20, 2023
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、使用 CREATE OPERATOR 语句定义操作符：
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=# select oracle.sysdate() , now();
sysdate | now
---------------------+-------------------------------
2017-11-08 18:07:16 | 2017-11-08 18:07:16.421318+08
(1 row)