How do I calculate an average date in SQL? [duplicate]

Question

I have a set of rows with a MySQL datetime field, and I want to calculate the average date on a subset of them. SQL WHERE clauses are easy, but how does one go from a datetime column to an average date?

The field is called Created, and when I try a naive approach, I get some pretty odd results:

SELECT AVG(Date(Created)) FROM table WHERE BLAH:

+---------------------------+
| AVG(Date(Created))        |
+---------------------------+
|   20140599.8857143        | 
+---------------------------+

I'd prefer output that that returned something like 2014-05-31, but DATE() on the results of AVG() just give me NULL. How do I get an intelligible query result out of SQL?

Answer 1

Try:

SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(created))) FROM table