CODEWITHSUNDEEP
mysqlsqldate
Sas
Sas

Reputation: 2503

How to find average on a timestamp column

I need to find out the average time in which an event starts. The start time is recorded in DB in startDate column.

|    StartDate        |
|=====================|
|2015/04/10 3:46:07 AM|
|2015/04/09 3:47:37 AM|
|2015/04/08 3:48:07 AM
|2015/04/07 3:43:44 AM|
|2015/04/06 3:39:08 AM|
|2015/04/03 3:47:50 AM|

So what I need is calculate the average time (hh:MM:ss) the event starts daily.

I am not quite sure how to approach this. Below query won't work coz it just sums up and divide the total value by total number:

SELECT AVG(DATE_FORMAT(StartDate,'%r')) FROM MyTable

Upvotes: 1

Views: 13939

Answers (4)

JohnLBevan
JohnLBevan

Reputation: 24410

Demo: http://sqlfiddle.com/#!9/33c09/3

Statement:

select TIME_FORMAT(avg(cast(startDate as time)),'%h:%i:%s %p') as avg_start_date
from demo

Setup:

create table demo (startDate datetime);

insert demo (startDate) values ('2015-04-10 3:46:07');
insert demo (startDate) values ('2015-04-09 3:47:37');
insert demo (startDate) values ('2015-04-08 3:48:07');
insert demo (startDate) values ('2015-04-07 3:43:44');
insert demo (startDate) values ('2015-04-06 3:39:08');
insert demo (startDate) values ('2015-04-03 3:47:50');

Explanation:

  • Casting to Time ensures the Date component is ignored (if you're averaging datetimes and hoping for an average time it's like averaging double figure numbers and hoping for the unit to match what you'd have seen if you'd only averaged the units of those numbers).
  • AVG is the average function you're already familiar with.
  • TIME_FORMAT is to present your data in a user friendly way so you can check your results.

Upvotes: 3

54l3d
54l3d

Reputation: 3973

You can convert the timestamp to unixtime, calculate the average then turn back to timestamp format:

>select FROM_UNIXTIME((sum(UNIX_TIMESTAMP(tstamp))/count(*))) from test
April, 07 2015 07:45:25

Here is the example with your data: timestamp column average

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Firstly you have to extract your datetime format to contain only time so that you can use an aggregate function on it. This is done by casting it to type TIME. Then you can use built-in aggregate function AVG() to achieve your goal and grouping to get average start date for every day you have in your table.

This is done by

SELECT AVG(StartDate::TIME) FROM MyTable

To get average starting time per day simply

SELECT StartDate::DATE, AVG(StartDate::TIME) FROM MyTable GROUP BY StartDate::DATE

And if you also wish to have days that have no events starting in that time check here for equivalent to generate_series which is not an option in MySQL.

Upvotes: 2

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26926

Convert it to milliseconds apply the AVG function and go back to date

Upvotes: 0

Related Questions