Average of datetime data type

Question

I am trying to calculate the average of a few rows with a datetime data type (standard datetime format).
How can I do that?

Answer 1

Convert the datetime to a float. The SQL standard defines that as the number of days since 1900, so it should be fairly portable. For example:

declare @t table (dt datetime)
insert @t select '1950-01-01'
union all select '1960-01-01'

select cast(avg(cast(dt as float)) as datetime) from @t

This result is1955-01-01. Example at SE Data.

Answer 2

This is how to get the average of a DateTime column in MySql:

create temporary table table_1 (
    aDate DateTime
);

insert into table_1 values
    ('2000-01-01 00:00:00'),
    ('2010-01-01 00:00:00');

select CAST(avg(aDate) as DateTime) from table_1;
-- Result: "2005-01-01 00:00:00"

Answer 3

In PostgreSQL you could:

SELECT to_timestamp(avg(EXTRACT(EPOCH FROM my_timestamp)))
  FROM my_tbl;

More info in the fine manual here.