Rails Time Zones and Custom database queries

I’ve been using Rails for so long without caring about how Time Zone’s are processed, that when I recently built a new application with Rails 2.3, I decided that I needed to care about the support for Time Zones since Rails 2.1

Technically, my new app doesn’t really care about time zones, so I could have just left the whole thing alone, but I wanted to see how these worked and get some experience.

The first big surprise is that dates are stored in the database as UTC time. Well, duh! This is usually seamless, but I noticed this detail when my custom code to group by created date on a set of objects suddenly started showing items for tomorrow at about 5pm Pacific time.

Here’s the code I was using:

1      Items.find(:all,
2                 :select => "DATE(created_at) as created_on, count(*) as c",
3                 :group => "DATE(created_at)",
4                 :conditions => [ "created_at > ?", 14.days.ago.to_date ],
5                 :order => 'created_on desc')

I realized that I needed to adjust for the current Time Zone when retrieving this data.

With some investigation – Google search wasn’t entirely helpful here – I ended up with the following code that retrieves the Time Zone offset in seconds to add to the stored time (for Pacific Time, this is a negative number!)

1      zone = 'PDT'
2      Items.find(:all,
3                 :select => "DATE(date_add(created_at, interval #{Time.zone_offset(zone)} second)) as created_on, count(*) as c",
4                 :group => "DATE(date_add(created_at, interval #{Time.zone_offset(zone)} second))",
5                 :conditions => [ "created_at > ?", 14.days.ago.to_date ],
6                 :order => 'created_on desc')

Now my reported counts by day rollover to the next day at midnight Pacific Time. Adjust your zone setting to suit what results you need.