Converting Oracle date arithmetic to PostgreSQL

Question

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

D-Shih · Accepted Answer · 2018-10-05 08:49:05Z

3

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 |

edited Oct 5, 2018 at 8:49

answered Oct 5, 2018 at 8:35

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ip696 Over a year ago

yes it helped! what about order by nvl(sm.last_sent_date, to_date('2000.01.01', 'yyyy-MM-dd'))?

D-Shih Over a year ago

@ip696 You can try to use COALESCE, There is a typo in your order by COLEASCE(sm.last_sent_date, to_date('2000.01.01', 'yyyy-MM-dd')) currently

ip696 Over a year ago

[42883] ERROR: the coleasce function (timestamp without time zone, date) does not exist. Hint: The function with the given name and argument types was not found. Perhaps you should add explicit type conversions. Position: 231

D-Shih Over a year ago

Your COLEASCE is a typo .. use COALESCE instead of COLEASCE

ip696 Over a year ago

I write like this:

order by CASE sm.last_sent_date            WHEN null THEN to_date('2000.01.01', 'yyyy-MM-dd')            ELSE sm.last_sent_date     END

Is this a normal way?

|

Dilyan Trayanov · Accepted Answer · 2018-10-05 08:37:30Z

0

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

answered Oct 5, 2018 at 8:37

Dilyan Trayanov

5493 silver badges21 bronze badges

Collectives™ on Stack Overflow

Converting Oracle date arithmetic to PostgreSQL

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related