The OVERLAPS Function

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

The OVERLAPS Function

Post by JimKnicely » Tue Jan 15, 2013 2:45 pm

The OVERLAPS function returns true when two time periods overlap, false when they do not overlap.

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)
The OVERLAPS function can be used in a WHERE clause to limit query results!

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)
It appears that those subjects having Dunlap Disease tend to snack while eating their meals. Sheesh!

Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”