Cách tính trung bình cột TIME trong MySQL

Công thức

Chuyển TIME → giây (TIME_TO_SEC), tính trung bình (AVG), chuyển ngược giây → TIME (SEC_TO_TIME):

SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(ten_cot_time))) AS ten_alias FROM ten_bang;

Ví dụ: Trung bình giờ chấm công (PunchInTime)

1. Tạo bảng

mysql> CREATE TABLE AverageOnTime ( 
-> PunchInTime TIME 
-> ); 
Query OK, 0 rows affected (0.61 sec)

2. Chèn dữ liệu

mysql> INSERT INTO AverageOnTime VALUES('00:00:40'); 
Query OK, 1 row affected (0.20 sec) 
mysql> INSERT INTO AverageOnTime VALUES('00:02:50'); 
Query OK, 1 row affected (0.15 sec) 
mysql> INSERT INTO AverageOnTime VALUES('00:03:30'); 
Query OK, 1 row affected (0.13 sec) 
mysql> INSERT INTO AverageOnTime VALUES('00:04:55'); 
Query OK, 1 row affected (0.14 sec)

3. Dữ liệu gốc

mysql> SELECT * FROM AverageOnTime;

+-------------+ 
| PunchInTime | 
+-------------+ 
| 00:00:40    | 
| 00:02:50    | 
| 00:03:30    | 
| 00:04:55    | 
+-------------+ 
4 rows in set (0.00 sec)

4. Tính trung bình

mysql> SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(PunchInTime))) AS Average FROM AverageOnTime;

+---------------+ 
| Average       | 
+---------------+ 
| 00:02:58.7500 | 
+---------------+ 
1 row in set (0.08 sec)

Giải thích từng bước

1. TIME_TO_SEC('00:00:40') = 40 giây
2. TIME_TO_SEC('00:02:50') = 170 giây
3. TIME_TO_SEC('00:03:30') = 210 giây
4. TIME_TO_SEC('00:04:55') = 295 giây
5. AVG(40, 170, 210, 295) = 715 / 4 = 178.75 giây
6. SEC_TO_TIME(178.75) = '00:02:58.7500'

Ứng dụng thực tế

Trung bình giờ làm việc theo nhân viên

SELECT 
    employee_id,
    SEC_TO_TIME(AVG(TIME_TO_SEC(work_hours))) AS avg_work_time
FROM timesheets 
GROUP BY employee_id;

Trung bình thời gian phản hồi (response time)

SELECT 
    DATE(created_at) AS day,
    SEC_TO_TIME(AVG(TIME_TO_SEC(response_time))) AS avg_response
FROM tickets 
GROUP BY DATE(created_at);

Lưu ý

• Kết quả có thể có phần microseconds (.xxxx)
• Dùng TIME_FORMAT() để format hiển thị: TIME_FORMAT(SEC_TO_TIME(AVG(TIME_TO_SEC(cot))), '%H:%i:%s')
• NULL values bị bỏ qua bởi AVG()
• Chỉ áp dụng cho kiểu TIME, không dùng cho DATETIME

Mở rộng: Các thống kê khác trên TIME

SELECT 
    SEC_TO_TIME(MIN(TIME_TO_SEC(cot))) AS min_time,
    SEC_TO_TIME(MAX(TIME_TO_SEC(cot))) AS max_time,
    SEC_TO_TIME(AVG(TIME_TO_SEC(cot))) AS avg_time,
    SEC_TO_TIME(SUM(TIME_TO_SEC(cot))) AS total_time
FROM ten_bang;