Is it possible to do date arithmetic in an active record query without using custom sql ?

rubyonrails-talk
1

Hi,

I currently have a large array of model objects (around 5000 this could grow to be in the 100k range) which have a date attribute. I want to filter this objects if one of their date attribute falls into an array of years selected by the user. I wrote the code to do this in ruby land:

def FilterUtils.apply_year_filter readings, years     readings.each {|reading|       if( reading.timestamp != nil && years != nil && years.index(reading.timestamp.year) == nil )         readings.delete(reading)       end     }     readings   end

but its too slow so I'm going to do it through a :conditions query on the .find(:all) call rather than fetching them all back and filtering through them afterwards.

As far as I can tell there's no built in date queries in the active record interface. I've done about an hour of searching and no dice.

I'm currently writing the custom sql to do this in our postgres db but was just curious if there was an efficient way to do this without tying the project to postgres.

Thanks Barry

2

this kind of thing are better to left in the db server since is much faster. and by the why you can do date arithmetic but dont do it that way since you have a lot of records returned

3

Hi,

I currently have a large array of model objects (around 5000 this could grow to be in the 100k range) which have a date attribute. I want to filter this objects if one of their date attribute falls into an array of years selected by the user. I wrote the code to do this in ruby land:

def FilterUtils.apply_year_filter readings, years readings.each {|reading| if( reading.timestamp != nil && years != nil && years.index(reading.timestamp.year) == nil ) readings.delete(reading) end } readings end

but its too slow so I'm going to do it through a :conditions query on the .find(:all) call rather than fetching them all back and filtering through them afterwards.

As far as I can tell there's no built in date queries in the active record interface. I've done about an hour of searching and no dice.

You can do timestamp comparisons in ActiveRecord queries, so something like :conditions => [ 'timestamp >= ? and timestamp <= ?', timestamp1, timestamp2 ]

Colin

4

voidstar wrote:

def FilterUtils.apply_year_filter readings, years     readings.each {|reading|       if( reading.timestamp != nil && years != nil && years.index(reading.timestamp.year) == nil )         readings.delete(reading)       end     }     readings   end

I realize that you're not actually planning to use this code (thank goodness), but I just wanted to point out that you're mutating the array your iterating over. This is a VERY bad idea generally speaking.

5

Thanks Robert, yeah that's a silly thing to do :slight_smile:

Colin I'll try doing the :conditions => [ 'timestamp >= ? and timestamp <= ?', timestamp1, timestamp2 ] and see how it performs thanks I should have thought of doing that.

radhames yeah it makes a lot more sense to do it in the db, just wrote some code quickly to do it in ruby land to see if it was possible

I'll post up the finished code when I'm done for future reference if anyone else finds themselves in the same pickle

6

Here's the finished method thanks again Colin

def FilterUtils.get_readings_with_filters_applied reading_class, meter_ids, timestamp_col_name, years,

    all_conditions = Array.new

    all_conditions += ["meter_id IN (?)", meter_ids] unless meter_ids == nil || meter_ids.size <= 0

    years.each{ |year_int|       year = Date.civil(year_int, 1, 1)       year_start = year.beginning_of_year       year_end = year.end_of_year       all_conditions += [ "#{timestamp_col_name} >= ? and #{timestamp_col_name} <= ?", year_start, year_end ]     }

    readings = reading_class.all(:conditions => all_conditions)

  end

7

voidstar wrote:

Here's the finished method thanks again Colin

def FilterUtils.get_readings_with_filters_applied reading_class, meter_ids, timestamp_col_name, years,

    all_conditions = Array.new

    all_conditions += ["meter_id IN (?)", meter_ids] unless meter_ids == nil || meter_ids.size <= 0

    years.each{ |year_int|       year = Date.civil(year_int, 1, 1)       year_start = year.beginning_of_year       year_end = year.end_of_year       all_conditions += [ "#{timestamp_col_name} >= ? and #{timestamp_col_name} <= ?", year_start, year_end ]     }

    readings = reading_class.all(:conditions => all_conditions)

  end

When selecting based on a timestamp range one typically wants the range to be inclusive on the lower end and exclusive on the upper end. Being inclusive on both ends is probably not what you want.

A convenient (and nicely readable) syntax for that would be something like:

Rails 2.x: :conditions => { timestamp_col_name => year_start...next_year_start }

Rails 3.x .where({ timestamp_col_name => year_start...next_year_start })

This would produce something like: ... WHERE 'timestamp_col_name' >= '2010-01-01' AND 'timestamp_col_name' < '2011-01-01'

If you where to look at the year_start to year_end I'm afraid you would be missing the last day. Your range would be midnight of 2010-01-01 to midnight 2010-12-31. For example, looking at 2010-12-31 11:00 a.m. that would, incorrectly, fall outside of your range. You need to include all times up to, but not including, midnight 2011-01-01 in order to include all times of that last day.

8

Robert Walker wrote:

Rails 2.x: :conditions => { timestamp_col_name => year_start...next_year_start }

CORRECTION: conditions => { :timestamp_col_name => year_start...next_year_start }                 ^                  \__ Forgot to make this a symbol

9

Ah thank you Robert I'd missed that condition in my tests (lazy!) thanks

10

Couldn't you do something like:

reading_class.all(:conditions => ['YEAR(timestamp_field) IN (?)', years_list])

This is sort of a bad idea on some DBs, but Postgres will allow you to add an index on the *computed* value if things get slow...

--Matt Jones