(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: h...@tbbs.net [mailto:h...@tbbs.net] > Sent: Friday, May 24, 2013 11:08 AM > To: mysql@lists.mysql.com > 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: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql