Re: Bug in BETWEEN same DATETIME

2013-05-24 Thread hsv
2013/05/24 09:49 -0400, shawn green Or we could coerce datetime values back to their date values when both are being used. The trick now becomes choosing between rounding the datetime value (times past noon round to the next date) or do we use the floor() function all the time. <<<

Re: Bug in BETWEEN same DATETIME

2013-05-24 Thread shawn green
Hi Rick, Thank you for continuing this. It brings up a few good points. On 5/24/2013 12:17 PM, Rick James wrote: For years (even decades), I have stayed out of trouble by assuming a 'date' represents the instant in time corresponding to midnight at the start of that day. In MySQL (until 5.6)

RE: Bug in BETWEEN same DATETIME

2013-05-24 Thread Rick James
For years (even decades), I have stayed out of trouble by assuming a 'date' represents the instant in time corresponding to midnight at the start of that day. In MySQL (until 5.6), that is equivalent to a 1-second DATETIME. I also assume midnight belongs to the day that it is the _start_ of.

Re: Bug in BETWEEN same DATETIME

2013-05-24 Thread shawn green
Hello Rick, On 5/23/2013 7:08 PM, Rick James wrote: 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' wi