Timestamp Difference In Hours for PostgreSQL

Question

I found exactly what I need in MySQL's TIMESTAMPDIFF() but I am having a hell of a time trying to find an equivablent with PostgreSQL. I know I can subtract the two timestamps to get an INTERVAL but I am having a hard time wrapping my head about what it means and specifically I just want the difference between the two timestamps in in hours represented by an INT. I accomplished this in MySQL by using:

TIMESTAMPDIFF(HOUR, links.created, NOW())

I am still learning PostgreSQL but so far I am really enjoying it. I have just hit a small roadblock with this. Thanks in advanced!

EDIT

These answers are close but I just need the difference between two timestamps in hours represented as an integer. Basically the only thing it should ever return is a 0 or a whole number > 0 representing the difference in hours.

EDIT 2

"2009-12-25 23:04:24.032883-05";"By The Book Brewing";"00:55:35.967117"
"2009-12-26 00:07:43.281076-05";"Sweetwater Brewery... Really bad site...";"-00:07:43.281076"
"2009-12-26 00:08:46.234226-05";"Best Domestic, Widely Available Beer";"-00:08:46.234226"
"2009-12-26 15:01:49.930634-05";"Radiohead";"-15:01:49.930634"

Is my output with a query of

SELECT "links_link"."created", "links_link"."title", AGE("links_link"."created") AS "age" FROM "links_link"

Why do I get both positive and negative numbers when age should be the difference between the given timestamp and a current timestamp?

EDIT 3

Haha, why would anyone put up with me? The different between two timestamps in hours. I'll give a more precise example. A rows creation timestamp and the current timestamp. The value should never be negative (unless row was inserted into the future) and if it was submitted less than an hour ago, the value should be 0. If something was submitted more exactly two days and 1 hour ago the returned value should be 49 (24 +24 + 1).

SOLUTION

After everybody's help, this is what I came up with:

SELECT "links_link"."created", "links_link"."title", (EXTRACT(EPOCH FROM current_timestamp - "links_link"."created")/3600)::Integer AS "age" FROM "links_link"

Time for the hard parts now :)

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

You can use the "extract" or "date_part" functions on intervals as well as timestamps, but I don't think that does what you want. For example, it gives 3 for an interval of '2 days, 3 hours'. However, you can convert an interval to a number of seconds by specifying 'epoch' as the time element you want: extract(epoch from '2 days, 3 hours'::interval) returns 183600 (which you then divide by 3600 to convert seconds to hours).

So, putting this all together, you get basically Michael's answer: extract(epoch from timestamp1 - timestamp2)/3600. Since you don't seem to care about which timestamp precedes which, you probably want to wrap that in abs:

SELECT abs(extract(epoch from timestamp1 - timestamp2)/3600)