CODEWITHSUNDEEP
ruby-on-railsruby
Shaunak
Shaunak

Reputation: 18028

Ruby on Rails: Retrieve value for every hour in last 24 hours

I have a sensor that sends in a value say ever 5 mins. This is being stored in a table along with the time stamp. I am using the code below to retrieve all values of that sensor in last 24 hours.

series = sensor.raindata.all(:order => "time_stamp", :conditions =>  {:time_stamp => Date.today...Date.today+1} )

But as the sensor us updating every 5 mins this is huge data. How do I sample the just one data point every hour? That is I need one value of that sensor per every hour. (I don't need the average for every hour. Just one sample per hour)

What is the most elegant way of achieving this? Is it possible to modify the above query to fetch this kind of data? or only way to do it is by post processing 'series' array?

Thanks Shaunak

Upvotes: 0

Views: 854

Answers (1)

MrTheWalrus
MrTheWalrus

Reputation: 9700

The way to do this is probably with SQL. Here's an example, though I had to make a lot of assumptions about your setup to give it. I'm assuming:

  • you're using Rails 3, and thus can use this syntax
  • you have a model named Sensor, of which each object is a reading from the sensor that has fields named time_stamp and reading
  • an average of the readings in an hour is an acceptable value for that hour
  • that your database is some flavor of SQL (the example works in PostgreSQL, minor modifications may be needed for others)

So, with those disclaimers out of the way, here's the example:

Sensor.select('avg(reading) as avg_reading, extract(hour from time_stamp) as hour').where(:time_stamp => Date.today...Date.today+1).group('extract(hour from time_stamp)').order('hour')

This will give you a collection of Sensor objects (sort of), each of which responds to .hour and .avg_reading.

Alternatively (if, for instance, you're not on Rails 3), you should be attempting to execute a SQL query that looks something like this (inserting dates where indicated):

SELECT AVG(reading) AS avg_reading, EXTRACT(HOUR FROM time_stamp) AS hour FROM sensors WHERE time_stamp > {start date} AND time_stamp < {end date} GROUP BY EXTRACT(HOUR FROM time_stamp) ORDER BY hour

I hope this is helpful.

Upvotes: 1

Related Questions