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) as timediff from orders where editTime is not null limit 50,100; +-----------+----------+ | id_orders | timediff | +-----------+----------+ | 51 | 1 | | 52 | 1 | | 53 | NULL | | 54 | 1 | | 55 | 1 | | 56 | NULL | | 57 | 2 | | 58 | 2 | | 59 | 2 | | 60 | 2 | | 61 | 4 | | 62 | 67 | | 63 | 1 | | 64 | 1 | | 65 | 3 | | 66 | 54 | | 67 | 31 | | 68 | 1 | | 69 | 60 | | 70 | 57 | | 71 | 34 | | 72 | 7 | | 73 | 19 | | 74 | 24 | | 75 | 86 | | 76 | 24 | | 77 | 8 | | 78 | 15 | | 79 | 126 | | 80 | 66 | | 81 | 2 | | 82 | 1 | | 83 | 51 | | 84 | 82 | | 85 | 17 | | 86 | 49 | | 87 | 4 | | 88 | 4 | | 89 | 11 | | 90 | 2 | | 91 | NULL | | 92 | 19 | | 93 | 2 | | 94 | 6 | | 95 | 24 | | 96 | 1 | | 97 | 6 | | 98 | 6 | | 99 | NULL | | 100 | 19 | | 101 | 1 | | 102 | 1 | | 103 | 3 | | 104 | 1 | | 105 | 5 | | 106 | 6 | | 107 | 36 | | 108 | 1 | | 109 | 38 | | 110 | 1 | | 111 | 9 | | 112 | 19 | | 113 | 9 | | 114 | 43 | | 115 | 19 | | 116 | 47 | | 117 | 10 | | 118 | 24 | | 119 | 1 | | 120 | 1 | | 121 | 1 | | 122 | 1 | | 123 | 57 | | 124 | 17 | | 125 | 52 | | 126 | 21 | | 127 | 471 | | 128 | 75 | | 129 | 6 | | 130 | 4 | | 131 | 96 | | 132 | 2 | | 133 | 1 | | 134 | 57 | | 135 | 30 | | 136 | 1 | | 137 | 11 | | 138 | 15 | | 139 | 3 | | 140 | 1 | | 141 | 29 | | 142 | 1 | | 143 | 31 | | 144 | 1 | | 145 | 1 | | 146 | 37 | | 147 | 34 | | 148 | 2 | | 149 | 4 | | 150 | 187 | +-----------+----------+ 100 rows in set (0.00 sec)