Watch out for CAST(), DATE(), and any other function. In a WHERE clause, if you hide an
indexed column inside a function, the index cannot be used for optimization.
WHERE DATE(datetime_col) = '2013-01-01'
will not use the index!
The workaround is messy, but worth it (for performance):
WHERE datetime_col >= '2013-01-01'
AND datetime_col < '2013-01-01' + INTERVAL 1 DAY
(or any of a zillion variants)
(Yeah, it seems like the optimizer could do the obvious transformation for you. Hint,
> -----Original Message-----
> From: shawn green [mailto:shawn.l.green@stripped]
> Sent: Thursday, May 23, 2013 3:50 PM
> To: mysql@stripped
> Subject: Re: Bug in BETWEEN same DATETIME
> On 5/23/2013 4:55 PM, Daevid Vincent wrote:
> > I just noticed what I consider to be a bug; and related, has this
> > fixed in later versions of MySQL?
> > We are using:
> > mysql Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using
> > 5.2
> > If you use BETWEEN and the same date for both parts (i.e. you want a
> > single
> > day) it appears that the operator isn't smart enough to consider the
> > full day in the cases where the column is a DATETIME
> > http://dev.mysql.com/doc/refman/5.0/en/comparison-
> > tor_be
> > tween
> > WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
> > I actually have to format it like this to get results
> > WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-
> > 11:59:59'
> From the Fine Manual...
> Conversion of DATE values:
> Conversion to a DATETIME or TIMESTAMP value adds a time part of
> '00:00:00' because the DATE value contains no time information.
> Prior to MySQL 5.0.42, when DATE values are compared with DATETIME
> values, the time portion of the DATETIME value is ignored, or the
> comparison could be performed as a string compare. Starting from MySQL
> 5.0.42, a DATE value is coerced to the DATETIME type by adding the time
> portion as '00:00:00'. To mimic the old behavior, use the CAST()
> function to cause the comparison operands to be treated as previously.
> For example:
> date_col = CAST(datetime_col AS DATE)
> That seems pretty clear to me as not a bug.
> Shawn Green
> MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware
> and Software, Engineered to Work Together.
> Office: Blountville, TN
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql