Find the difference in seconds between two dates

Question

I have following query in oracle to get diff in seconds

SELECT TO_DATE('a', 'dd-mm-yyyy hh24:mi:ss') - TO_DATE(Q'b', 'dd-mm-yyyy hh24:mi:ss') 
FROM dual

i'm looking for equivalent query in postgres

https://stackoverflow.com/questions/13626887/postregsql-date-difference-on-basis-on-seconds — Jonas Metzler, May 02 '22 at 10:50
`TO_DATE('a', 'dd-mm-yyyy hh24:mi:ss')` will result in an error because `'a'` isn't a valid date string, same for `Q'b'` — a_horse_with_no_name, May 02 '22 at 10:54
In Oracle `date - date` returns the difference in (fractional) days, not in seconds — a_horse_with_no_name, May 02 '22 at 10:56

Rahul Tripathi · Answer 1 · 2022-05-02T11:00:33.563

0

You can do it like this:

SELECT EXTRACT(EPOCH FROM (timestamp1- timestamp2)) FROM dual

edited May 02 '22 at 11:00

answered May 02 '22 at 10:49

Rahul Tripathi

`extract()` only works on `date`, `timestamp` or `interval` values. However, `date - date` returns an integer. – a_horse_with_no_name May 02 '22 at 10:56
You can cast to timestamp easily with `somedate::timestamp`. – Krab May 02 '22 at 11:01
@a_horse_with_no_name Correct. Cast to 'timestamp' instead. – Rahul Tripathi May 02 '22 at 11:01

1 Answers1