1

Background

I have a ror application which is continuously recording and showing on a web site real time sensor data. Then I have a table called sensors which has unique list of all sensors and stores latest values.

I also have another table histories which dumps all the sensor values ever received for each sensor.

So the relation is "sensor has many histories" , the time_stamp col records the creation time stamp.

Not all sensors update at same interval or frequency.

Problem

Now I want to take a input time stamp from user, a date and time in past, and show what the sensors were showing at that time. For example say i want to see what all sensors looked like at 2 PM yesterday, once i have this time stamp from user, how do i retrieve one sensors value closest to input time stamp from the history table.

I am looking to add a method in Sensor model, which will take time_stamp as argument, and retrive the value closest to input time_stamp from the history table.

What is they simplest way to write this Active record query?

Thanks Shaunak

Shaunak
  • 17,377
  • 5
  • 53
  • 84

1 Answers1

8

Just sort the histories according to the difference between the passed timestamp and the history timestamp (absolute value so it can go in either direction), and return the top result (that's the one with the smallest difference).

sensor.histories.order("ABS(time_stamp - #{params[:time_stamp].to_i})").first

Note that for this query I am assuming you are using MySQL (because I'm using a MySQL method ABS) and I am also assuming that the time_stamp field is stored as unix timestamp and the user input likewise. If the database storage or input is in a different format, you'll have to use different date arithmetic functions. See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html for details. Or if you are not using MySQL, see the docs for the database you are using.

Also note that I am using .to_i to sanitize my data for the query. If your data is in a different format, you may need to sanitize it a different way.

To make this more efficient, limit it to time_spans within the maximum possible range. If sensors take data every 10 minutes or more frequently (never less than 10 minutes apart between readings), then a range of greater than 10 minutes on each side will do. Something like below. Here, 600 = 10 (minutes) * 60 (seconds):

sensor.histories.where("time_stamp >= ? AND time_stamp <= ?", params[:time_stamp].to_i - 600, params[:time_stamp].to_i + 600).order("ABS(time_stamp - #{params[:time_stamp].to_i})").first

It is simple to convert this to a model method:

class Sensor < ActiveRecord::Base
    def history_at(time_stamp)
        self.histories.where("time_stamp >= ? AND time_stamp <= ?", time_stamp.to_i - 600, time_stamp.to_i + 600).order("ABS(time_stamp - #{time_stamp.to_i})").first
    end
end
Ben Lee
  • 52,489
  • 13
  • 125
  • 145
  • Thanks for a quick answer! Just one more thing before call this closed, every sensor updates every 10 mins and i have data for about an year! so wont sorting the whole thing take lots of time every time i want to read value of one sensor? IS there some way to optimize the query you told me by giving some span based on the input time stamp? and I am using MS SQL Server 2008. Thanks again! – Shaunak Jan 31 '12 at 01:09
  • Yes, you can limit it to only reasonable time_stamps to consider. I updated my answer with some details. – Ben Lee Jan 31 '12 at 01:17
  • What if it's a query for multiple sensors? Is there a way to do a single query to find them all without hitting the database for each sensor? – Lev Nov 29 '17 at 17:39