Calculate the Average Difference Between Two Timestamp Columns in MySQL 5.7

1. There are two columns of values, representing the creation time and the update time. I need to calculate the average of the differences between these two columns across all records. See Figure 1.

2. If the column type is timestamp, it must be converted to a timestamp for calculation. You can use the UNIX_TIMESTAMP function to convert the timestamp into seconds. First, query only 2 records to compare the result with manual calculation. The SQL is as follows; the comparison results are equal, and the result is 47. See Figure 2.

id created_at updated_at                   difference
45 2022-12-06 10:19:48 2022-12-06 10:20:36 48 
46 2022-12-13 08:22:45 2022-12-13 08:23:31 46

id created_at updated_at                   difference
45 2022-12-06 10:19:48 2022-12-06 10:20:36 48 
46 2022-12-13 08:22:45 2022-12-13 08:23:31 46

SELECT AVG(UNIX_TIMESTAMP(`updated_at`) - UNIX_TIMESTAMP(`created_at`)) AS avg_diff FROM `table` WHERE `id` IN (45,46);

SELECT AVG(UNIX_TIMESTAMP(`updated_at`) - UNIX_TIMESTAMP(`created_at`)) AS avg_diff FROM `table` WHERE `id` IN (45,46);

3. For the final calculation, you can omit the where condition or adjust it. The result meets expectations. See Figure 3.