On 22 Nov 2002, at 12:43, Hans Zaunere wrote: > I know all about cacheing and all, but I'm just frustrated > by this DATETIME column issue, after switching from the fantastic INT column > and UNIX timestamp scheme.
If using a Unix timestamp was fantastic, why did you move away from it? (And surely that meant using a range query also.) Or are you being sarcastic or using a different meaning of "fantastic"? > I guess what throws me is this from > http://www.mysql.com/doc/en/Comparison_Operators.html > > "If one of the arguments is a TIMESTAMP or DATETIME column and the other > argument is a constant, the constant is converted to a timestamp before the > comparison is performed. This is done to be more ODBC-friendly." > > ODBC or not, this is very important feature of a DATE-centric column, > otherwise, what's the sense of a special column, except a larger range than > an int (which could be circumvented anyway). > > And since MySQL is doing the conversion as noted above, which is good, > there's no reason it shouldn't recognize that "2002-11-27" means a "wildcard" > or "unimportant" time, and only pay attention to the date part. Even if it recognized that, what good would it do if it doesn't have a separate date-only index to use? Are you suggesting that the index for a DATETIME column should automatically include an index by date alone as well? > Is there a "feature request" type of list or forum where one can talk > directly to developers, etc. ? Well, there's [EMAIL PROTECTED], but I don't know how happy they are with feature requests. > > Yes, it might be nice if you could do something like "WHERE > > DATE_PART(thecolumn) = '2002-11-21'" and have MySQL use the index, > > but it would be doing exactly the same thing it does now when you say > > "WHERE thecolumn BETWEEN '2002-11-21 00:00:00' AND '2002-11-21 > > 23:59:59'". There wouldn't be any difference in performance, just a > > different query syntax. > > > > But maybe I'm still misunderstanding your problem. > > Again, no - it's not just syntactic sugar. A range comparison isn't as fast, > or the same, as a direct comparison, ever! It seems to me that it is syntactic sugar unless you have an additional date-only index. It's true that a range comparison isn't as fast as a direct comparison, but I don't see how you can do what you want with a direct comparison, any more than you could get all matches for any other column prefix. It seems to require a separate date-only index, which could be somehow built into the DATETIME index, I suppose. Is that what you're saying Oracle does? -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
