List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 29 2013 4:59pm
Subject:RE: Bug in BETWEEN same DATETIME
View as plain text  
(To ramble in a slightly different direction...)

I claim that the world gained half a second when we went from "round time" to "square
time" a few decades ago.  Before then, announcers on radio/tv would look at their
round-shape analog clock to see what time it was; they would perform a ROUND() function
before announcing the time.  Now they look at their square-shaped digital clock and
perform FLOOR().  So, what you hear on radio/tv is half a second behind what you used to
hear.   ;)

> -----Original Message-----
> From: hsv@stripped [mailto:hsv@stripped]
> Sent: Friday, May 24, 2013 11:08 AM
> To: mysql@stripped
> Subject: Re: Bug in BETWEEN same DATETIME
> >>>> 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)".
> --
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

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