How to get average of DATETIME column in MySQL while grouping by some other column-CodePudding

I am creating and filling a MySQL table with the following Queries:

SET time_zone = ' 00:00';

CREATE TABLE timestamp_n_datetime (
    id INT,
    time DATETIME,
    avg_time DATETIME
);

INSERT INTO timestamp_n_datetime(id,time,avg_time)
VALUES("1","2023-01-03 10:12:13", NULL),
("1", "2023-02-04 11:13:14", NULL),
("2", "2023-03-02 09:14:10", NULL),
("2", "2023-04-02 05:15:50", NULL),
("1", "2023-06-10 02:04:10", NULL);

I want to fill the 'avg_time' column with the average of 'time' column grouped by the id. For example the first row has 'id' = 1 so the 'avg_time' column should take the average of first, second and fifth row since their 'id' is also 1.

How could I approach this question?

Edit: For clarification I want all the DATETIME columns to be converted to miliseconds, adding all of them up and then dividing by the number added. For example For id=2 converting them to miliseconds adding them up and then dividing by 2 (because there are two rows with 'id' = 2) gives the average of March 17, 2023 5:45:00 PM. I want to get an average this way.

CodePudding user response：

Using This UNIX_TIMESTAMP to convert to seconds, then FROM_UNIXTIME to get it back to datetime format, this is how you can get the average :

    select id, FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(`time`)))
    from timestamp_n_datetime
    group by id;

This is how to fill the average :

update timestamp_n_datetime  td
inner join (
    select id, FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(`time`))) as average
    from timestamp_n_datetime
    group by id
) as s on s.id = td.id
set avg_time = s.average;