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

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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

On Thu, Sep 9, 2010 at 2:24 PM, voidstar
<barryodriscoll-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> 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
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 9 September 2010 19:24, voidstar
<barryodriscoll-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
>
> 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
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

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



On Sep 9, 10:21 pm, Robert Walker
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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.
> --
> Posted viahttp://www.ruby-forum.com/.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

On Sep 10, 11:27 am, voidstar
<barryodrisc...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Thanks Robert, yeah that''s a silly thing to do :)
>
> 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
>
> On Sep 9, 10:21 pm, Robert Walker
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:
>
>
>
> > 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.
> > --
> > Posted viahttp://www.ruby-forum.com/.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

On Sep 10, 2:03 pm, Robert Walker
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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
> --
> Posted viahttp://www.ruby-forum.com/.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Sep 9, 2:24 pm, voidstar
<barryodrisc...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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.
>
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.