如何获取MySQL时间上的平均值列？
要获取平均时间列，请使用以下语法。它将以时间格式给出平均值-
SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(yourColumnName))) as anyVariableName from yourTableName;
为了理解上述概念，让我们创建一个表。以下是查询-
mysql> create table AverageOnTime −> ( −> PunchInTime time −> );
使用insert命令将时间值插入表中。插入记录的查询如下-
mysql> insert into AverageOnTime values('00:00:40');
mysql> insert into AverageOnTime values('00:02:50');
mysql> insert into AverageOnTime values('00:03:30');
mysql> insert into AverageOnTime values('00:04:55');使用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)