MySQL Bugs: #23140: Explicit support of datetime type for aggregate functions like AVG()

Bug #23140	Explicit support of datetime type for aggregate functions like AVG()
Submitted:	10 Oct 2006 14:33	Modified:	10 Oct 2006 17:07
Reporter:	Benjamin Root	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0	OS:	Any (All)
Assigned to:		CPU Architecture:	Any
Tags:	avg, datetime, unix_timestamp

Description:
This is a resubmission of bug report 23036 as a feature request.

I maintain a historical database of weather event reports, like tornado reports or hail reports.  These reports extend well before 1950 on up to present day.  Often there would be several reports for the same event, for example, several reports over several days for a tornado outbreak or several observations over multiple days for a heat wave.  I have these records indexed by events, but I can't correctly determin the average datetime when I group these records by events.

Since the dates go before 1970, I can't use UNIX_TIMESTAMP in order to average the time since Epoch.  And calculating the seconds since Epoch myself using Perl or C\C++ and storing that doesn't help me since I can't do datetime searches like finding all tornados that occurred before 3 PM.

Is there a way to deal with this effectively in historical databases already, or do we need to expand support for functions like AVG()?

How to repeat:
nothing to repeat since this is a feature request.

as a workaround (at some expense)

select 
 from_days(
   avg(  to_days( date_column )*86400 + time_to_sec(time( date_column )) )
 ),
 sec_to_time(
    mod(
      avg(  to_days( date_column )*86400 + time_to_sec(time( date_column )) ),
      86400
    )
  )

returns the correct date and time, at least for dates AC

BUT, you need to calculate the avg twice....

if you dont use join, you can assign the result to a variable, and re-use it from there.

tiny mistake:

 from_days(
   avg(  to_days( date_column )*86400 + time_to_sec(time( date_column )) ) / 86400
 ),

dont miss the division at the end of line

works from year 200 onwards see bug #20577

and with exception for possible oddities with the non existent days in the Gregorian calendar (1582), see http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

ugh, I figured I might have to do a work-around like that.  Seems a little bit better than my previous attempt of subtracting the minimum date (yielding positive dates), averaging them, and then adding the minimum date back on.  Turns out it had exponental complexity.

Anyway, I think I will have to use this work-around for now, but my original request still stands.  An AVG function that will work for datetime, because a lot of the complexity of the query can be eliminated when done internally in the code, making the query significantly more efficient.

Thanks for the code snippet, Martin!