Average data type data time

Question

I am trying to calculate the average of several rows with a datetime data type (standard date and time format).
How can i do this?

Answer 1

Convert date and time to number with float . The SQL standard defines this as the number of days since 1900, so it should be fairly portable. For instance:

 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 is 1955-01-01 . Example on SE data.

Answer 2

Here's how to get the average value 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 can:

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

Read more about the great guide here .