MySQL:选择查询，每5分钟递增一次 [英] MySQL: select query, 5 minute increment

问题描述

我有一个天气数据库，它大约每70秒获取一次数据集(取决于气象站何时发送数据).
我想使用Pchart绘制图形，但是我有太多示例，所以X轴被拧紧了.
所以我想要大约每5分钟一次的数据. (或每30分钟)
我目前有的查询是这样的:

I have a weather database which gets datasets about every 70 seconds (depends on when the weather-station delivers the data).
I want to graph it using Pchart but i have too many samples so the X axis is screwed up.
So i want the data for about every 5 minutes. (or every 30 minutes)
The query i currently have is this:

SELECT time, temp_out FROM wmr200 WHERE date(time) = curdate()

这可以获取最近24小时的样本，但是数量太多.

This gets the samples for the last 24 hours but there are too many.

推荐答案

以下内容将为您提供一个包含时间戳为:00，:05，:10 ...的任何数据的示例.

The following will get you a sample consisting of any data with a timestamp at :00, :05, :10 ...

SELECT time, temp_out FROM wmr200 WHERE date(time) = curdate()
    AND mod(minute(time),5) = 0

我正在使用模函数来检查时间的分钟部分是否为(0或)5的整数.

I'm using the modulo function to check if the minute part of the time is (0 or) divisible by 5.

如果每个时间段只需要一个结果，那么我们就变得更加复杂了.

If you need only one result for each time segment, we've got to get quite a bit more complex.

SELECT concat(date(time),' ',hour(time),':',round(minute(time)/5,0)*5), 
       min(temp_out), avg(temp_out), max(temp_out) 
FROM wmr200 
GROUP BY date(time), hour(time), round(minute(time)/5,0)*5

我现在无权访问MySQL实例以对其进行测试，但这就是这个主意.按日期，小时和round(minute(time)/5,0)*5分组，每五分钟分组一次-将分钟四舍五入到最接近的5.

I don't have access to a MySQL instance to test it out right now, but here's the idea. It groups by every five minutes, by grouping by date, hour, and round(minute(time)/5,0)*5 - which rounds minute to the nearest 5.

它选择应该分组的时间值以及min，avg和max temp_out，因为我不确定哪一个最适合您的情况.

It selects the value of time that it should be grouped around, and the min, avg, and max temp_out, as I wasn't sure which of these would best apply to your situation.

但是，如果您的数据库在5分钟内(或30分钟，如果您正在使用的话)没有数据，那么它可能仍然没有采样点的数据.

If, however, your DB doesn't have data for a five minute stretch (or 30 minute stretch, if that's what you're using), it still may not have data for a sample point.

这篇关于MySQL:选择查询，每5分钟递增一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！