1

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
Cœur
  • 37,241
  • 25
  • 195
  • 267
Sas
  • 2,473
  • 6
  • 30
  • 47
  • cast to time, then average that – JohnLBevan Apr 10 '15 at 14:58
  • 1
    Average is defined as the sum of the items divided by the count, so why isn't that what you want? But DATE_FORMAT will make it a string, so that's probably the problem? Try `avg(time(StartDate))` – Sami Kuhmonen Apr 10 '15 at 14:59

4 Answers4

3

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.
JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
2

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.

Community
  • 1
  • 1
Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
0

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

Davide Lorenzo MARINO
  • 26,420
  • 4
  • 39
  • 56
  • NB: that'll average the dates as well as the times; so if you had 8am on 1st Jan 2015 and 8:02am on 2nd Jan, rather than getting 8:01am you'd get 8:01pm! – JohnLBevan Apr 10 '15 at 15:05
0

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

54l3d
  • 3,913
  • 4
  • 32
  • 58
  • NB: that'll average the dates as well as the times; so though your times are all around 3:45, the average time's 7:45 :S. – JohnLBevan Apr 10 '15 at 15:11