postgres timerange that goes to the next day

Question

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'

FYI

CREATE FUNCTION ext.time_subtype_diff(x time, y time) RETURNS float8 AS 
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE ext.timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

Answer 1

There's no timerange range data type based on time (of day, without date). Closest thing is tsrange, which includes dates. It's best to always use the timezone-aware variant: demo

select tstzrange('today'::date   +'22:00'::time,
                 'tomorrow'::date+'03:00'::time);

tstzrange
["2024-02-20 22:00:00+00","2024-02-21 03:00:00+00")

Depending on what you plan to do with it, you could also create type of your own, setting it up as your desired range based on time. This would automatically create a multirange version of it and give you access to all built-in functions and operators, like @>: demo

create type ttzrange as range(subtype=timetz);
--above automatically creates `ttzmultirange` variant

create table test (tmr ttzmultirange);

insert into test 
select ttzmultirange(ttzrange('22:00','24:00'),ttzrange('00:00','05:00')) as ttzmr;

select ttzmr @> '01:00'::timetz as "is 1 o'clock in range" from test;

is 1 o'clock in range
t

You need the multirange for these to span "multiple days" - which isn't entirely valid interpretation, as time isn't supposed to be aware of date. 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 split the value into separate fields, or create a plain, non-range type with the start/end time fields:

create type timerange as(start_time time, end_time time);
select ('22:00','03:00')::timerange;

row
(22:00:00,03:00:00)

For some purposes it might be easier to hold only the start time and duration interval:

create type timerange2 as(start_time time, duration interval);

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):

select (tr2), 
       (tr2).start_time, 
       (tr2).duration,
       (tr2).start_time+(tr2).duration as end_time,
       '02:00' between (tr2).start_time 
                   and (tr2).start_time+(tr2).duration as "2 o'clock in range"
from test2;

tr2	start_time	duration	end_time	2 o'clock in range
(22:00:00,05:00:00)	22:00:00	05:00:00	03:00:00	F
(04:00:00,01:30:00)	04:00:00	01:30:00	05:30:00	F

The non-range approach forces you to emulate or re-implement the built-in functions and operators and/or address the range bounds "manually".

Answer 2

Simple example

create table test (id int,start_time time,end_time time);
insert into test values
 (1,'22:00','3:00')
,(2,'22:00','23:00')
,(3,'22:00','24:00')
;
select * from test;
select *
  ,end_time-start_time diff1
  ,end_time-start_time
   +case when end_time<start_time then '24:00'::time
    else '00:00'::time
    end
    diff2
from test;

Output

id	start_time	end_time	diff1	diff2
1	22:00:00	03:00:00	-19:00:00	05:00:00
2	22:00:00	23:00:00	01:00:00	01:00:00
3	22:00:00	24:00:00	02:00:00	02:00:00