MySql 如何返回 时间差的平均值

MySql 如何返回 时间差的平均值

返回时间差平均值

设有一表内有字段submitTime，editTime,需要返回所有数据的时间差的平均值
时间为空则不计，语句为

//返回平均值为小时
SELECT AVG(TIMESTAMPDIFF(HOUR,submitTime,editTime)) as timediff from orders where editTime is not null;

//返回平均值为分钟
SELECT AVG(TIMESTAMPDIFF(MINUTE,submitTime,editTime)) as timediff from orders where editTime is not null;

//返回平均值为秒
SELECT AVG(TIMESTAMPDIFF(SECOND,submitTime,editTime)) as timediff from orders where editTime is not null;

返回值是

MariaDB [mypchelper]>  SELECT AVG(TIMESTAMPDIFF(HOUR,submitTime,editTime)) as timediff from orders where editTime is not null;
+----------+
| timediff |
+----------+
|  62.9911 |
+----------+
1 row in set (0.00 sec)

查看原数据

若要查看这些数据，则输入

 SELECT id,TIMESTAMPDIFF(HOUR,submitTime,editTime) as timediff from orders where editTime is not null ;

我这里取了100条数据

MariaDB [mypchelper]>  SELECT id_orders,TIMESTAMPDIFF(HOUR,submitTime,editTime