CODEWITHSUNDEEP
ruby-on-railsrubydatabase
The Whiz of Oz
The Whiz of Oz

Reputation: 7043

Rails - correct db query returns empty array

Can anyone please explain me, why my db queries return empty, when I have data in my table?

Event.all

returns

...
Event Load (0.3ms)  SELECT "events".* FROM "events"
 => #<ActiveRecord::Relation [#<Event id: 1, created_at: "2013-08-01 12:27:36", updated_at: "2013-08-01 12:27:36"> 
...

etc,

While

Event.where(created_at: Date.today)

gives me 

Event Load (0.3ms)  SELECT "events".* FROM "events" WHERE "events"."created_at" = '2013-08-01'
 => #<ActiveRecord::Relation []>

Where is everything?

Upvotes: 1

Views: 235

Answers (3)

MrYoshiji
MrYoshiji

Reputation: 54882

The field created_at is a DateTime, but you are comparing it to a Date (no time). You need to Cast the field as a Date to get all Events created today:

Event.where('CAST(events.created_at as DATE) = ?', Date.today)

Attention: The syntax may change depending on your Data-base system (PostGreSQL / MySQL, etc).

Hope this helps!

Useful link:

Upvotes: 3

amb110395
amb110395

Reputation: 1545

The problem is that the created_at: attribute (assuming that it was created in the migration timestamps) also stores the time. So it will never equal a simple date. You're best option is to parse the date and then compare it.

Upvotes: 0

ddavison
ddavison

Reputation: 29042

If you look at your actual query -

SELECT "events".* FROM "events" WHERE "events"."created_at" = '2013-08-01'

You are looking for a record with created_at equals 2013-08-01, but in actuality, the record you are trying to search for - the created_at field equals 2013-08-01 12:27:36.

Change your statement to do a search for created_at that contains 2013-08-01

Upvotes: 0

Related Questions