start time ’22:00′
end time ‘3:00’
select time '22:00' - time '3:00' --result interval -18 h<br />
SELECT 24 - (abs(extract(hour from time '1:00' - time '22:00' ))) -- returns 3
finally
how to create this timerange knowing that endtime of ‘3:00′ [upper bound] is less than [lower bound] ’22:00’
2
Answers
There’s no
timerange
range data type based ontime
(of day, without date). Closest thing istsrange
, which includes dates. It’s best to always use the timezone-aware variant: demoDepending on what you plan to do with it, you could also
create type
of your own, setting it up as your desired range based ontime
. This would automatically create a multirange version of it and give you access to all built-in functions and operators, like@>
: demoYou need the multirange for these to span "multiple days" – which isn’t entirely valid interpretation, as
time
isn’t supposed to be aware ofdate
. Aside from the huge benefit of having built-ins at disposal, this also enables you to accommodate mid-day breaks in your ranges – the range crossing midnight is actually two ranges, one early in the small hours, one late evening up to midnight, with a long break in between.You could also create a plain, non-range type with the start/end
time
fields:For some purposes it might be easier to hold only the start
time
and durationinterval
:Which doesn’t help much in containment/overlap checks (2 o’clock should be in the first range, but it’s still ToD vs ToD, assuming no difference in date, since it’s unaware of dates):
The non-range approach forces you to emulate or re-implement the built-in functions and operators.
Simple example
Output