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
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'
As it appears that in the first instance it defaults the time to 00:00:00 always, as verified by this:
WHERE `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
WHERE `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?
WHERE `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)?
May 23
Peterson, Timothy R RE: Bug in BETWEEN same DATETIME
May 23, 2013; 16:07
Peterson, Timothy R
RE: Bug in BETWEEN same DATETIME
May 23
Andrew Moore Re: Bug in BETWEEN same DATETIME
May 23, 2013; 22:16
Andrew Moore
Re: Bug in BETWEEN same DATETIME
May 23
Andrew Moore Re: Bug in BETWEEN same DATETIME
May 23, 2013; 22:25
Andrew Moore
Re: Bug in BETWEEN same DATETIME
May 23
Michael Dykman Re: Bug in BETWEEN same DATETIME
May 23, 2013; 17:56
Michael Dykman
Re: Bug in BETWEEN same DATETIME
May 23
Rick James RE: Bug in BETWEEN same DATETIME
May 23, 2013; 22:41
Rick James
RE: Bug in BETWEEN same DATETIME
May 23
shawn green Re: Bug in BETWEEN same DATETIME
May 23, 2013; 18:50
shawn green
Re: Bug in BETWEEN same DATETIME
May 23
Rick James RE: Bug in BETWEEN same DATETIME
May 23, 2013; 23:08
Rick James
RE: Bug in BETWEEN same DATETIME
May 24
shawn green Re: Bug in BETWEEN same DATETIME
May 24, 2013; 09:49
shawn green
Re: Bug in BETWEEN same DATETIME
May 24
Rick James RE: Bug in BETWEEN same DATETIME
May 24, 2013; 16:17
Rick James
RE: Bug in BETWEEN same DATETIME
May 24
shawn green Re: Bug in BETWEEN same DATETIME
May 24, 2013; 15:23
shawn green
Re: Bug in BETWEEN same DATETIME
May 24
hsv Re: Bug in BETWEEN same DATETIME
May 24, 2013; 14:07
hsv
Re: Bug in BETWEEN same DATETIME
May 29
Rick James RE: Bug in BETWEEN same DATETIME
May 29, 2013; 16:59
Rick James
RE: Bug in BETWEEN same DATETIME
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!