MySQL 如何在时间列上获取平均值

要在时间列上获取平均值，请使用下面的语法。它将以时间格式给出平均值−

SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(yourColumnName))) as anyVariableName from yourTableName;

为了理解上述概念，让我们创建一个表。以下是查询语句−

mysql> create table AverageOnTime
   −> (
   −> PunchInTime time
   −> );
Query OK, 0 rows affected (0.61 sec)

使用插入命令在表中插入时间值。插入记录的查询如下所示−

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)

使用select语句从表中显示时间。查询如下所示−

mysql> select *from AverageOnTime;

以下是输出结果−

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

现在要获取时间列的平均值，请使用以下查询。查询如下所示−

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)

阅读更多：MySQL 教程