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

最新推荐文章于 2025-05-20 15:01:13 发布

原创最新推荐文章于 2025-05-20 15:01:13 发布 · 4.7k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#时间差 #mysql #平均值

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何在MySQL中计算时间差的平均值。针对包含时间字段的表，当时间为空时忽略，提供的SQL语句能够返回所有有效数据的时间差平均值。同时，提供了查看原始数据的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)