Menu
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
832
If you'd like to calculate the difference between the timestamps in seconds in PostgreSQL, subtract one date from the other (here: arrival - departure ) then extract EPOCH from the difference to get it in seconds.
In PostgreSQL, if you subtract one datetime value (TIMESTAMP, DATE or TIME data type) from another, you will get an INTERVAL value in the form ”ddd days hh:mi:ss”. So you can use DATE_PART function to extact the number of days, but it returns the number of full days between the dates.
If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals 24 * 60 * 60 = 86400 , or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute.
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.
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
