Rails - correct db query returns empty array

Question

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?

What DBM are you using? Typically we'd truncate the DateTime value to a date in the query to make it match. — the Tin Man
– the Tin Man, Commented Aug 1, 2013 at 14:04

MrYoshiji · Accepted Answer · 2013-08-01 14:02:35Z

3

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:

http://sqlserverplanet.com/tsql/cast-date

edited Aug 1, 2013 at 14:02

answered Aug 1, 2013 at 13:56

MrYoshiji

54.9k13 gold badges130 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ddavison · Accepted Answer · 2013-08-01 13:57:04Z

0

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

answered Aug 1, 2013 at 13:57

ddavison

29.3k17 gold badges90 silver badges116 bronze badges

Comments

amb110395 · Accepted Answer · 2013-08-01 13:59:30Z

0

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.

answered Aug 1, 2013 at 13:59

amb110395

1,54513 silver badges16 bronze badges

Collectives™ on Stack Overflow

Rails - correct db query returns empty array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related