Is there a TIMESTAMPDIFF()
equivalent for PostgreSQL?
I know I can subtract two timestamps to get a postgresql INTERVAL
. I just want the difference between the two timestamps in in hours represented by an INT.
I can do this in MySQL like this:
TIMESTAMPDIFF(HOUR, links.created, NOW())
I just need the difference between two timestamps in hours represented as an integer.
Solution works for me:
SELECT "links_link"."created",
"links_link"."title",
(EXTRACT(EPOCH FROM current_timestamp - "links_link"."created")/3600)::Integer AS "age"
FROM "links_link"
The first things popping up
EXTRACT(EPOCH FROM current_timestamp-somedate)/3600
May not be pretty, but unblocks the road. Could be prettier if division of interval by interval was defined.
Edit: if you want it greater than zero either use abs or greatest(...,0). Whichever suits your intention.
Edit++: the reason why I didn't use age
is that age
with a single argument, to quote the documentation: Subtract from current_date (at midnight). Meaning you don't get an accurate "age" unless running at midnight. Right now it's almost 1am here:
select age(current_timestamp);
age
------------------
-00:52:40.826309
(1 row)