How do I convert an interval into a number of hours with postgres?

Question

Say I have an interval like

4 days 10:00:00

in postgres. How do I convert that to a number of hours (106 in this case?) Is there a function or should I bite the bullet and do something like

extract(days, my_interval) * 24 + extract(hours, my_interval)

Monahon

Note: If your interval contains months or years, there is no defined answer of how many hours there are, since the number of days in a month or year vary. So watch out for that! – Glennglenna

@Teddy: More precisely, there are multiple defined answers ;) – Bevel

Answer 1

Probably the easiest way is:

SELECT EXTRACT(epoch FROM my_interval)/3600

Stepup

And maybe floor or cast the result to integer if the interval contains minutes and/or seconds – Triumvir

Extract epoch? Oh my, that wouldn't have crossed my mind in a million years. – Monahon

SELECT EXTRACT(epoch FROM my_interval/3600) (interval has native 'divide integer' support, result is interval, and extract result is integer, not float). So. Autocast/Floor done. – Whereunto

Warning: simply extracting epoch implicitly assumes that one month = 30 days and one year = 365.25 days. – Glennglenna

@Glennglenna what can we do with it? How to avoid this issue and get real number of epochs? – Prober

@Asmox, where is the problem ? This is used for duration. select extract(epoch from timestamp '2020-10-31 23:59:59' - timestamp '2020-10-01 00:00:00') gives 2678399 as does select extract(epoch from interval '1 month 23:59:59'). – Amylopectin

Just found a problem with this approach. It doesn't take into consideration of leap years so it's always a few days less if you are doing a long interval like 10 years. – Velocipede

@ZZCoder Calculations with Date and time are more complicated than people think: A century can be 36525 or 36524 days, a month 28-31 days, a minute 59-61 seconds. And this without taking into account time zones (which can be plus or minus hours and 0, 15, 30 or 45 minutes, and some change the offset twice a year, the rules for when this happens can change, and which time zone applies can change at any time). My advice is: don't do calculations in seconds if seconds aren't the goal. – Feet

@ZZCoder This approach just takes an interval (a duration of time) and tells you how many hours that interval is. Intervals are independent of leap years. If you're having a problem with leap years, the problem is somewhere else, outside the scope of this answer. – Aphid

Another example for this function that will convert interval to second as integer without round function. SELECT ROUND(EXTRACT(epoch FROM '00:00:59.743685'::interval)) – Tetrafluoroethylene

Answer 2

If you want integer i.e. number of days:

SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int

Karinkarina

Great! Thank you for that :) Yet, I found that we can now modify this to be SELECT extract('epoch' FROM age('2014-08-02'::timestamp)) / 86400 (I'm using Pg 9.4), since age(ts) automatically use CURRENT_DATE when only one argument. – Mendymene

Warning: simply extracting epoch implicitly assumes that one month = 30 days and one year = 365.25 days. – Glennglenna

Answer 3

To get the number of days the easiest way would be:

SELECT EXTRACT(DAY FROM NOW() - '2014-08-02 08:10:56');

As far as I know it would return the same as:

SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int;

Cherimoya

If your interval is '1 month 0 days', using extract(day from …) will give you 0 as the result. – Choreographer

Answer 4

select floor((date_part('epoch', order_time - '2016-09-05 00:00:00') / 3600)), count(*)
from od_a_week
group by floor((date_part('epoch', order_time - '2016-09-05 00:00:00') / 3600));

The ::int conversion follows the principle of rounding. If you want a different result such as rounding down, you can use the corresponding math function such as floor.

Bohs

Answer 5

If you convert table field:

1. Define the field so it contains seconds:

    CREATE TABLE IF NOT EXISTS test (
        ...
        field        INTERVAL SECOND(0)
    );
   

2. Extract the value. Remember to cast to int other wise you can get an unpleasant surprise once the intervals are big:

   EXTRACT(EPOCH FROM field)::int

Platypus

I don't think Redshift supports the INTERVAL datatype. It would just be BIGINT. – Banderilla

Answer 6

If you want to display your result only in date type after adding the interval then, should try this

Select (current_date + interval 'x day')::date;

Assyria

Answer 7

A function to avoid having verbose extract epoch all over the place. Returns seconds so for hours needs int_interval('1 day') / 3600

CREATE OR REPLACE FUNCTION int_interval(interval_text text) returns INT LANGUAGE SQL IMMUTABLE as $$ SELECT EXTRACT(epoch FROM interval_text::INTERVAL)::INT $$;

Usage

int_interval('1 day')

Cognomen

Answer 8

I'm working with PostgreSQL 11, and I created a function to get the hours betweeen 2 differents timestamps

create function analysis.calcHours(datetime1 timestamp, datetime2 timestamp)
    returns integer
    language plpgsql as $$
    declare
        diff interval;
    begin
        diff = datetime2 - datetime1;
        return (abs(extract(days from diff))*24 + abs(extract(hours from diff)))::integer;
    end; $$;

Edlin

Answer 9

         select date 'now()' - date '1955-12-15';

Here is the simple query which calculates total no of days.

Reckford

That does not take an interval value. – Glennglenna