How can I make an average of dates in MySQL?

Question

How can I make an average between dates in MySQL? I am more interested in the time values, hours and minutes.

On a table with:

| date_one   | datetime | | date_two   | datetime | 

Doing a query like:

 SELECT AVG(date_one-date_two) FROM some_table WHERE some-restriction-applies; 

---

Edit:

The AVG(date1-date2) works but I have no clue what data it is returning.

Answer 1

This seems a bit hackish, but will work for dates beteen ~ 1970 and 2030 (on 32 bit arch). You are essentially converting the datetime values to integer, averaging them, and converting the average back to a datetime value.

SELECT     from_unixtime(         avg(             unix_timestamp(date_one)-unix_timestamp(date_two)         )     ) FROM     some_table WHERE     some-restriction-applies 

There is likely a better solution out there, but this will get you by in a pinch.

Answer 2

select avg(datediff(date1,date2)) select avg(timediff(datetime,datetime))