Postregsql Date Difference on basis on seconds

Question

How can I find date difference between two dates in terms of seconds in PostgreSQL?

There is no function for giving date difference in terms of seconds like in SQL-Server:

DATE_DIFF(second, '2011-12-30 09:55:56', '2011-12-30 08:54:55')

Please help me to have this in PostgreSQL too

Answer 1

First, the dates need to be values of timestamp type (so append ::timestamp if you're just specifying them as string literals).

If you subtract two timestamps, the result is of interval type, which describes a duration of time (in hours, minutes, seconds etc.) You can use extract(epoch from interval_value) to convert the interval into an absolute number of seconds.

So, putting that all together:

select extract(epoch from ('2011-12-30 09:55:56'::timestamp - '2011-12-30 08:54:55'::timestamp));

Remember that the ::timestamp is only needed to convert the string literal to a timestamp: you don't need it if you're using the value of a timestamp column, for example.