How to compute duration between two times in postgresql when the end time is sometimes past midnight

I have to compute the duration of events in postgres based on start_time and end_time, both are in the HH:MM:SS format. Simply end_time – start_time works:

create table test_date as
    select sum(eindtijd - starttijd) as tijdsduur
    from evenementen_2019;

This results in an interval in HH:MM:SS format. But sometimes the end_time is the next day. Examples:

start_time end_time duration computed
18:00       21:00   3:00        3:00
18:00        0:00   6:00      -18:00
18:00        1:00   7:00      -17:00

I only have times without time zones, no dates.

The solution is conceptually simple: when duration < 0, add 24 hours to it. So I tried:

update test_date
    set duration = to_date('24:00:00', 'HHMMSS') - duration 
    where duration < 0 * interval '1' second; 

This generates an error:

ERROR: column “duration” is of type interval but expression is of type timestamp without time zone

That’s right and I thought that a timestamp - interval yields a timestamp. I am at a loss on how to solve this problem. Does somebody know what is the best way to solve this problem?

Answer

I thought that a timestamp – interval yields a timestamp

It does. And you then try to store that resulting timestamp into a column of type interval, which doesn’t work.

Note that if it survived this part, it still wouldn’t work, because your to_date function call would fail at run time.

You say you should add 24 hours, but you what you try to do is subtract from 24 hours (of the wrong type), which would be wrong if it did work.

You can translate what you said you want to do almost word for word into SQL:

update test_date
    set duration =  duration + interval '24 hours'
    where duration < 0 * interval '1' second;