PostgreSQL OVERLAPS examples

gistfile1.txt

Starting data: 2 reserves on two different meeting rooms.

M1: Meeting Room 1
M2: Meeting Room 2

   M1 M2
07
08
09  x
10  x x
11  x x
12  x
13
14
15
16
17

Check availability on different schedules:

select (start_time, end_time) OVERLAPS ('2016-04-18 08:00'::timestamp, '2016-04-18 10:00'::timestamp), r.start_time, r.end_time, r.meeting_room_id from reserves r;
+------------+---------------------------+---------------------------+--------------------------------------+
| overlaps   | start_time                | end_time                  | meeting_room_id                      |
|------------+---------------------------+---------------------------+--------------------------------------|
| True       | 2016-04-18 09:00:00-03:00 | 2016-04-18 13:00:00-03:00 | e5188d4b-a1dd-4ea2-bde5-6344e7bef577 |
| False      | 2016-04-18 10:00:00-03:00 | 2016-04-18 12:00:00-03:00 | 9ee24501-8fcc-4144-b9ff-47efa3360862 |
+------------+---------------------------+---------------------------+--------------------------------------+
SELECT 2
Time: 0.002s
select (start_time, end_time) OVERLAPS ('2016-04-18 08:00'::timestamp, '2016-04-18 17:00'::timestamp), r.start_time, r.end_time, r.meeting_room_id from reserves r;
+------------+---------------------------+---------------------------+--------------------------------------+
| overlaps   | start_time                | end_time                  | meeting_room_id                      |
|------------+---------------------------+---------------------------+--------------------------------------|
| True       | 2016-04-18 09:00:00-03:00 | 2016-04-18 13:00:00-03:00 | e5188d4b-a1dd-4ea2-bde5-6344e7bef577 |
| True       | 2016-04-18 10:00:00-03:00 | 2016-04-18 12:00:00-03:00 | 9ee24501-8fcc-4144-b9ff-47efa3360862 |
+------------+---------------------------+---------------------------+--------------------------------------+
SELECT 2
Time: 0.002s
select (start_time, end_time) OVERLAPS ('2016-04-18 10:00'::timestamp, '2016-04-18 11:00'::timestamp), r.start_time, r.end_time, r.meeting_room_id from reserves r;
+------------+---------------------------+---------------------------+--------------------------------------+
| overlaps   | start_time                | end_time                  | meeting_room_id                      |
|------------+---------------------------+---------------------------+--------------------------------------|
| True       | 2016-04-18 09:00:00-03:00 | 2016-04-18 13:00:00-03:00 | e5188d4b-a1dd-4ea2-bde5-6344e7bef577 |
| True       | 2016-04-18 10:00:00-03:00 | 2016-04-18 12:00:00-03:00 | 9ee24501-8fcc-4144-b9ff-47efa3360862 |
+------------+---------------------------+---------------------------+--------------------------------------+
SELECT 2
Time: 0.002s
select (start_time, end_time) OVERLAPS ('2016-04-18 12:00'::timestamp, '2016-04-18 18:00'::timestamp), r.start_time, r.end_time, r.meeting_room_id from reserves r;
+------------+---------------------------+---------------------------+--------------------------------------+
| overlaps   | start_time                | end_time                  | meeting_room_id                      |
|------------+---------------------------+---------------------------+--------------------------------------|
| True       | 2016-04-18 09:00:00-03:00 | 2016-04-18 13:00:00-03:00 | e5188d4b-a1dd-4ea2-bde5-6344e7bef577 |
| False      | 2016-04-18 10:00:00-03:00 | 2016-04-18 12:00:00-03:00 | 9ee24501-8fcc-4144-b9ff-47efa3360862 |
+------------+---------------------------+---------------------------+--------------------------------------+
SELECT 2
Time: 0.002s
select r.start_time, r.end_time, r.meeting_room_id from reserves r where (start_time, end_time) OVERLAPS ('2016-04-18 12:00'::timestamp, '2016-04-18 18:00'::timestamp);
+---------------------------+---------------------------+--------------------------------------+
| start_time                | end_time                  | meeting_room_id                      |
|---------------------------+---------------------------+--------------------------------------|
| 2016-04-18 09:00:00-03:00 | 2016-04-18 13:00:00-03:00 | e5188d4b-a1dd-4ea2-bde5-6344e7bef577 |
+---------------------------+---------------------------+--------------------------------------+
SELECT 1
Time: 0.002s