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
transaction_date BETWEEN '2013-04-16' AND '2013-04-16'
I actually have to format it like this to get results
transaction_date BETWEEN '2013-04-16 00:00:00' AND '2013-04-16 11:59:59'
As it appears that in the first instance it defaults the time to 00:00:00 always, as verified by this:
transaction_date BETWEEN '2013-04-16' AND '2013-04-16 11:59:59'
So, I think it's probably safe to assume that if someone is using the BETWEEN on datetime columns, their intent more often than not is to get the full 24 hour period, not the 0 seconds it currently pulls by default.
I also tried these hacks as per the web page above, but this doesn't yield results either
transaction_date BETWEEN CAST('2013-04-16' AS DATE) AND CAST('2013-04-16' AS DATE) WHERE
transaction_date BETWEEN CAST('2013-04-16' AS DATETIME) AND CAST('2013-04-16' AS DATETIME)
This one works, but I fail to see how it's any more beneficial than using a string without the CAST() overhead?
transaction_date BETWEEN CAST('2013-04-16 00:00:00' AS DATETIME) AND CAST('2013-04-16 11:59:59' AS DATETIME)
Or is there some other magical incantation that is supposed to be used (without me manually appending the time portion)?