PostgreSQL Oracle兼容性之 时间相减得到NUMBER - timestamp-timestamp=numeric not interval
背景
Oracle 时间相减，得到的是一个浮点值N，代表N天。
PostgreSQL 时间相减，得到的是一个时间间隔类型，但是可以转换为一个浮点值。
Oracle 例子
SQL> select sysdate - to_date('2017-01-01','yyyy-mm-dd') from dual;
SYSDATE-TO_DATE('2017-01-01','YYYY-MM-DD')
------------------------------------------
501.794444
PostgreSQL 例子
1、原生时间相减返回的是interval
postgres=# select now() - to_timestamp('2017-01-01','yyyy-mm-dd');
?column?
--------------------------
501 days 19:01:15.950408
(1 row)
2、采用extract可以将interval转换为秒
postgres=# select extract(epoch from now()-to_timestamp('2017-01-01','yyyy-mm-dd'));
date_part
----------------
43354846.07834
(1 row)
3、除以86400就得到天
postgres=# select 43354846/86400;
?column?
----------------------
501.7921990740740741
(1 row)
为了方便使用，可以定义个函数
create or replace function ts_ts(timestamp, timestamp) returns float8 as $$
select extract(epoch from $1-$2)/86400;
$$ language sql strict immutable;
使用函数相减即可得到天为单位的的浮点数
postgres=# select ts_ts(now(),to_timestamp('2017-01-01','yyyy-mm-hh'));
ts_ts
------------------
501.755990025012
(1 row)
PG 内置的减号操作符
可以看到原生的时间相减得到的就是interval类型。
postgres=# \do -
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Description
------------+------+-----------------------------+-----------------------------+-----------------------------+-------------------------------------
pg_catalog | - | abstime | reltime | abstime | subtract
pg_catalog | - | aclitem[] | aclitem | aclitem[] | remove ACL item
pg_catalog | - | anyrange | anyrange | anyrange | range difference
pg_catalog | - | bigint | bigint | bigint | subtract
pg_catalog | - | bigint | integer | bigint | subtract
pg_catalog | - | bigint | smallint | bigint | subtract
pg_catalog | - | box | point | box | subtract point from box (translate)
pg_catalog | - | circle | point | circle | subtract
pg_catalog | - | date | date | integer | subtract
pg_catalog | - | date | integer | date | subtract
pg_catalog | - | date | interval | timestamp without time zone | subtract
pg_catalog | - | double precision | double precision | double precision | subtract
pg_catalog | - | double precision | real | double precision | subtract
pg_catalog | - | inet | bigint | inet | subtract
pg_catalog | - | inet | inet | bigint | subtract
pg_catalog | - | integer | bigint | bigint | subtract
pg_catalog | - | integer | integer | integer | subtract
pg_catalog | - | integer | smallint | integer | subtract
pg_catalog | - | interval | interval | interval | subtract
pg_catalog | - | jsonb | integer | jsonb | delete array element
pg_catalog | - | jsonb | text | jsonb | delete object field
pg_catalog | - | jsonb | text[] | jsonb | delete object fields
pg_catalog | - | money | money | money | subtract
pg_catalog | - | numeric | numeric | numeric | subtract
pg_catalog | - | path | point | path | subtract (translate path)
pg_catalog | - | pg_lsn | pg_lsn | numeric | minus
pg_catalog | - | point | point | point | subtract points (translate)
pg_catalog | - | real | double precision | double precision | subtract
pg_catalog | - | real | real | real | subtract
pg_catalog | - | smallint | bigint | bigint | subtract
pg_catalog | - | smallint | integer | integer | subtract
pg_catalog | - | smallint | smallint | smallint | subtract
pg_catalog | - | timestamp without time zone | integer | timestamp without time zone | subtract integer from timestamp
pg_catalog | - | timestamp without time zone | interval | timestamp without time zone | subtract
pg_catalog | - | timestamp without time zone | numeric | timestamp without time zone | subtract numeric from timestamp
pg_catalog | - | timestamp without time zone | timestamp without time zone | interval | subtract
pg_catalog | - | timestamp with time zone | interval | timestamp with time zone | subtract
pg_catalog | - | timestamp with time zone | timestamp with time zone | interval | subtract
pg_catalog | - | time without time zone | interval | time without time zone | subtract
pg_catalog | - | time without time zone | time without time zone | interval | subtract
pg_catalog | - | time with time zone | interval | time with time zone | subtract
pg_catalog | - | | bigint | bigint | negate
pg_catalog | - | | double precision | double precision | negate
pg_catalog | - | | integer | integer | negate
pg_catalog | - | | interval | interval | negate
pg_catalog | - | | numeric | numeric | negate
pg_catalog | - | | real | real | negate
pg_catalog | - | | smallint | smallint | negate
参考
《PostgreSQL Oracle 兼容性之 - round interval》
《PostgreSQL Oracle 兼容性 之 NUMTODSINTERVAL》
《PostgreSQL Oracle 兼容性之 - timestamp + numeric》