How to get average of DATETIME column in MySQL while grouping by other columns

Question

I am creating and populating a MySQL table using the following query:

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 populate the "avg_time" column with the average of the "time" column grouped by id. For example, the first row has 'id' = 1, so the 'avg_time' column should take the average of the first, second, and fifth rows since their 'id' is also 1.

How should I answer this question?

Edit: To clarify, I want to convert all the DATETIME columns to milliseconds, add them all up, and then divide by the added number. For example, for id=2, converting them to milliseconds, adding them and then dividing by 2 (since there are two rows with "id" = 2) gives the average for March 17, 2023 at 5:45:00 PM. I want to get the average this way.

Answer 1

Use this UNIX_TIMESTAMP to convert to seconds, then use FROM_UNIXTIME to get it back to datetime format, this is how you get the average:

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

This is how to fill in 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;