Query the average of two column values (TytStamp) in MySQL 5.7

1. There are two column values, which are the creation time and the update time, which need to be counted in all records, and the average number of the two column values is subtracted. as shown in Figure 1

Figure 1

2. If the column type is TIMESTAMP, then you need to convert it to a timestamp for operation. The timestamp can be converted to seconds using the UNIX_TIMESTAMP function. Only 2 records are queried first to compare the results with manual calculations. SQL is as follows, the comparison results are equal, and the result is equal to 47. as shown in Figure 2

Figure 2

id created_at updated_at                   差值
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);

3. In the final statistics, you can save the WHERE condition, or adjust the WHERE condition. The result is as expected. as shown in Figure 3

Figure 3