Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

SELECT 1

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

CREATE TYPE

row

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

SELECT 1

create table test (tr timerange);
insert into test values (('22:00','03:00')),(('4:00','05:30')) returning *;

CREATE TABLE

tr

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

(04:00:00,05:30:00)

INSERT 0 2

--range overlaps are ugly
select (tr), 
       (tr).start_time, 
       (tr).end_time,
       (tr).start_time>(tr).end_time as "starts late, ends early",
       '02:00' between (tr).start_time 
                   and (tr).end_time as "3 o'clock in range",--wrong
       case when (tr).start_time<(tr).end_time
            then '02:00' between (tr).start_time and (tr).end_time
            else '02:00' between (tr).start_time and '00:00'::time
              or '02:00' between '00:00'::time and (tr).end_time
       end as "3 o'clock in range, correct"
from test as t(tr);

tr
start_time
end_time
starts late, ends early
3 o'clock in range
3 o'clock in range, correct

(22:00:00,03:00:00)
22:00:00
03:00:00
t
f
t

(04:00:00,05:30:00)
04:00:00
05:30:00
f
f
f

SELECT 2

create type timerange2 as(start_time time, duration interval);
create table test2 (tr2 timerange2);
insert into test2 values (('22:00','05:00')),(('4:00','01:30')) returning *;

CREATE TYPE

CREATE TABLE

tr2

(22:00:00,05:00:00)

(04:00:00,01:30:00)

INSERT 0 2

--easier to check range overlap
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

SELECT 2

tr	start_time	end_time	starts late, ends early	3 o'clock in range	3 o'clock in range, correct
(22:00:00,03:00:00)	22:00:00	03:00:00	t	f	t
(04:00:00,05:30:00)	04:00:00	05:30:00	f	f	f

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