CODEWITHSUNDEEP
ruby-on-railsactiverecord
Terence Chow
Terence Chow

Reputation: 11153

Rails Where Created_at Equals specific number

I'm trying to figure out how to do a query where created_at.year == a given year, and created_at.month equals a given month.

However I can't figure out what I'm doing wrong.

Model.where("'created_at.month' = ? AND 'created_at.year' = ?", 7,2013)

results in nothing being shown.

However when I try Model.first.created_at.month ==7 and Model.first.created_at.year ==2013 I get true for both.

Therefore theoretically my query should be at least be returning my first record.

Anyone know what I'm doing wrong or any alternative way to find records created on specific months?

Note that in my views the month / year will be parameters but for the purposes of this example I used actual values.

using ruby 1.9.3 rails 3.2.13

Upvotes: 3

Views: 2998

Answers (2)

user2503775
user2503775

Reputation: 4367

You can use the extract SQL function, that will extract the month and year of the timestamp:

Model.where('extract(year from created_at) = ? and extract(month from created_at) = ?', '2013','7')

This query should give you the desired result.

Upvotes: 4

Chris Heald
Chris Heald

Reputation: 62648

created_at is a timestamp; it is not a set of discrete fields in the database. created_at.year and such don't exist in your DB; it's simply a single timestamp field. When you call @model.created_at.year, Rails is loading the created_at field from the database, and creating a Time object from it, which has a #year method you can call.

What you want is to query on a range of dates:

Model.where("created_at >= ? and created_at < ?", Time.mktime(2013, 7), Time.mktime(2013, 8))

This will find any Model with a created_at timestamp in July 2013.

Upvotes: 3

Related Questions