PostgreSQL Oracle兼容性之 时间相减得到NUMBER - timestamp-timestamp=numeric not interval

作者

digoal

日期

2018-05-17

标签

PostgreSQL , Oracle , 兼容性 , timestamp , interval , 时间相减 , numeric

背景

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》

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.