How to get difference between 2 dates in PostgreSQL with TIMESTAMPDIFF?

Question

I'm trying to get it as:

SELECT * FROM "Employee" WHERE TIMESTAMPDIFF('YEAR', "BirthDate", "HireDate");

but I get the next error:

ERROR:  function timestampdiff(unknown, timestamp without time zone, timestamp without time zone) does not exist
LINE 1: SELECT * FROM "Employee" WHERE TIMESTAMPDIFF('YEAR', "BirthD...
                                   ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I've found another way to get difference between 2 dates but in my instructions it says that I have to get it via TIMESTAMPDIFF. Can someone help me where to look and how to fix my error?

`"HireDate" - "BirthDate"` There is no `timestampdiff` in Postgres - whatever those instructions are, they are clearly for a different DBMS product. https://www.postgresql.org/docs/current/static/functions-datetime.html — , Oct 21 '18 at 17:34
@a_horse_with_no_name you mean to switch places? I've tried it also and I got the same error — J. Doe, Oct 21 '18 at 17:35

Zaynul Abadin Tuhin · Answer 1 · 2018-10-21T17:41:15.800

3

you can use minus operator

EXTRACT(DAY FROM (HireDate)-(BirthDate)) AS DateDifference

Example select date '2001-10-01' - date '2001-09-28'

DEMO IN FIDDLE

PostGrey Docs

edited Oct 21 '18 at 17:41

answered Oct 21 '18 at 17:35

Zaynul Abadin Tuhin

31,407
5
33
63

Interesting. This works with `-`, but it doesn't work with `age()`. – Gordon Linoff Oct 21 '18 at 17:42
with age , date_part('year', age("BirthDate", "HireDate")) – Vishantha Peiris Oct 21 '18 at 18:09
so how I understood there is no TIMESTAMPDIFF in PostgreSQL, right? By the way, thanks for the answer! – J. Doe Oct 21 '18 at 18:13

How to get difference between 2 dates in PostgreSQL with TIMESTAMPDIFF?

1 Answers1