4

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()

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

HyperDevil
  • 2,519
  • 9
  • 38
  • 52

2 Answers2

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

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

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.

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.

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.

Sam DeHaan
  • 10,246
  • 2
  • 40
  • 48
  • I changed it to this: SELECT time, temp_out FROM wmr200 WHERE date(time) = curdate() AND mod(minute(time),30) = 0 But some samples seem to miss, or i get duplicates. Since the data is not stored in a steady pase.. – HyperDevil May 01 '12 at 19:32
  • Example output: `2012-05-01 00:00:18 4.4 2012-05-01 00:30:51 4.4 2012-05-01 01:00:01 4.3 2012-05-01 01:00:50 4.3` – HyperDevil May 01 '12 at 19:35
  • 1
    @HyperDevil I edited in a possible solution. I can't test it atm, so let me know how it works. – Sam DeHaan May 01 '12 at 19:45
  • would it be possible to only see the the hour:minute? Also the minute function seems to print 60, but the mysql documentation only says the range of the minute command is 0 to 59...? – HyperDevil May 01 '12 at 20:10
  • 1
    Certainly. The `concat(...)` function concatenates all of those strings together. If you don't want the date, remove the `date(time)` and `' '` strings. As to the 60, the `round(minute(time)/5,0)*5` function groups by closest 5, so it rounds up to 60, that's my fault. I can't seem to come up with a quick fix at the moment, I'll return and try to help with that when I have time. – Sam DeHaan May 01 '12 at 20:16
3

In case anyone else happens upon this page, here's a solution that's comparable to Sam's. It makes use of Unix timestamps and integer division to "bin" and average values over any arbitrary time period.

SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(time) div (5*60))*(5*60)+(5*60)) as timeIntervals,
round(avg(temp_out),3) as temp_out
FROM wmr200 
GROUP BY 1
andrewgcross
  • 253
  • 2
  • 13