Rails/Postgres query rows grouped by day with time zone

Question

I'm trying to display a count of impressions per day for the last 30 days in the specific users time zone. The trouble is that depending on the time zone, the counts are not always the same, and I'm having trouble reflecting that in a query.

For example, take two impressions that happen at 11:00pm in CDT (-5) on day one, and one impression that happens at 1:00am CDT. If you query using UTC (+0) you'll get all 3 impressions occurring on day two, instead of two the first day and one the second. Both CDT times land on the day two in UTC.

This is what I'm doing now, I know I must be missing something simple here:

start = 30.days.ago
finish = Time.now

# if the users time zone offset is less than 0 we need to make sure
# that we make it all the way to the newest data
if Time.now.in_time_zone(current_user.timezone) < 0
  start += 1.day
  finish += 1.day
end

(start.to_date...finish.to_date).map do |date|
  # get the start of the day in the user's timezone in utc so we can properly
  # query the database
  day = date.to_time.in_time_zone(current_user.timezone).beginning_of_day.utc
  [ (day.to_i * 1000), Impression.total_on(day) ]
end

Impressions model:

class Impression < ActiveRecord::Base
  def self.total_on(day)
    count(conditions: [ "created_at >= ? AND created_at < ?", day, day + 24.hours ])
  end
end

I've been looking at other posts and it seems like I can let the database handle a lot of the heavy lifting for me, but I wasn't successful with using anything like AT TIME ZONE or INTERVAL.

What I have no seems really dirty, I know I must missing something obvious. Any help is appreciated.

It's unclear how things are stored in the DB. timestamps without time zone? timestamps with time zones? Are the time zones normalized at the app or database level? Are you even sure they're stored properly? (e.g. might you be storing them without timezone and the server stores them after appending its own time zone, etc.) — Denis de Bernardy, Jun 25 '13 at 12:03
I'm using created_at which rails sets by default in http://api.rubyonrails.org/classes/ActiveRecord/Timestamp.html. They are stored in UTC. I should add that that's because my server's local timezone is UTC. — ifightcrime, Jun 25 '13 at 21:08
Err... In other words they're timestamp with time zone at time zone utc? — Denis de Bernardy, Jun 25 '13 at 21:22

score 2 · Accepted Answer · answered Jun 25 '13 at 22:17

Ok, with a little help from this awesome article I think I've figured it out. My problem stemmed from not knowing the difference between the system Ruby time methods and the time zone aware Rails methods. Once I set the correct time zone for the user using an around_filter like this I was able to use the built in Rails methods to simplify the code quite a bit:

# app/controllers/application_controller.rb

class ApplicationController < ActionController::Base
  around_filter :set_time_zone

  def set_time_zone
    if logged_in?
      Time.use_zone(current_user.time_zone) { yield }
    else
      yield
    end
  end
end

# app/controllers/charts_controller.rb

start = 30.days.ago
finish = Time.current

(start.to_date...finish.to_date).map do |date|
  # Rails method that uses Time.zone set in application_controller.rb
  # It's then converted to the proper time in utc
  time = date.beginning_of_day.utc
  [ (time.to_i * 1000), Impression.total_on(time) ]
end

# app/models/impression.rb

class Impression < ActiveRecord::Base
  def self.total_on(time)
    # time.tomorrow returns the time 24 hours after the instance time. so it stays UTC
    count(conditions: [ "created_at >= ? AND created_at < ?", time, time.tomorrow ])
  end
end

There might be some more that I can do, but I'm feeling much better about this now.

score 1 · Answer 2 · answered Jul 01 '13 at 13:09

Presuming the around_filter correctly works and sets the Time.zone in the block, you should be able to refactor your query into this:

class Impression < ActiveRecord::Base
  def self.days_ago(n, zone = Time.zone)
    Impression.where("created_at >= ?", n.days.ago.in_time_zone(zone))
  end
end

Rails/Postgres query rows grouped by day with time zone

2 Answers2