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?

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.

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.