I think that's because those databases render datetime values into their "seconds from epoch" values (whichever epoch they use) when casting to a numeric type (I know MS SQL does. I assume the others do, too.) It seems that MySQL is the oddball on this issue.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine James Nobis <[EMAIL PROTECTED]> wrote on 04/12/2005 04:28:59 PM: > Thanks for the detailed explanation which is what I assumed was > happening. Does > it make any sense though? Shouldn't we get some sort of warning for the > implicit cast? I could be wrong but I thought PostgreSQL, Oracle, and SQL > server handled avg(date/time values) correctly. > > Quoting [EMAIL PROTECTED]: > > > James Nobis <[EMAIL PROTECTED]> wrote on 04/12/2005 > > 10:19:33 AM: > > > >> Hi all, > >> > >> Essentially I was asked to look into a problem with a query at the > >> office which > >> used AVG on a datetime column which produces incorrect results. (MySQL > > 4.1.10 > >> on RHEL 3 update 4) As you can see below the stark difference between > > the > >> correct and incorrect results. Though, the incorrect result has the > > correct > >> year (this seems actually to be coincidence and nothing more) but I > >> am not sure > >> exactly what MySQL did for casting. The query produces no errors > > orwarnings. > >> I *think* the incorrect result is an "internal" timestamp format but > > with the > >> wrong data. Is there a part of the manual that I missed which explains > > this > >> unexpected behavior? > >> > >> AVG(col) v FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(col))) > >> 20050258324839 v 2005-03-24 02:05:11 > >> > >> James Nobis > >> Web Developer > >> Academic Superstore > >> 223 W. Anderson Ln. Suite A110, Austin, TX 78752 > >> Voice: (512) 450-1199 x453 Fax: (512) 450-0263 > >> http://www.academicsuperstore.com > >> > >> ---------------------------------------------------------------- > >> This message was sent using IMP, the Internet Messaging Program. > >> > > > > I can't find it in the online manual but it used to explain that datetime > > values will be cast as a number in the form of YYYYMMDDHHNNSS or as a > > string like 'YYYY-MM-DD HH:NN:SS'. AVG() is a numeric function so if you > > are averaging datetime values, they will appear in their numeric format. > > > > As an example, if you needed the AVG() of the times '2005-04-30 12:55:59' > > and '2005-04-30 12:56:01' you would have wound up averaging the following > > numbers: > > > > 20050430125559 > > 20050430125601 > > > > Which does not numerically average out to an numeric representaion of > > '2005-04-30 12:56:00' but instead to 20050430125680 which is the numeric > > form of the time '2005-04-30 12:55:80'. However, by first converting the > > dates to "seconds from epoch" (by using UNIX_TIMESTAMP()) you take the > > average of the numbers > > > > 1114880159 > > 1114880161 > > > > which works out to 1114880160. Run that value through FROM_UNIXTIME() and > > you get: > > > > select from_unixtime(1114880160); > > +---------------------------+ > > | from_unixtime(1114880160) | > > +---------------------------+ > > | 2005-04-30 12:56:00 | > > +---------------------------+ > > 1 row in set (0.00 sec) > > > > Which is exactly what your test revealed. Basically, you got hosed results > > for your first column because the AVG() and SUM() aggregate functions do > > not work with dates or times in a format that is conducive to proper > > "date" math. They use the human-readable version of the date(a packed > > number) and not a machine-useful, mathematically sound value like the > > date's offset from epoch. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > > > > > James Nobis > Web Developer > Academic Superstore > 223 W. Anderson Ln. Suite A110, Austin, TX 78752 > Voice: (512) 450-1199 x453 Fax: (512) 450-0263 > http://www.academicsuperstore.com >
