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

mingjie

发布于 2022-05-12 10:29:08

1.5K0

文章被收录于专栏：Postgresql源码分析Postgresql源码分析

1 背景描述

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

2 场景分析

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

date - date：返回整型

Oracle

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

31

Postgresql

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

31

timestamp - timestamp：一致

Oracle

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

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

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

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

1.28373842592592592592592592592592592593

Postgresql：返回interval

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

1 day 06:48:19.348028

sysdate - timestamp：一致

Oracle

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

+000001906 15:57:05.000000000

Postgresql

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

1906 days 15:57:29.880652

3 改造方案

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

在Postgresql中实现函数

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

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

1.29969907407407407407407407407407407407

Postgresql

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

1.29990540226852

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

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020-06-29，如有侵权请联系 cloudcommunity@tencent.com 删除

postgresql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度