如何获取 MySQL 时间列的平均值?
mysqlmysqli database
要获取时间列的平均值，请使用以下语法。它将以时间格式给出平均值 −
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)
使用 insert 命令在表中插入时间值。插入记录的查询如下 −
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)
