Averaging multiple dates in mysql-CodePudding

I have 5-6 dates and I want to average them. How can I do it?

Example Data:

2021-11-03 12:00:00
2021-11-04 12:28:00
2021-11-05 11:57:00
2021-11-06 12:16:00
2021-11-07 12:08:00
2021-11-08 12:27:00

I want to average the hours here.

(12:00:00 12:28:00 11:57:00 12:16:00 12:08:00 12:27:00)/6 = 12:12:40 (Average)

Select avg(date_) from table; is not working.

CodePudding user response：

Guess this is something expected

select 
 sec_to_time(avg(time_to_sec(time(time_value)))) time_only_avg, 
 from_unixtime(avg(unix_timestamp(time_value))) date_time_avg
from times

data

create table times (
  time_value datetime
);
insert into times values 
('2021-11-03 12:00:00'),
('2021-11-04 12:28:00'),
('2021-11-05 11:57:00'),
('2021-11-06 12:16:00'),
('2021-11-07 12:08:00'),
('2021-11-08 12:27:00');

CodePudding user response：

To get the average time:

SELECT 
    SEC_TO_TIME(
        (
            TIME_TO_SEC(TIME("2021-11-03 12:00:00"))   
            TIME_TO_SEC(TIME("2021-11-04 12:28:00"))   
            TIME_TO_SEC(TIME("2021-11-05 11:57:00"))   
            TIME_TO_SEC(TIME("2021-11-06 12:16:00"))   
            TIME_TO_SEC(TIME("2021-11-07 12:08:00"))   
            TIME_TO_SEC(TIME("2021-11-08 12:27:00"))
        ) / 6
    ) as result 
;

Result

12:12:40.0000