CODEWITHSUNDEEP
sqldatetimeaggregate-functions
alienavatar
alienavatar

Reputation: 299

Average of datetime data type

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

Upvotes: 6

Views: 8011

Answers (3)

Andomar
Andomar

Reputation: 238058

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.

Upvotes: 9

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

In PostgreSQL you could:

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

More info in the fine manual here.

Upvotes: 2

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

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"

Upvotes: 2

Related Questions