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:[email protected]]
> Sent: Thursday, May 23, 2013 3:50 PM
> To: [email protected]
> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
