Search code examples
mysqlsqldatetimeunix-timestamp

YEAR(AVG(UNIX(date))) different from ROUND(AVG(YEAR(date)))

Summarizing a practice question:
I need to query the average year from a datetime column. My initial solution was to YEAR(AVG()) all dates. But since I can't AVG() a datetime, I convert the dates to unix, then back to datetime:

SELECT 
    YEAR(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(date1))))
FROM table1;

Which returns 1980

The correct query is:

SELECT 
    ROUND(AVG(YEAR(date1)))
FROM table1;

Which returns 1960
The second query is better for clear reasons, but why are the results different?

Solution

  • Seems like your data contains dates earlier than 1970-01-01. The UNIX_TIMESTAMP() function returns 0 for dates earlier than the epoch:

    SELECT UNIX_TIMESTAMP('1969-12-31')
-- 0

    Hence the result for first query is biased as it does not count < 1970 dates properly.