How can I make an average of dates in MySQL?

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.

Solution 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.

Solution 2:

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

Solution 3:

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.

Solution 4:

SELECT TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE, '2011-02-12 10:00:00', '2011-02-12 12:00:00')/2, '2011-02-12 10:00:00')

The result is '2011-02-12 11:00:00'

Solution 5:

CREATE TABLE `some_table`
(
  `some_table_key` INT(11) NOT NULL AUTO_INCREMENT,
  `group_name` VARCHAR(128) NOT NULL,
  `start` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `finish` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (`some_table_key`)
);

SELECT
    group_name,
    COUNT(*) AS entries,
    SEC_TO_TIME( AVG( TIME_TO_SEC( TIMEDIFF(finish, start) ) ) ) AS average_time
FROM some_table
GROUP BY
    some_table.group_name
;

You should always specify the group you want when using group functions, you can end up in some nasty messes with the group functions if you later extend queries with JOIN etc and assume MySql will choose the right group for you.