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

thinking outloud you could do a datediff in minutes from a set time, average that and then add those minutes back to the set time...

Answer 2

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 3

AVG is a grouping function, which means it will sum all the rows in the table and divide by the row count. But it sounds like you want the average of two different columns, reported individually for each row. In that case, you should just compute it yourself: (date1+date2)/2. (MySQL may want some extra syntax to add those columns properly.)

The code you've written will give you the table's average elapsed time between date1 and date2.

Answer 4

SELECT date_one + (date_two - date_one) / 2 AS average_date
FROM thetable
WHERE whatever

You can't sum dates, but you can subtract them and get a time interval that you can halve and add back to the first date.

Answer 5

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