3

I have got a table structure as below:

create table schedule_tab
(
    schedule_id serial
            primary key,
    user_id     integer     not null,
    type        varchar(64) not null,
    start_date       timestamp   not null,
    end_date       timestamp not null
);

insert into schedule_tab (user_id, type, start_date, end_date) values
   (1, 'AVAIL', '2023-01-04 06:00:00', '2023-01-04 22:00:00'),
   (1, 'AVAIL', '2023-01-05 06:00:00', '2023-01-05 22:00:00'),
   (1, 'AVAIL', '2023-01-06 06:00:00', '2023-01-06 22:00:00'),
   (1, 'BUSY',  '2023-01-04 08:00:00', '2023-01-04 12:00:00'),
   (1, 'BUSY',  '2023-01-05 17:00:00', '2023-01-05 23:00:00');

db example

It is possible to prepare an SQL query (PostgreSQL 12.6) that returns a user's real slots when a specific user is available. Records with type AVAIL represent user availability. On the other hand BUSY shows when the user has got reserved already time.

For example for date: 2023-01-04 should be returned 2 slots between 6:00-8:00 and 12:00-22:00 because between 8:00-12:00 my user is busy.

1 Answer 1

1

If you upgraded to PostgreSQL v14, that would be simple using multiranges:

SELECT range_agg(tsrange(start_date, end_date)) FILTER (WHERE type = 'AVAIL')
     - range_agg(tsrange(start_date, end_date)) FILTER (WHERE type = 'BUSY')
FROM schedule_tab;

If you want the multirange split up:

SELECT lower(q2.range) AS available_from,
       upper(q2.range) AS available_to
FROM (SELECT range_agg(tsrange(start_date, end_date)) FILTER (WHERE type = 'AVAIL')
           - range_agg(tsrange(start_date, end_date)) FILTER (WHERE type = 'BUSY') AS ranges
      FROM schedule_tab) AS q1
   CROSS JOIN LATERAL unnest(q1.ranges) AS q2(range);

   available_from    │    available_to     
═════════════════════╪═════════════════════
 2023-01-04 06:00:00 │ 2023-01-04 08:00:00
 2023-01-04 12:00:00 │ 2023-01-04 22:00:00
 2023-01-05 06:00:00 │ 2023-01-05 17:00:00
 2023-01-06 06:00:00 │ 2023-01-06 22:00:00
(4 rows)
1
  • Unfortunately we have got PG 12.6
    – Robert
    Jan 31, 2023 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.