Hi Rick,
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
support them.
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.
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html
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:
http://dev.mysql.com/doc/refman/5.6/en/datetime.html
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
