How to convert an interval like "1 day 01:30:00" into "25:30:00"?

Question

I need to add some intervals and use the result in Excel.

Since

sum(time.endtime-time.starttime)

returns the interval as "1 day 01:30:00" and this format breaks my Excel sheet, I thought it'd be nice to have the output like "25:30:00" but found no way to do it in the PostgreSQL documentation.

Can anyone here help me out?

score 27 · Accepted Answer · edited Feb 16 '15 at 11:52

27

Since there is not an exact solution for the topic:

=> SELECT date_part('epoch', INTERVAL '1 day 01:30:00') * INTERVAL '1 second' hours;
  hours
-----------
 25:30:00
(1 row)

Source: Documentation

edited Feb 16 '15 at 11:52

mat

12,943
5
39
44

answered Jun 23 '14 at 10:01

neshkeev

6,280
3
26
47

Great! Ideal solution. – Piotr Olaszewski Feb 04 '15 at 10:36
This works with negative intervals! Thanks!!! – Jeff Jun 10 '22 at 01:29

score 21 · Answer 2 · answered Dec 04 '08 at 17:30

21

The only thing I can come with (beside parsing the number of days and adding 24 to the hours every time) is :

mat=> select date_part('epoch', '01 day 1:30:00'::interval);
 date_part 
-----------
     91800
(1 row)

It will give you the number of seconds, which may be ok for excel.

answered Dec 04 '08 at 17:30

mat

12,943
5
39
44

Great stuff. I needed some way of taking an absolute from an interval and the first step I needed was to 'cast' the interval to seconds and this just did the trick :) `SELECT * FROM table ORDER BY abs(date_part('epoch',('2011-07-19 02:40:05' - table.time)))` – Boro Dec 19 '12 at 09:51

score 19 · Answer 3 · answered Dec 04 '08 at 17:35

19

You could use EXTRACT to convert the interval into seconds.

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800

Then you would need to do your own maths (or let Excel do it).

Note that '1 day' is not necessarily equivalent to '24 hours' - PostgreSQL handles things like an interval that spans a DST transition.

answered Dec 04 '08 at 17:35

slim

40,215
13
94
127

1

In a (timestamp|date|time) with time zone, yes, but, hum, an interval has no notion of when it's happening, so, it has no DST transition or leap seconds meaning. – mat Dec 04 '08 at 17:37
osm=> select '2008-03-29T02:00:05+0200'::timestamptz + '1 day'::interval; ?column? ------------------------ 2008-03-30 01:00:05+01 (1 row) – mat Dec 04 '08 at 17:39
3

I can't be authoritative on the matter, but it seems as if Postgres stores the interval as a set of fields, not just an integer that's converted for display. It will be interpreted as a different number of seconds depending on where it's applied. Robbing it of context means it must make assumptions. – slim Dec 04 '08 at 17:47

score 13 · Answer 4 · answered Jun 16 '11 at 19:29

13

If you wanted postgres to handle the HH:MM:SS formatting for you, take the difference in epoch seconds and convert it to an interval scaled in seconds:

SELECT SUM(EXTRACT(EPOCH FROM time.endtime) - EXTRACT(EPOCH FROM time.starttime))
         * INTERVAL '1 SECOND' AS hhmmss

answered Jun 16 '11 at 19:29

pilcrow

56,591
13
94
135

3

Very subtle. This changes the interval from days, hours, minutes, seconds to just seconds, after which you can just do `to_char(xxx, 'HH24:MI:SS')`. – sayap Feb 24 '12 at 10:05

score 0 · Answer 5 · answered Dec 04 '08 at 17:47

In standard SQL, you want to represent the type as INTERVAL HOUR TO SECOND, but you have a value of type INTERVAL DAY TO SECOND. Can you not use a CAST to get to your required result? In Informix, the notation would be either of:

SUM(time.endtime - time.starttime)::INTERVAL HOUR(3) TO SECOND

CAST(SUM(time.endtime - time.starttime) AS INTERVAL HOUR(3) TO SECOND)

The former is, AFAIK, Informix-specific notation (or, at least, not standard); the latter is, I believe, SQL standard notation.

score -1 · Answer 6 · answered Dec 08 '08 at 23:19

It can be done, but I believe that the only way is through the following monstrosity (assuming your time interval column name is "ti"):

select
              to_char(floor(extract(epoch from ti)/3600),'FM00')
    || ':' || to_char(floor(cast(extract(epoch from ti) as integer) % 3600 / 60), 'FM00')
    || ':' || to_char(cast(extract(epoch from ti) as integer) % 60,'FM00')
    as hourstamp
from whatever;

See? I told you it was horrible :)

It would have been nice to think that

select to_char(ti,'HH24:MI:SS') as hourstamp from t

would worked, but alas, the HH24 format doesn't "absorb" the overflow beyond 24. The above comes (reconstructed from memory) from some code I once wrote. To avoid offending those of delicate constitution, I encapsulated the above shenanigans in a view...

How to convert an interval like "1 day 01:30:00" into "25:30:00"?

6 Answers6

Linked