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

Postgresql – 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?

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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'
)
    Login or Signup to reply.
  3. 0

    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.

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

Back To Top
Search