Skip to content
Advertisement

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:

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

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:

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?

Advertisement

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:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement