日期时间列的平均值

Question

I have the following table with a datetime column:

id | datetime 
1  | 2014-12-01 20:00:00
2  | 2014-12-03 12:04:00
3  | 2014-12-04 10:15:00
4  | 2014-12-05 13:45:00
.. | ...etc

First I select only the hour & minute of the datetime field, but I don't know how to select the average on a time field:

SELECT TIME_FORMAT(datetime, '%H:%i') as time FROM table_x;

The end result should be the average time in HH:SS format.

Answer 1

What you're asking doesn't make sense. Here's why.

Average, in the sense of the arithmetic mean, is calculated by dividing the sum of the values by the number of values. What's the sum of '2014-01-01 08:00 am' and '2014-01-01 09:00 am'? This is literally nonsense; you can't add 9:00 am to 8:00 am.

Both the median and the mode make sense as a measurement of central tendency for values of type DateTime. But average does not.

Other platforms with better type support make it clearer. Take PostgreSQL, for example.

sandbox=# create table test (ts timestamp with time zone);
CREATE TABLE
sandbox=# insert into test values ('2014-01-01 08:00');
INSERT 0 1
sandbox=# insert into test values ('2014-01-01 09:00');
INSERT 0 1
sandbox=# select avg(ts) from test;
ERROR:  function avg(timestamp with time zone) does not exist
LINE 1: select avg(ts) from test;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
sandbox=# select sum(ts) from test;
ERROR:  function sum(timestamp with time zone) does not exist
LINE 1: select sum(ts) from test;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Often, people using MySQL are trying to express a time interval using a DateTime data type. This can be confusing, because both intervals and points in time use similar notation. For example, the value '08:00' might mean "eight hours" if you interpret it as an interval. It might mean "eight o'clock am" if you interpret it as a point in time.

MySQL supports point-in-time data types. It does not support SQL intervals. But PostgreSQL does, and here's how it differs.

sandbox=# create table test (duration interval);
CREATE TABLE
sandbox=# insert into test values ('15 hours');
INSERT 0 1
sandbox=# insert into test values ('16 hours');
INSERT 0 1
sandbox=# select sum(duration) from test;
   sum
----------
 31:00:00
(1 row)

sandbox=# select avg(duration) from test;
   avg
----------
 15:30:00
(1 row)

The distinction: while it doesn't make sense to add or average 8 o'clock and 9 o'clock, it does make sense to add and average 8 hours and 9 hours.

If you want to store something like SQL intervals in MySQL, store them as integers meaning minutes or seconds (or whatever), and do arithmetic on the integers.