标簽
PostgreSQL , Oracle , 相容性 , timestamp , interval , 時間相減 , numeric
https://github.com/digoal/blog/blob/master/201805/20180517_02.md#%E8%83%8C%E6%99%AF 背景
Oracle 時間相減，得到的是一個浮點值N，代表N天。
PostgreSQL 時間相減，得到的是一個時間間隔類型，但是可以轉換為一個浮點值。
https://github.com/digoal/blog/blob/master/201805/20180517_02.md#oracle-%E4%BE%8B%E5%AD%90 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
https://github.com/digoal/blog/blob/master/201805/20180517_02.md#postgresql-%E4%BE%8B%E5%AD%90 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)
https://github.com/digoal/blog/blob/master/201805/20180517_02.md#pg-%E5%86%85%E7%BD%AE%E7%9A%84%E5%87%8F%E5%8F%B7%E6%93%8D%E4%BD%9C%E7%AC%A6 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
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)