SELECT with date range
suggest changeSELECT ... WHERE dt >= '2017-02-01'
AND dt < '2017-02-01' + INTERVAL 1 MONTH
Sure, this could be done with BETWEEN
and inclusion of 23:59:59
. But, the pattern has this benefits:
- You don’t have pre-calculate the end date (which is often an exact length from the start)
- You don’t include both endpoints (as
BETWEEN
does), nor type ‘23:59:59’ to avoid it. - It works for
DATE
,TIMESTAMP
,DATETIME
, and even the microsecond-includedDATETIME(6)
. - It takes care of leap days, end of year, etc.
- It is index-friendly (so is
BETWEEN
).
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents