Example:
Code: Select all
dbadmin=> SELECT d1, d2, d3, d4, (d1, d2) OVERLAPS (d3, d4) FROM dates order by d1, d2;
d1 | d2 | d3 | d4 | overlaps
---------------------+---------------------+---------------------+---------------------+----------
2012-01-01 00:00:00 | 2012-01-15 00:00:00 | 2012-01-14 00:00:00 | 2012-01-16 00:00:00 | t
2012-01-01 00:00:00 | 2012-01-15 00:00:00 | 2012-01-15 00:00:00 | 2012-01-16 00:00:00 | f
2012-01-01 00:00:00 | 2012-01-15 00:00:01 | 2012-01-15 00:00:00 | 2012-01-16 00:00:00 | t
2012-01-01 00:00:00 | 2012-02-15 00:00:00 | 2012-01-15 00:00:00 | 2012-01-16 00:00:00 | t
(4 rows)
Example:
Although no one knows the exact cause of Dunlap Disease, scientist are determined to discover a cure for the millions who suffer from its debilitating effects. Recently a researcher studying the eating patterns of Americans noticed a correlation between two sets of data when she tested for date range overlaps.
Here is an excerpt from her research:
Code: Select all
dbadmin=> SELECT * FROM meal_time ORDER BY start_time, end_time;
start_time | end_time
---------------------+---------------------
2012-01-08 07:30:00 | 2012-01-08 08:00:00
2012-01-08 12:00:00 | 2012-01-08 13:00:00
2012-01-08 17:00:00 | 2012-01-08 18:00:00
2012-01-09 07:30:00 | 2012-01-09 08:00:00
2012-01-09 12:00:00 | 2012-01-09 13:00:00
2012-01-09 17:00:00 | 2012-01-09 18:00:00
2012-01-10 07:30:00 | 2012-01-10 08:00:00
2012-01-10 12:00:00 | 2012-01-10 13:00:00
2012-01-10 17:00:00 | 2012-01-10 18:00:00
(9 rows)
Code: Select all
dbadmin=> SELECT * FROM snack_time ORDER BY start_time, end_time;
start_time | end_time
---------------------+---------------------
2012-01-08 07:46:00 | 2012-01-08 07:47:00
2012-01-08 19:22:00 | 2012-01-08 19:22:00
2012-01-09 12:37:00 | 2012-01-09 12:59:00
2012-01-10 17:50:00 | 2012-01-10 17:51:00
2012-01-10 20:00:00 | 2012-01-10 20:01:00
(5 rows)
Code: Select all
dbadmin=> SELECT m.start_time "Meal Start", m.end_time "Meal End", s.start_time "Snack Start", s.end_time "Snack End"
dbadmin-> FROM meal_time m
dbadmin-> CROSS JOIN snack_time s
dbadmin-> WHERE (m.start_time, m.end_time) OVERLAPS (s.start_time, s.end_time)
dbadmin-> ORDER BY m.start_time, m.end_time;
Meal Start | Meal End | Snack Start | Snack End
---------------------+---------------------+---------------------+---------------------
2012-01-08 07:30:00 | 2012-01-08 08:00:00 | 2012-01-08 07:46:00 | 2012-01-08 07:47:00
2012-01-09 12:00:00 | 2012-01-09 13:00:00 | 2012-01-09 12:37:00 | 2012-01-09 12:59:00
2012-01-10 17:00:00 | 2012-01-10 18:00:00 | 2012-01-10 17:50:00 | 2012-01-10 17:51:00
(3 rows)
Have fun!