sqlpostgresqlintervals

Select intervals between since and until parameters including those that overlap period

Let's assume there is a following table:

CREATE TABLE time_interval (
    id SERIAL PRIMARY KEY,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP
);

Sample data:

INSERT INTO time_interval (start_time, end_time) VALUES
    ('2024-02-10 01:30:00',                  null), -- pending    
    ('2024-02-10 03:00:00',                  null), -- pending
    ('2024-02-10 07:00:00', '2024-02-10 10:30:00'),
    ('2024-02-10 09:00:00', '2024-02-10 12:00:00'),
    ('2024-02-10 11:30:00', '2024-02-10 15:00:00'),
    ('2024-02-10 13:30:00', '2024-02-10 17:30:00'),
    ('2024-02-10 16:00:00', '2024-02-10 20:00:00'),
    ('2024-02-10 18:30:00', '2024-02-10 22:00:00'),
    ('2024-02-10 21:00:00', '2024-02-10 23:30:00');

Query:

Give me all intervals between 2024-02-10 10:00:00 - 2024-02-10 17:00:00 including overlapping ones

Expected result:

    ('2024-02-10 01:30:00',                  null)
    ('2024-02-10 03:00:00',                  null)
    ('2024-02-10 07:00:00', '2024-02-10 10:30:00')
    ('2024-02-10 09:00:00', '2024-02-10 12:00:00')
    ('2024-02-10 11:30:00', '2024-02-10 15:00:00')
    ('2024-02-10 13:30:00', '2024-02-10 17:30:00')
    ('2024-02-10 16:00:00', '2024-02-10 20:00:00')

I was trying to use union to split the logic between those that pending and those that not but I am not sure about it.
Here is the simple version:

SELECT *
FROM time_interval
WHERE start_time < '2024-02-10 17:00:00'
  AND (end_time is null or end_time  > '2024-02-10 10:00:00');

Result is good but the question is... Is it that simple? Am I missing something? Do someone see any other corner cases that I should include?

Solution

  • Demo at db<>fiddle:

    select * from time_interval
where time_range && '[2024-02-10 10:00:00,2024-02-10 17:00:00]';

    You're working with ranges of timestamp: there's a built-in tsrange type for that. No need to emulate it with separate start/end fields and re-implement functions and operators that are already available - in this case you have a && range overlap operator.

    Added benefit is that you can even use multiranges: a single value can hold multiple ranges with breaks, not just one block with a single start and end. You can back these up with a proper index.

    If you wish to keep all your app logic that feeds these columns, you can set up the tsrange one to be separately generated based on those.

    alter table time_interval 
  add column time_range tsrange 
  generated always as (tsrange(coalesce(start_time,'infinity'), 
                               coalesce(end_time,  'infinity'))) stored;

    You can also cast those fields to the right type on-the-fly:

    select * from time_interval
where tsrange(coalesce(start_time,'infinity'), 
              coalesce(end_time,  'infinity')) 
    && '[2024-02-10 10:00:00,2024-02-10 17:00:00]';

    Or swap the columns in place:

    alter table time_interval add column start_end_time tsrange;
update time_interval 
  set start_end_time=tsrange(coalesce(start_time,'infinity'), 
                             coalesce(end_time,  'infinity'),
                             '[]');
alter table time_interval drop column start_time,
                          drop column end_time;

    Or leave your columns untouched and add an expression index. That way you keep your structure but at the same time, you can use fast and highly flexible queries, casting your target range on the fly: performance demo on 420 000 samples.

    create index gist_idx on time_interval 
  using gist(tsrange(coalesce(start_time,'infinity'), 
                     coalesce(end_time,  'infinity')));