Rails/Postgres查询行按日与时区分组

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.

我试着在特定用户的时区里，每天显示出对过去30天的印象。问题在于，根据时区的不同，计数并不总是相同的，而且我在查询中无法将其反映出来。

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.

例如，在CDT(-5)的第一天晚上11点的时候，有两种印象，一种印象是在1:00am CDT。如果您使用UTC(+0)查询，您将得到所有3个印象在第2天出现，而不是第一天出现2个，第2天出现1个。两个CDT都在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.

我所没有的似乎真的很脏，我知道我一定错过了一些明显的东西。任何帮助都是感激。

2 个解决方案

#1

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:

好的，在这篇很棒的文章的帮助下，我想我已经弄明白了。我的问题源于不知道系统Ruby时间方法和时区感知Rails方法之间的区别。一旦我使用这样的around_filter为用户设置了正确的时区，我就可以使用内置的Rails方法来简化代码:

# 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.

也许我还能做些什么，但我现在感觉好多了。

#2

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

假设around_filter正确工作并设置时间。区域在块中，您应该能够重构您的查询到以下内容:

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