paul@stripped wrote on 06/10/2005 10:21:51 PM:
<snip>
> # This is a BitKeeper patch. What follows are the unified diffs for the
> # set of deltas contained in the patch. The rest of the patch, the part
> # that BitKeeper cares about, is below these diffs.
> # User: paul
> # Host: frost.snake.net
> # Root: /Volumes/frost2/MySQL/bk/mysqldoc
> --- 1.2975/Docs/manual.texi 2005-06-10 20:52:32 -05:00
> +++ 1.2979/Docs/manual.texi 2005-06-10 21:20:35 -05:00
> @@ -97919,6 +97919,35 @@
> string containing no separators (such as @code{'20040815'}), provided it
> makes sense as a date.
>
> +When you compare a @code{DATE}, @code{TIME}, @code{DATETIME}, or
> +@code{TIMESTAMP} to a constant string with the @code{<}, @code{<=},
> +@code{=}, @code{>=}, @code{>}, or @code{BETWEEN} operators, MySQL
normally
> +converts the string to an internal long integer for faster comparision
(and
> +also for a bit more ``relaxed'' string checking).
> +However, this conversion is subject to the following exceptions:
> +
> +@itemize @bullet
> +
> +@item
> +When you compare two columns
> +
> +@item
> +When you compare a @code{DATE}, @code{TIME}, @code{DATETIME}, or
> +@code{TIMESTAMP} column to an expression
> +
> +@item
> +When you use any other comparison method than those just listed, such
as
> +@code{IN} or @code{STRCMP()}.
> +
> +@end itemize
> +
> +For these exceptional cases, the comparison is done by converting the
> +objects to strings and performing a string comparison.
> +
> +To keep things safe, assume that strings are compared as strings and
use the
> +appropriate string functions if you want to compare a temporal value to
a
> +string.
> +
This would be excellent advice if I could predict what the STRING format
looked like. Can you provide examples of BOTH comparisons (the internal
LONG INT and the STRING comparisons)? That way we have a more _concrete_
definition of what is going on. When you compute the LONG INT version of
the date, is it the "packed string" version of the value or a "number of
time-intervals since EPOCH" value. Which form you use can make a huge
difference (especially if we are trying to do date math: how much time
elapsed between event1 and event2? Use that difference in seconds in the
report.)
Now, I know that last example requires the use of UNIX_TIMESTAMP() with
our current implementation. I am just absolutely curious: Why can't dates
be stored AS their UNIX_TIMESTAMP() values so that they are always LONG
INTs? Wouldn't that save a lot of extra time doing the "date math"
functions and render the need to explain the difference between the
different comparison methods moot?
If necessary, use a LONG INT for the date and a regular INT for the time
(to the nearest 1/10th of a second) or a LONG INT for time (to the nearest
1/10000th of a second). Some of the remaining higher order bits in the
TIME field could be used to encode time zone information.
Even with the timezone information in the TIME portion, you only need to
do an AND before making the comparison to mask out those bits. Once you
have cleared them (and possibly adjusted with them), the time-math becomes
academic.
I think the point of my rant (and I know I have rambled) is that it's
frustrating to learn rules and exceptions to rules for something as simple
as date and time comparisons. Can you , the development team, not find a
consistent method of dealing with this, IMHO, basic datatype?
<snip>
Sincerely,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
