18

So I'm trying to do things like so:

today = Date.today - 1
todaysReport = Report.where(:created_at => today).find_by_user_id(user.id)

The problem is created_at is a datetime, so it won't find any matches..Any suggestions?

Elliot
  • 13,580
  • 29
  • 82
  • 118
  • 1
    `Date.today - 1` will evaluate to *yesterday*. Either remove the `-1` or change the three instances of "today" to "yesterday" – Noach Magedman Sep 06 '12 at 16:48

6 Answers6

23

You probably want something like this:

yesterday = Time.now - 1.day
user = User.find(user_id)

todaysReport = user.reports.where(["created_at >= ? AND created_at <= ?", yesterday.beginning_of_day, yesterday.end_of_day])
Dex
  • 12,527
  • 15
  • 69
  • 90
  • 3
    @dex, @elliot...i dont know if this works for postgres, but this works for mysql..`user.report.where(["DATE(created_at) = ?", Date.today - 1])` – rubyprince Jul 03 '11 at 06:46
  • @rubyprince You want to steer away from DB-specific functions like DATE(). Also, this query will be slow since it must run DATE() on every record in the table. – Dex Jul 03 '11 at 07:00
  • Why is using ruby ranges in rails queries so unpopular? I prefer: `.where(created_at: yesterday.beginning_of_day..yesterday.end_of_day)` – Mike Campbell Aug 06 '13 at 15:07
  • @MikeCampbell at the time I actually didn't realize you could do this. I like it better. – Dex Aug 07 '13 at 00:09
16

In postgres, you can cast a timestamp as a date. For example:

Report.where("created_at::date = ?", Date.today - 1)

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

Community
  • 1
  • 1
fbonetti
  • 6,652
  • 3
  • 34
  • 32
  • SQLite3::SQLException: unrecognized token: ":": ? this is what I use, ** Note.where("created_at::date= ? and user_id= ? ", day , current_user.id)** – Kick Buttowski Nov 15 '18 at 23:49
6

You need to compare against a range from one midnight to the next. Also, this is a good candidate for making your own class method for higher flexibility.

class Report
  # All reports created on the specified Date
  def self.created_on(date)
    where(['created_at >= ? AND created_at < ?', date, date + 1])
  end
end

# Find all reports created yesterday by our user
Report.created_on(Date.today - 1).where(:user_id => user.id)
Lars Haugseth
  • 14,721
  • 2
  • 45
  • 49
4

In Rails 5.1 and greater, you can use all_day instance method like this:

today = Date.today - 1
todaysReport = Report.where(created_at: today.all_day).find_by_user_id(user.id)
Sharvy Ahmed
  • 7,247
  • 1
  • 33
  • 46
1

It can also be TimeWithZone if time zone specified in application config. To get Date to TimeWithZone you have to do like Date.today.to_time.in_time_zone.

Renars Sirotins
  • 176
  • 3
  • 5
-5

Have you tried using #to_datetime, which Rails adds to the Date and Time classes?

Report.where(:created_at => today.to_datetime).find_by_user_id(user.id)

Actually surprised AR doesn't handle this for you.

d11wtq
  • 34,788
  • 19
  • 120
  • 195
  • wouldn't it be trying to match on a time when I'm only trying to match the day? – Elliot Jul 03 '11 at 01:14
  • The only problem with this is that he probably actually wants a range, which is why it doesn't work. AR should not generate an error as he has it now. – Dex Jul 03 '11 at 01:15