sql - 将 Oracle 日期算法转换为 PostgreSQL

Question

我有这个 Oracle 查询:

select sm.*
from MESSAGES sm
where sm.delivery_finished = 0
  and (sm.last_sent_date is null or (SYSDATE - sm.last_sent_date) * 24 * 60 * 60 > sm.delay)
  and sm.status = 'REQUEST_READY'
order by nvl(sm.last_sent_date, to_date('2000.01.01', 'yyyy-MM-dd'))

我如何为 PostgreSQL 重写这个查询？

我试过这个:

select sm.*
from MESSAGES sm
where sm.delivery_finished = 0
  and (sm.last_sent_date is null or (now() - sm.last_sent_date) * 24 * 60 * 60 > sm.delay)
  AND sm.status = 'REQUEST_READY'
order by COALESCE(sm.last_sent_date, to_date('2000.01.01', 'yyyy-MM-dd'))

但是在这条线上:

now() - sm.last_sent_date) * 24 * 60 * 60 > sm.delay

我得到错误:

[42883] ERROR: operator does not exist: interval > integer
Hint: An operator with the given name and types of arguments was not found. Perhaps you should add explicit type conversions. Position: 148

Answer 1

如果你想做时间减法，我会用EXTRACT功能。

EXTRACT(SECOND FROM now() - sm.last_sent_date)  * 24 * 60 * 60 > sm.delay

我们可以看到 now()- '2000.01.01'::timestamp 将返回 interval 而不是整数。

所以EXTRACT函数可以帮助从时间减法中得到数量

查询 1:

SELECT  now()- '2000.01.01'::timestamp,
        EXTRACT(SECOND FROM now() - '2000.01.01'::timestamp) 

Results :

|                                                ?column? | date_part |
|---------------------------------------------------------|-----------|
| 0 years 0 mons 6852 days 8 hours 47 mins 37.710379 secs | 37.710379 |