MySQL中如何求时间列的平均值？|极客笔记

MySQL中如何求时间列的平均值？

在MySQL中，如果要求一个时间列的平均值，需要进行一些额外的处理。因为时间在MySQL中是以时间戳（timestamp）的方式存储的，因此需要将时间戳转换为日期时间格式，然后再进行平均值计算。

我们以一个示例表t1为例，该表包含两列数据，分别是时间戳（timestamp）和数值（value）：

CREATE TABLE `t1` (
  `timestamp` timestamp NULL DEFAULT NULL,
  `value` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t1` (`timestamp`, `value`) VALUES 
('2020-01-01 00:00:01', 1),
('2020-01-01 00:00:02', 2),
('2020-01-01 00:00:03', 3),
('2020-01-01 00:00:04', 4),
('2020-01-01 00:00:05', 5),
('2020-01-01 00:00:06', 6);

现在我们想求出时间戳列的平均值，可以按照以下步骤进行操作：

将时间戳转换为日期时间格式，使用DATE_FORMAT函数实现：

“`mysql
SELECT DATE_FORMAT(`timestamp`, ‘%Y-%m-%d %H:%i:%s’) AS datetime, `value` FROM `t1`;
“`

转换后的结果如下：

“`mysql
+———————+——-+
| datetime | value |
+———————+——-+
| 2020-01-01 00:00:01 | 1 |
| 2020-01-01 00:00:02 | 2 |
| 2020-01-01 00:00:03 | 3 |
| 2020-01-01 00:00:04 | 4 |
| 2020-01-01 00:00:05 | 5 |
| 2020-01-01 00:00:06 | 6 |
+———————+——-+
“`

计算日期时间列的平均值，使用AVG函数实现：

“`mysql
SELECT AVG(UNIX_TIMESTAMP(`datetime`)) AS avg_timestamp FROM (
SELECT DATE_FORMAT(`timestamp`, ‘%Y-%m-%d %H:%i:%s’) AS datetime FROM `t1`
) AS tmp;
“`

计算结果如下：

“`mysql
+—————+
| avg_timestamp |
+—————+
| 1.577836e+09 |
+—————+
“`

这里返回的是UNIX时间戳，需要将时间戳转换为日期时间格式，使用FROM_UNIXTIME函数实现：

“`mysql
SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(`datetime`))) AS avg_datetime FROM (
SELECT DATE_FORMAT(`timestamp`, ‘%Y-%m-%d %H:%i:%s’) AS datetime FROM `t1`
) AS tmp;
“`

计算结果如下：

“`mysql
+———————+
| avg_datetime |
+———————+
| 2020-01-01 00:00:03 |
+———————+
“`

通过以上操作，我们可以得到时间列的平均值。需要注意的是，在转换时间格式的过程中，需要根据实际格式进行调整。如果时间列包含毫秒、微秒等精度更高的信息，需要在转换格式中进行调整。

阅读更多：MySQL 教程