CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3activerecord
user2469227
user2469227

Reputation:

Search for database objects created on a specific day?

I want to query my db for objects of an arbitrary model created on a certain day, what's a good way to do this?

In an sql query I used the following to query the objects created yesterday.

where (created_at AT TIME ZONE 'EDT') > (now() AT TIME ZONE 'EDT')::DATE - 1 and (created_at AT TIME ZONE 'EDT') < (now() AT TIME ZONE 'EDT')::DATE

I want to do the same thing in ruby with ActiveRecord calls.

Thanks!

Upvotes: 0

Views: 233

Answers (2)

Novae
Novae

Reputation: 1171

I would do the cast on the database and select the current date there, instead of doing it with a range. You can do this with DATE(created_at) (Will result in "2013-11-01") Then inject the param in the where:

Model.where("DATE(created_at) = ?", Date.today)

If it's used on more places then one it would be good to create a scope as Morner suggested:

scope :created_today { where("DATE(created_at) = ?", Date.today) }

with name:

scope :created_today_with_name lambda do |name| 
  where("DATE(created_at) = ? AND name = ?", Date.today, name)
end

or chain it:

scope :created_today_with_name lambda do |name| 
  where("DATE(created_at) = ?", Date.today).where(name: name)
end

Then call it as:

Model.created_today_with_name('Joe')

Upvotes: -2

Momer
Momer

Reputation: 3237

An example, as a scope, for all items that were created today:

scope :created_today, -> { where('created_at between ? AND ?', Time.zone.now.beginning_of_day, Time.zone.now.end_of_day) }

More information at the PostgreSQL site

Upvotes: 3

Related Questions