CODEWITHSUNDEEP
sqloracle-databasepostgresqldatetime
ip696
ip696

Reputation: 7104

Converting Oracle date arithmetic to PostgreSQL

I have this Oracle query:

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'))

How can I rewrite this query for PostgreSQL?

I tried this:

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'))

But on this line:

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

I get error:

[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

Upvotes: 3

Views: 203

Answers (2)

D-Shih
D-Shih

Reputation: 46249

If you want to do time subtraction, I would use EXTRACT function.

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

We can see now()- '2000.01.01'::timestamp will return interval not the interger.

so EXTRACT function can help get the amount from time subtraction

Query 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 |

Upvotes: 3

Dilyan Trayanov
Dilyan Trayanov

Reputation: 549

As the error says:

operator does not exist: interval> integer

You have to typecast explicitly one of the two arguments. In PostgreSQL you can use ::desired_type to cast the column.

I guess in your case you have to add ::integer after the closing bracket

Upvotes: 0

Related Questions