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), that is equivalent to a 1-second DATETIME. I also assume midnight
> belongs to the day that it is the _start_ of.
>> There is also a strong desire to make a database server try to do exactly what
> the user tells it to do.
> That is difficult, because of definitions and representation.
> A common problem is comparing a FLOAT value to a 'decimal' value like 1.23. MySQL
> does a good job of covering some cases, but there are still cases between DECIMAL, FLOAT,
> DOUBLE, and literals that will register as inequality, to the surprise of the user.
> I see the DATE problem as another thing where the user needs to understand the
> computer's algorithm, which, as Shawn points out is:
> "We do one thing (make dates represent midnight on that date when they need to be
> compared to datetime values) and allow the users to decide how to handle the rest of the
> comparison according to their specific needs."
>>> WHERE datetime_col >= '2013-01-01'
>>> AND datetime_col < '2013-01-01' + INTERVAL 1 DAY
>> but for predictability and reliability, this is one rewrite that may not always
> be true.
> So, to be safe, one should perhaps say:
> WHERE datetime_col >= '2013-01-01 00:00:00'
> AND datetime_col < '2013-01-01 00:00:00' + INTERVAL 1 DAY
> IN_DATE (or maybe "ON_DAY") is an interesting idea. I assume it would be
> transliterated by the parser into something like the expression above, then optimized
> based on which part(s) are columns and which are literals.
>> '2013-05-14 17:00:00' >= '2013-01-01'
>> AND '2013-05-14 17:00:00' <= '2013-05-14' + INTERVAL 12 HOUR
> There's an extra second in that! (I call it the "midnight bug".)
It includes the extra second only because your second comparison is
using "<=" and not just "<"
> I perceive (rightly or wrongly) that comparing a TIMESTAMP to something first
> converts the TIMESTAMP value to a string ('2013-...'). Shawn, perhaps this statement
> belongs as part of the 'algorithm' explanation?
Sort of. TIMESTAMP is not a universal constant (as you say later); it is
timezone-aware. This causes all sorts of problems when the UTC value it
keeps internally is used for date-literal comparisons on or about the
time of Daylight Saving Time changes happen in the timezones that
During the 'spring forward' period (which depends on hemisphere) there
will be a gap of times that do not exist. The clock shifts from
"01:59.59.999999" to "03:00:00". During the 'fall back' period, the
values will repeat the "02:00:00" to "02:59:59.999999" range. Some
queries will return multiple rows.
To be timezone agnostic, always store date values relative to UTC or
store them as integer values using the FROM_UNIXTIME() and
UNIX_TIMESTAMP() functions. Then convert the absolute time to a local
timezone during presentation.
> Yes, you might get in trouble if the same SELECT were run in two different timezones
> at the same time. Or, TIMESTAMP might help you get the "right" answer.
> There are something like 5 different "datetime" concepts. MySQL covers 2 of them.
> DATETIME is a picture of _your_ clock.
> TIMESTAMP is an instant in the _universe_.
> For these, and others, think of a recurring event on a calendar, a sporting event, an
> appointment (potentially in a diff timezone), train schedule, etc.
For an explanation between the DATETIME and TIMESTAMP data types, I
encourage the others on this list to review:
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN