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

Question

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?

Answer 1

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')));

Answer 2

You can determine overlapping dates by using the condition (StartA <= EndB) and (EndA >= StartB). In your case the start and end times might be null :

SELECT *
FROM time_interval
WHERE (start_time <= '2024-02-10 17:00:00' OR start_time IS NULL) 
      AND ('2024-02-10 10:00:00' <= end_time OR end_time IS NULL);

NOTE : If you wish to exclude the edges then change the <= operators to <

More details here : Determine Whether Two Date Ranges Overlap

Answer 3

You can check whether the start time OR the end time is between the start and end point of said interval OR the interval's start is between start time and end time OR the interval's end is between start time and end time:

SELECT *
FROM time_interval
WHERE (start_time BETWEEN '2024-02-10 17:00:00' AND '2024-02-10 10:00:00') OR
      (COALESCE(end_time, now()) BETWEEN '2024-02-10 17:00:00' AND '2024-02-10 10:00:00') OR
      ('2024-02-10 17:00:00' BETWEEN start_time AND COALESCE(end_time, now())) OR
      ('2024-02-10 10:00:00' BETWEEN start_time AND COALESCE(end_time, now()))

EDIT

I changed end_time references to COALESCE(end_time, now()), that is, for pending items it makes more sense to fall back to the current moment than having null in the comparison.

EDIT2

Simpler approach:

• if end_time is earlier than the right-hand-side of the interval you have given
  • or start_time is later than the left-hand-side of the interval you have given
• then the two intervals are NOT overlapping
• hence let's negate the result

SELECT *
FROM time_interval
WHERE NOT
(
    '2024-02-10 10:00:00' < COALESCE(end_time, now()) OR
    start_time > '2024-02-10 17:00:00'
)