Re: avg and timestamp/datetime

[James Nobis]

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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]