So I have this table where I’ve got two times for each line, but no date and need to get the interval between those two, all is fine when it’s:
11:00:00 – 09:38:54
Returns: 01:21:06
As there’s no dates, times are stored in “time without time zone” format.
The problem arises when the time enters the next day and the hour becomes 00h, as there’s no date the interval will something absurd like -22:58:21
Example:
00:00:00 – 22:59:01
Returns: -22:59:01
00:00:00 – 22:44:06
Returns: -22:44:06
Is there anyway to make SQL understand 00:00:00 as 24:00:00 for the sake of math without date?
The hours only range between 8 and 0, and nothing from the previous day goes further than 0h30, a simple case for “00h” solves it, but I can’t make SQL understand 00h as 24h so far. Any ideas?
Advertisement
Answer
Like:
select '24:00:00'::time - '22:59:01'::time; ?column? ---------- 01:00:59
UPDATE
If the 00:00:00 is coming from somewhere you can’t modify in place then:
select time_fld from time_test ; time_fld ---------- 00:00:00 01:30:00 select coalesce(nullif(time_fld, '00:00:00'::time), '24:00:00') from time_test; coalesce ---------- 24:00:00 01:30:00