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.
解决方案
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.