query where date = Date.today with Rails, MySQL, and Active Record

Question

I see in the Active Record docs, you can query for a date using a greater than / less than comparison. However, what if you want to select where date = Date.today or must I query where date is greater than yesterday and less than tomorrow?

As you can see, I'm doing exactly that in the following queries and querying where Date = today returns an empty set

1.9.3p286 :096 > Subscription.where("created_at = ?", Date.today).count
   (0.5ms)  SELECT COUNT(*) FROM `subscriptions` WHERE (created_at = '2013-01-18')
 => 0 

vs.

1.9.3p286 :098 > Subscription.where("expiration_date < ? AND expiration_date > ?", Date.today + 1, Date.today - 1).count
   (0.4ms)  SELECT COUNT(*) FROM `subscriptions` WHERE (expiration_date < '2013-01-19' AND expiration_date > '2013-01-17')
 => 1 

Is this the proper way to query for today's date or am I missing something?

Tattoo

Answer 1

I think this is a DUP of this question:

How to select date from datetime column?

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

I'm pretty sure this works in MySQL and PostgreSQL, but I'm not sure if it's a SQL standard.

Wikipedia seems to think TO_DATE would be the standard: http://en.wikipedia.org/wiki/SQL#Date_and_time

That didn't work for me in PostgreSQL though.

Hanschen

Thanks! And thanks for pointing out the DUP. Perhaps the question is still useful for people who want it written out explicitly in activerecord / Ruby. – Tattoo

this got working for me – Goblet

Answer 2

I believe the following post is more relevent to you Rails ActiveRecord Find / Search by Date

In your case, when you search with date with activerecord you might want to take the timezone conversions into consideration.

When you use the below method

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

it uses Mysql's DATE function to parse the date and you will get UTC time as saved in Mysql by activerecord. If your rails app is using some other time zone, you will get wrong results.

The correct way to use this would be avoid using any SQL functions, and instead use a range.

Subscription.where(created_at: Date.today.beginning_of_day..Date.today.end_of_day)

This would fetch results with timezone conversions applied. Please let me know you anyone has a better solution for this.

Peti

I think this answer doesn't get enough attention. Ive been using DATE() for awhile and only by chance while testing i discovered this behaviour. Now I have to go back and ensure every part of my code still behaves as it should. – Geek

Agree this is probably a better way to do it taking time zones in account. – Antananarivo

Answer 3

For me just works like this:

@fisrt_payment_day = Invoice.where("DATE(date_first_payment) >= ?", Date.today - 1.day)

Fairlead

Answer 4

You can make it really nice thanks to Active Support's #all_day extension.

Subscription.where(created_at: Date.today.all_day)

Lidia