StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your temporal condition is currently:</p> <pre><code> " (start_time >= ? AND start_time <= ?) OR " " (end_time > ? AND end_time <?) OR " " (start_time < ? AND end_time > ?)" </code></pre> <p>We can immediately improve the readability (in constant width fonts) with some spaces:</p> <pre><code> " (start_time >= ? AND start_time <= ?) OR " " (end_time > ? AND end_time < ?) OR " " (start_time < ? AND end_time > ?)" </code></pre> <p>And from the commentary, we know that the same value will be passed to the placeholders 1, 3, 5, and a different value will be passed to placeholders 2, 4, 6 (but they all get the same value too). Further, if we call those times <code>t1</code> and <code>t2</code>, then we can assume that <code>t1 <= t2</code>.</p> <p>So, what is this criterion looking for?</p> <ul> <li>start time falls in the range t1..t2</li> <li>end time falls in the range t1..t2</li> <li>start time is earlier than t1 and end time is later than t2</li> </ul> <p>This is an overlaps criterion written the hard way - it should be replaced by:</p> <pre><code> "(start_time <= ? AND end_time >= ?)" </code></pre> <p>Except that placeholder one here corresponds to <code>t2</code> and placeholder two corresponds to <code>t1</code>. If you don't want events that meet the time range to be counted (that is, you do not want to count an event that ended at the instant <code>t1</code>, or an event that started at the instant <code>t2</code>), then change the '<code>>=</code>' and '<code><=</code>' into '<code>></code>' and '<code><</code>' respectively.</p> <p>This is the standard way of writing the overlaps predicate when including the end times. The condition is much simpler - no OR terms - and is reliable. It will be faster in that the optimizer has less work to do, and possibly that the execution engine will have fewer criteria to apply. (A really good optimizer might spot the equivalence of the 2-placeholder and 6-placeholder versions, but I wouldn't want to bet on it doing so - not least because the optimizer cannot tell that placeholders 1,3,5 will be the same, nor that placeholders 2,4,6 will be the same; that can only be determined if it bothered to reoptimize when the statement is executed.)</p>

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