CODEWITHSUNDEEP
ruby-on-railsactiverecord
Lesleh
Lesleh

Reputation: 1675

Selecting entries with a specific year and month from a Rails database

How can I select all database entries with a given date and month in a datetime? I tried this:

scope :with_year_and_month, ->(year, month) {
  where("YEAR(created_at) = ? AND MONTH(created_at) = ?", month, year)
}

Now that would work perfectly fine in a MySQL database, but in an Sqlite database it fails because Sqlite hasn't implemented the MONTH() and YEAR() functions. The following works fine though in Sqlite:

scope :with_year_and_month, ->(year, month) {
  where("strftime('%m', created_at) = ? AND strftime('%Y', created_at) = ?", "%02d" % month, year.to_s)
}

How would I do this in a database agnostic way?

Upvotes: 5

Views: 4213

Answers (1)

Mohammad AbuShady
Mohammad AbuShady

Reputation: 42799

I think you should let active record handle this, since it checks what database it's using and uses a driver for each database, the trick is to find a generic query to run, for me this is what I could come up with, might not be the best but I'm open for suggestions

scope :with_year_and_month, ->(year, month) {
  where(created_at: Date.new(year,month,1)..Date.new(year,month,-1))
}

This will generate a between query

WHERE created_at BETWEEN '2015-02-01' AND '2015-02-28'

I think this should work with you

If created_at is DateTime then you should replace the Date with DateTime object so you don't miss the time between 2015-02-28 00:00:00 and 2015-02-28 23:59:59, but you'll need extra parameters for the time

scope :with_year_and_month, ->(year, month) {
  where(created_at: DateTime.new(year,month,1)..DateTime.new(year,month,-1, -1, -1, -1))
}

of course you could create a small helper function to return those dates and shorten your method.
The result would be

Where created_at BETWEEN '2015-02-01 00:00:00' AND '2015-02-28 23:59:59'

Update:

So after a couple of comments, here's the final version

scope :with_year_and_month, ->(year, month) {
  date = DateTime.new(year,month)
  where(created_at: date...date.next_month)
}

The generated query would look like this

created_at >= '2015-02-01 00:00:00' AND created_at < '2015-03-01 00:00:00'

Upvotes: 6

Related Questions