timestamp - find record closest to a given time in ruby on rails -

timestamp - find record closest to a given time in ruby on rails -

background

i have ror application continuously recording , showing on web site real time sensor data. have table called sensors has unique list of sensors , stores latest values.

i have table histories dumps sensor values ever received each sensor.

so relation "sensor has many histories" , time_stamp col records creation time stamp.

not sensors update @ same interval or frequency.

problem

now want take input time stamp user, date , time in past, , show sensors showing @ time. illustration want see sensors looked @ 2 pm yesterday, 1 time have time stamp user, how retrieve 1 sensors value closest input time stamp history table.

i looking add together method in sensor model, take time_stamp argument, , retrive value closest input time_stamp history table.

what simplest way write active record query?

thanks shaunak

just sort histories according difference between passed timestamp , history timestamp (absolute value can go in either direction), , homecoming top result (that's 1 smallest difference).

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

note query assuming using mysql (because i'm using mysql method abs) , assuming time_stamp field stored unix timestamp , user input likewise. if database storage or input in different format, you'll have utilize different date arithmetic functions. see http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html details. or if not using mysql, see docs database using.

also note using .to_i sanitize info query. if info in different format, may need sanitize different way.

to create more efficient, limit time_spans within maximum possible range. if sensors take info every 10 minutes or more (never less 10 minutes apart between readings), range of greater 10 minutes on each side do. below. here, 600 = 10 (minutes) * 60 (seconds):

sensor.histories.where("time_stamp >= ? , 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 simple convert model method:

class sensor < activerecord::base def history_at(time_stamp) self.histories.where("time_stamp >= ? , time_stamp <= ?", time_stamp.to_i - 600, time_stamp.to_i + 600).order("abs(time_stamp - #{time_stamp.to_i})").first end end

ruby-on-rails timestamp