选择 2 个日期和平均每小时输出之间的数据 [英] Select data between 2 dates and average hourly output

问题描述

我有一系列温度数据，每分钟收集一次并放入 MySQL 数据库中.我想编写一个查询来选择过去 24 小时的所有温度，然后将它们分组并平均为小时.这可以通过 SQL 命令实现吗?

I am have a series of temperature data which is gathered every minute and put into a MySQL database. I want to write a query to select all temperatures for the last 24 hours then group and average them into hours. Is this possible via a SQL command?

我认为这是选择两个日期/时间之间的所有记录的情况，但这就是我被卡住的地方.

I think its a case of selecting all records between the two date/times, but thats the point I get stuck.

数据示例:

ID    Temp      Timestamp
3922  22        2015-11-17 14:12:23
3923  22        2015-11-17 14:13:23
3924  22.05     2015-11-17 14:14:23
3925  22.05     2015-11-17 14:15:23

需要的输出/结果

Temp   Time
22     2015-11-17 14:00:00
23     2015-11-17 15:00:00
23     2015-11-17 16:00:00
22.05  2015-11-17 17:00:00

我希望你能帮忙，因为我对 SQL 命令完全迷失了.

I hope you can help as I am totally lost with SQL commands.

推荐答案

试试这个查询

SELECT
   AVG(Temp) AS Temp,
   DATE_FORMAT(Timestamp, '%Y-%m-%d %H:00:00') AS Time
FROM Table
WHERE DATE_FORMAT(Timestamp, '%Y-%m-%d %H:00:00') >= DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y-%m-%d %H:00:00')
GROUP BY DATE_FORMAT(Timestamp, '%Y-%m-%d %H:00:00')

这篇关于选择 2 个日期和平均每小时输出之间的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！