PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres timerange that goes to the next day

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’

Tags:

2

Answers


  1. 0

    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 trange as range(subtype=time);--automatically creates `tmultirange` variant

create table test (tmr tmultirange);

insert into test 
select tmultirange(trange('22:00','24:00'),trange('00:00','05:00')) as tmr;

select tmr @> '01:00'::time 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 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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search