Timestamp Difference In Hours for PostgreSQL

Question

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" 

Answer 1

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) 

Answer 2

Get fields where a timestamp is greater than date in postgresql:

SELECT * from yourtable  WHERE your_timestamp_field > to_date('05 Dec 2000', 'DD Mon YYYY'); 

Subtract minutes from timestamp in postgresql:

SELECT * from yourtable  WHERE your_timestamp_field > current_timestamp - interval '5 minutes' 

Subtract hours from timestamp in postgresql:

SELECT * from yourtable  WHERE your_timestamp_field > current_timestamp - interval '5 hours'