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

Rails
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

On 9 September 2010 19:24, voidstar [email protected] wrote:

 if( reading.timestamp != nil && years != nil &&

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

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

5

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.

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

Robert W. 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

8

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

9

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.

10

On Sep 9, 2:24 pm, voidstar [email protected] wrote:

  if( reading.timestamp != nil && years != nil &&

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.

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 J.