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
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
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
Need long-term technical maintenance or remote troubleshooting?
I am a PHP / Go backend engineer with 15+ years of experience, focused on existing system maintenance, bug fixing, performance optimization, server troubleshooting, WordPress maintenance, and small feature iterations.
If your project is facing any of the following issues, we can start with a small troubleshooting task first:
- ✅ PHP / Laravel / Yii2 legacy systems without active maintenance
- ✅ Go / Gin backend APIs that need troubleshooting or optimization
- ✅ Slow, broken, or unstable WordPress websites
- ✅ Nginx / MySQL / Redis / Linux server issues
- ✅ CDN / Cloudflare / DNS / HTTPS configuration problems
- ✅ Long-term remote technical support or part-time maintenance
More details: About Me & Collaboration
WeChat: 13980074657
Email: shuijingwanwq@gmail.com
Telegram: @shuijingwan
GitHub: https://github.com/shuijingwan
Leave a Reply