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?
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;