List:General Discussion« Previous MessageNext Message »
From:shawn green Date:May 24 2013 7:23pm
Subject:Re: Bug in BETWEEN same DATETIME
View as plain text  
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.

> 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:

Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Bug in BETWEEN same DATETIMEDaevid Vincent23 May
  • RE: Bug in BETWEEN same DATETIMETimothy R Peterson23 May
    • Re: Bug in BETWEEN same DATETIMEMichael Dykman23 May
      • RE: Bug in BETWEEN same DATETIMERick James23 May
  • Re: Bug in BETWEEN same DATETIMEAndrew Moore23 May
    • Re: Bug in BETWEEN same DATETIMEAndrew Moore23 May
  • Re: Bug in BETWEEN same DATETIMEshawn green23 May
    • RE: Bug in BETWEEN same DATETIMERick James23 May
      • Re: Bug in BETWEEN same DATETIMEshawn green24 May
        • RE: Bug in BETWEEN same DATETIMERick James24 May
          • Re: Bug in BETWEEN same DATETIMEshawn green24 May
        • Re: Bug in BETWEEN same DATETIMEhsv24 May
          • RE: Bug in BETWEEN same DATETIMERick James29 May