Postgresql兼容Oracle改造系列——时间减法

1 背景描述

Postgresql中时间减法运算结果都是interval，但是在Oracle中sysdate与date减法结果是numeric，差异会导致一些函数无法正确使用，比如 ceil 、 round等等，这里总结改造方法。

2 场景分析

Oracle中并不是所有的时间减法都是会等于浮点型，这里分析几种场景。

date - date：返回整型

Oracle

代码语言：javascript复制

select date '2020-02-01' - date '2020-01-01' from dual; 

31

Postgresql

代码语言：javascript复制

select date '2020-02-01' - date '2020-01-01' ; 

31

timestamp - timestamp：一致

Oracle

代码语言：javascript复制

select timestamp '2015-04-10 14:52:19.000' - timestamp '2014-03-11 13:55:29.000' 
from dual;

 000000395 00:56:50.000000000

Postgresql

代码语言：javascript复制

select timestamp '2015-04-10 14:52:19.000' - timestamp '2014-03-11 13:55:29.000';

395 days 00:56:50

timestamp - date：一致

Oracle

代码语言：javascript复制

select timestamp '2014-03-15 13:55:29.000' - date '2014-03-11' from dual; 

 000000004 13:55:29.000000000
```sql
`Postgresql`

select timestamp ‘2014-03-15 13:55:29.000’ - date ‘2014-03-11’;

4 days 13:55:29

代码语言：javascript复制

### sysdate - date：不一致
`Oracle`：返回浮点型，sysdate大约在2020-06-28 14:40，结果单位是天
```sql
select sysdate - date '2020-06-28' from dual; 

1.28373842592592592592592592592592592593

Postgresql：返回interval

代码语言：javascript复制

select CURRENT_TIMESTAMP - date '2020-06-28'; 

1 day 06:48:19.348028

sysdate - timestamp：一致

Oracle

代码语言：javascript复制

select sysdate - timestamp '2015-04-10 14:52:19.000' from dual; 

 000001906 15:57:05.000000000

Postgresql

代码语言：javascript复制

select CURRENT_TIMESTAMP - timestamp '2015-04-10 14:52:19.000'; 

1906 days 15:57:29.880652

3 改造方案

sysdate - date的结果的单位是天，允许出现小数，那么在Postgresql或PolarO中只需要把interval转化为天的小数即可。

在Postgresql中实现函数

代码语言：javascript复制

CREATE OR REPLACE FUNCTION time_between(TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) 
RETURNS FLOAT8 AS 
$m$  
  SELECT EXTRACT(EPOCH FROM $1-$2)/86400;  
$m$ LANGUAGE SQL STRICT IMMUTABLE; 


CREATE OR REPLACE FUNCTION sysdate_between(date) 
RETURNS FLOAT8 AS 
$m$  
  SELECT time_between(now(), $1);
$m$ LANGUAGE SQL STRICT IMMUTABLE; 

4 结果对比

Oracle

代码语言：javascript复制

select sysdate - date '2020-06-28' from dual; 

1.29969907407407407407407407407407407407

Postgresql

代码语言：javascript复制

-- 改写 select sysdate - date '2020-06-28' from dual; 
select sysdate_between(date '2020-06-28'); 

1.29990540226852

执行时间点有些许差异，所以结果不完全一致。

postgresql sql 云数据库postgresql oracle unix

0 人点赞