StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>(event.start BETWEEN week.start AND week.end) OR (week.start BETWEEN event.start AND event.end) </code></pre> <p>In simple words, either a week starts during the event, or an event starts during the week.</p> <p>Let's check it:</p> <blockquote> <p>Event begins and ends within the week</p> </blockquote> <p><strong>The event starts during the week.</strong></p> <blockquote> <p>Event begins before the week, but ends within the week</p> </blockquote> <p><strong>The week starts during the event.</strong></p> <blockquote> <p>Event begins within the week, but ends after the week</p> </blockquote> <p><strong>The event starts during the week.</strong></p> <blockquote> <p>Event begins before the week and also ends after the week</p> </blockquote> <p><strong>The week starts during the event.</strong></p> <p>Note that <code>BETWEEN</code> in expressions above is used just for the sake of brevity.</p> <p>Strict expression looks like this:</p> <pre><code>(event.start >= week.start AND event.start < week.end) OR (week.start >= event.start AND week.start < event.end) </code></pre> <p>, provided that <code>week.end</code> is a <code>week.start + INTERVAL 7 DAY</code>.</p> <p>I. e. if you week starts of <code>Sun, 0:00:00</code>, then it should end on <code>next Sun, 0:00:00</code> (not on <code>Sat, 0:00:00</code>)</p> <p>This expression looks more complex than the one which is commonly used:</p> <pre><code>event.start < week.end AND event.end > week.start </code></pre> <p>, but the former is more efficient and index friendly.</p> <p>See these articles in my blog for performance comparisons:</p> <ul> <li><a href="http://explainextended.com/2009/06/30/overlapping-ranges-sql-server/" rel="noreferrer"><strong>Overlapping ranges: SQL Server</strong></a></li> <li><a href="http://explainextended.com/2009/07/01/overlapping-ranges-mysql/" rel="noreferrer"><strong>Overlapping ranges: MySQL</strong></a></li> <li><a href="http://explainextended.com/2009/07/02/overlapping-ranges-oracle/" rel="noreferrer"><strong>Overlapping ranges: Oracle</strong></a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload