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"

Solution 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)

Solution 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'

Solution 3

Michael Krelin's answer is close is not entirely safe, since it can be wrong in rare situations. The problem is that intervals in PostgreSQL do not have context with regards to things like daylight savings. Intervals store things internally as months, days, and seconds. Months aren't an issue in this case since subtracting two timestamps just use days and seconds but 'days' can be a problem.

If your subtraction involves daylight savings change-overs, a particular day might be considered 23 or 25 hours respectively. The interval will take that into account, which is useful for knowing the amount of days that passed in the symbolic sense but it would give an incorrect number of the actual hours that passed. Epoch on the interval will just multiply all days by 24 hours.

For example, if a full 'short' day passes and an additional hour of the next day, the interval will be recorded as one day and one hour. Which converted to epoch/3600 is 25 hours. But in reality 23 hours + 1 hour should be a total of 24 hours.

So the safer method is:

(EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM somedate))/3600

As Michael mentioned in his follow-up comment, you'll also probably want to use floor() or round() to get the result as an integer value.

Solution 4

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)

Solution 5

postgresql get seconds difference between timestamps

SELECT (
    (extract (epoch from (
        '2012-01-01 18:25:00'::timestamp - '2012-01-01 18:25:02'::timestamp
                         )
             )
    )
)::integer

which prints:

-2

Because the timestamps are two seconds apart. Take the number and divide by 60 to get minutes, divide by 60 again to get hours.

View more solutions

Comments

[TheLizardKing]

This is indeed the closest. Dropping all decimals shouldn't be a problem although I am rather saddened by the lack of a built-in function to do this like MySQL.

[araqnid]

There shouldn't be any decimals, since it will be doing integer division.

[Michael Krelin - hacker]

araqnid, I also thought so, but I've just tried and it does indeed yield float.

[a_horse_with_no_name]

So how do you ensure that only valid dates make it into your "integer date"? Where do you check whether 2012-02-29 is valid or not. How do you know if the time 02:14 is valid for March 24th? (Hint it is not valid everywhere due to DST rules). And deleting is quite easy: DELETE FROM SESSION_TABLE WHERE time-initialized < current_date - interval '2' day

[A.H.]

After three years... I doubt. Besides: Trying select extract(hour from '11 mons 30 days 23:00:00'::interval); you will only '23' and not 11 months, 30 days and 23 hours expressed in hours (ca. 8660).

[Zoran777]

How to get a non-float value ? @MichaelKrelin-hacker

[Michael Krelin - hacker]

@Zoran777 convert it to integer?

[Zoran777]

@MichaelKrelin-hacker, got it, used round(). Thanks anyways!

[hakre]

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.