235

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)
agnul
  • 12,608
  • 14
  • 63
  • 85
  • 9
    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! – Teddy Jul 26 '17 at 08:57
  • 1
    @Teddy: More precisely, there are _multiple_ defined answers ;) – Jonathan Hall Jun 08 '21 at 14:27

9 Answers9

452

Probably the easiest way is:

SELECT EXTRACT(epoch FROM my_interval)/3600
Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
  • 8
    And maybe floor or cast the result to integer if the interval contains minutes and/or seconds – rasjani Jun 04 '09 at 19:25
  • 87
    Extract epoch? Oh my, that wouldn't have crossed my mind in a million years. – agnul Jun 04 '09 at 19:31
  • 7
    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. – Offenso Aug 12 '15 at 13:24
  • 30
    Warning: simply extracting epoch implicitly assumes that one month = 30 days and one year = 365.25 days. – Teddy Jul 26 '17 at 08:58
  • 2
    @Teddy what can we do with it? How to avoid this issue and get real number of epochs? – Asmoox Oct 19 '19 at 10:49
  • 1
    @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')`. – Ludovic Kuty Oct 19 '20 at 12:04
  • 2
    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. – ZZ Coder Oct 07 '21 at 23:51
  • @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. – some Dec 27 '22 at 14:08
  • @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. – Knox Apr 24 '23 at 19:40
  • 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))` – sitthykun Aug 22 '23 at 07:09
37

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

SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int
Pujan
  • 3,154
  • 3
  • 38
  • 52
  • 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. – 1111161171159459134 Dec 11 '15 at 19:19
  • 19
    Warning: simply extracting epoch implicitly assumes that one month = 30 days and one year = 365.25 days. – Teddy Jul 26 '17 at 08:58
10

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;
belveryin
  • 361
  • 3
  • 3
  • 58
    If your interval is `'1 month 0 days'`, using `extract(day from …)` will give you `0` as the result. – Underyx Mar 01 '17 at 13:02
3
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.

nick
  • 1,090
  • 1
  • 11
  • 24
haoming
  • 787
  • 7
  • 4
0

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

nick
  • 1,090
  • 1
  • 11
  • 24
Janek Olszak
  • 4,067
  • 1
  • 28
  • 22
0

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;

Aarav
  • 1
0

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')
Pawel
  • 16,093
  • 5
  • 70
  • 73
-2

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; $$;
Juan Salvador
  • 407
  • 9
  • 15
-4
         select date 'now()' - date '1955-12-15';

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

Arunkumar Papena
  • 239
  • 2
  • 13