List:General Discussion« Previous MessageNext Message »
From:hsv Date:May 24 2013 6:07pm
Subject:Re: Bug in BETWEEN same DATETIME
View as plain text  
>>>> 2013/05/24 09:49 -0400, shawn green >>>>
Or we could coerce datetime values back to their date values when both are being used. The
trick now becomes choosing between rounding the datetime value (times past noon round to
the next date) or do we use the floor() function all the time.
<<<<<<<<
This is simply wrong. Timestamps are not numbers: we do not add timestamps, and when we
subtract them we do not consider the difference something of the same type. Therefore,
one does well to be wary when applying to a timestamp the notion "rounding".

But containment generally applys: an event on MAY 25th from 1pm to 4pm is within May 25th,
which is within May, .... When containment fails, then there is trouble: what is the first
weekend of August? or the first week of August? better to say, the weekend or week of
August 1st, or 2d, or ...; "day" is a "common divisor" to calendar-month, weekend, and
week.

Therefore, when I learnt that in version 4 MySQL had gone from interpreting a comparison
between DATE and a finer timestamp by the DATE to interpreting it by the finer timestamp
I believed that MySQL was going the wrong way--that MySQL had gone from a realization of
an intuitive sense of containing, as above, to one on which too much thought had been
expended, with a loss of intuitive sense.

I consider the change of 2013/5/25-13 to 2013/5/25 to be truncation, not any sort of
rounding; that is, it is a matter of notation, but one which intuitivly expresses
containment.

These notions sometimes change over the years, and by nation. When the first public
striking clock was set up in Milan, it pointed to hours I through XXIV, with sunset
falling within the 24th hour--that is, the 24th hour ends with 24 o'clock s being struck.
This persists to this day in the German expression "viertel sechs", which means that the
sixth hour is one-fourth over, or, as we would say it, "quarter after five". (Like
expressions are found amongst the Germans s neighbors, but in English never took root.)
Nowadays we are are more inclined to associate both "quarter after five" and "quarter to
six" ("dreiviertel sechs") with 5 o'clock than 6 o'clock; this accompanies the change of
notation from 1 through 24 to 0 through 23.

I find MySQL s automatic conversion sometimes to be downright screwy; (version 5.5.8)
consider "SELECT NULL" and "SELECT NULL UNION SELECT NULL"; in one of my views there is a
complex wholly numeric expression that becomes "varbinary(32)".

Thread
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