Querying for date range in rails

Question

I have a scope that queries for today's calls. Based off of the scope I use it to count the amount of calls for today.

My dates are stored in UTC but rails converts to my local timezone. What I'm trying to do is find all calls between today at 00:00 and 23:59.

Scope:

scope :today, where("DATE(transfer_date) BETWEEN ? AND ?", Time.zone.now.utc.beginning_of_day, Time.zone.now.utc.end_of_day)

irb output: (The call it catches due to UTC)

irb(main):010:0> Call.last.transfer_date
  Call Load (0.9ms)  SELECT "calls".* FROM "calls" ORDER BY "calls"."id" DESC LIMIT 1
=> Sun, 07 Oct 2012 19:45:00 CDT -05:00
irb(main):011:0> 

irb(main):011:0> Call.last.transfer_date.utc
  Call Load (1.3ms)  SELECT "calls".* FROM "calls" ORDER BY "calls"."id" DESC LIMIT 1
=> 2012-10-08 00:45:00 UTC

I'm trying to figure out how to query only calls that were between 00:00 and 23:59 for today. So far trying to scope with and without utc, zone, etc doesn't work. It keeps pulling the scope based off of UTC which includes the call from yesterday (yesterday if it's formatted with the local timezone).

How can I query between the two times to get the correct output? I'm kind of lost here.

Solution 1

I was able to compensate for UTC by rewriting my scope as follows:

scope :today, where("transfer_date BETWEEN ? AND ?", Time.zone.now.beginning_of_day, Time.zone.now.end_of_day)

Solution 2

You can use an exclusive range.

scope :today, where(:transfer_date => Date.today...Date.tomorrow)

Comments

[nulltek]

This is good, and thanks for the example, but I ended up going with a simple lambda scope a few years back :)