1

I have these two lines in my model, written for PostgreSQL:

named_scope :by_month, lambda { |month| { :conditions => ["EXTRACT(MONTH FROM recorded_on) = ?", month] }}
named_scope :by_year, lambda { |year| { :conditions => ["EXTRACT(YEAR FROM recorded_on) = ?", year] }}

I'm running PostgreSQL in production, but I'm developing with SQLite3. How can I write those lines in a way that is database-agnostic?

Btw, "recorded_on" is formed from the following:

Model.recorded_on = Time.parse("Fri, 01 May 2009 08:42:23 -0400")
Mike Woodhouse
  • 51,832
  • 12
  • 88
  • 127
neezer
  • 19,720
  • 33
  • 121
  • 220

2 Answers2

4

Okay, found out there's a better way (thanks to this article):

Basically do nothing in the model!

date = Date.new(year, month, 1)
Model.find(:all, :conditions => { :recorded_on => date..date.end_of_month })
neezer
  • 19,720
  • 33
  • 121
  • 220
0

BETWEEN should be pretty universal: how about something like this:

  named_scope :by_month, lambda { |month| 
    d1 = Date.new(2009, month, 1)
    d2 = d1.end_of_month
    { :conditions => ['recorded_on between ? and ?', d1, d2]}
  }

  named_scope :by_year, lambda { |year| 
    d1 = Date.new(year, 1, 1)
    d2 = d1.end_of_year
    { :conditions => ['recorded_on between ? and ?', d1, d2]}
  }

If you have times, you'd need to get a little smarter with the hours, minutes and seconds.

Mike Woodhouse
  • 51,832
  • 12
  • 88
  • 127
  • Tried this in script/console and I get this error: ActiveRecord::StatementInvalid: PGError: ERROR: column "report_run_date" does not exist ?? – neezer May 21 '09 at 19:02
  • Whoops - left my own column name in the code from testing. Fixed. It worked beautifully on *my* table though... ;-) – Mike Woodhouse May 22 '09 at 09:19