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.
INDEX(datetime_col)
...
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,
hint, Shawn.)
> -----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
> been
> > 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-> operators.html#opera
> > 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-
> 16
> > 11:59:59'
> >
>
> From the Fine Manual...
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-> conversion.html
> ###############
> 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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.