{"id":13,"date":"2019-01-29T14:38:00","date_gmt":"2019-01-29T14:38:00","guid":{"rendered":"\/colin\/morethanfour\/post\/Detecting-overlapping-date-ranges"},"modified":"2019-01-29T14:38:00","modified_gmt":"2019-01-29T14:38:00","slug":"detecting-overlapping-date-ranges","status":"publish","type":"post","link":"https:\/\/spacefold.com\/colin\/morethanfour\/2019\/01\/29\/detecting-overlapping-date-ranges\/","title":{"rendered":"Detecting overlapping date ranges"},"content":{"rendered":"<p>Consider an Event table, with columns <strong>A<\/strong> and <strong>B<\/strong> defining the start and end of the period of time over which the event occurred.<br \/>Consider also a random period of time defined from <strong>S<\/strong> to <strong>E<\/strong>.<br \/>Now imagine that you want to select all event rows that overlap that specific period of time.<\/p>\n<p>By overlap, we mean that case 1,2,4, and 6 should be included, but 3 and 5 should not:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"image\" style=\"border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px\" border=\"0\" alt=\"image\" src=\"http:\/\/www.spacefold.com\/colin\/morethanfour\/wp-content\/uploads\/image_30.png\" width=\"801\" height=\"321\"><\/p>\n<p>The first solution that might occur to us is this one:<\/p>\n<div>\n<pre id=\"codeSnippet\" style=\"border-top-style: none; font-size: 8pt; overflow: visible; border-left-style: none; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><p><span style=\"color: #0000ff\">select<\/span> * <span style=\"color: #0000ff\">from<\/span> Event <br><span style=\"color: #0000ff\">where<\/span> B <span style=\"color: #0000ff\">between<\/span> S <span style=\"color: #0000ff\">and<\/span> E  <span style=\"color: #0000ff\">or <\/span> A <span style=\"color: #0000ff\">between<\/span> S <span style=\"color: #0000ff\">and<\/span> E<\/p><\/pre>\n<\/div>\n<div>At first glance it looks clear and elegant, but unfortunately it doesn&#8217;t handle case #6.<\/div>\n<div>Somewhat less intuitive but still elegant and also complete is:<\/div>\n<\/p>\n<div id=\"codeSnippetWrapper\">\n<pre id=\"codeSnippet\" style=\"border-top-style: none; font-size: 8pt; overflow: visible; border-left-style: none; font-family: 'Courier New', courier, monospace; width: 100%; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; background-color: #f4f4f4\"><span style=\"color: #0000ff\">select<\/span> * <span style=\"color: #0000ff\">from<\/span> Event<br><span style=\"color: #0000ff\">where<\/span> B &gt;= S <span style=\"color: #0000ff\">and<\/span> A &lt;= E<\/pre>\n<p><\/div>\n<\/p>\n<div><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Consider an Event table, with columns A and B defining the start and end of the period of time over which the event occurred.Consider also a random period of time defined from S to E.Now imagine that you want to select all event rows that overlap that specific period of time. By overlap, we mean [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-13","post","type-post","status-publish","format-standard","hentry","category-sql-syntax","post-preview"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/13","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/comments?post=13"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/posts\/13\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/media?parent=13"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/categories?post=13"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/colin\/morethanfour\/wp-json\/wp\/v2\/tags?post=13"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}