按日期分组并带有时区的Rails/Postgres查询行

Question

我正在尝试在特定用户的时区内，显示过去30天每天的展示量计数。问题在于，根据时区不同，计数并不总是相同的，我在查询中遇到了麻烦。

例如，假设在第一天晚上11:00（CDT -5）发生两次展示，以及第二天凌晨1:00发生一次展示（CDT）。如果您使用UTC (+0)进行查询，则会得到所有3个展示在第二天发生，而不是第一天发生两个展示和第二天发生一个展示。两个CDT时间都落在UTC的第二天。

这是我现在正在做的事情，我知道我肯定漏掉了一些简单的东西：

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

印象模型:

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

我一直在看其他帖子，似乎可以让数据库为我处理很多繁重的工作，但是我尝试使用 AT TIME ZONE 或 INTERVAL 等内容时没有成功。

我现在的做法似乎非常混乱，我知道我肯定漏掉了一些显而易见的东西。谢谢任何帮助。

- ifightcrime

---

数据库中存储的内容不太清楚。是没有时区的时间戳？还是有时区的时间戳？时区在应用程序或数据库级别上进行了规范化吗？你确定它们被正确地存储了吗？（例如，你可能会在没有时区的情况下存储它们，然后服务器在附加自己的时区后再存储它们等）。 - Denis de Bernardy

我正在使用 Rails 默认设置的 created_at 字段，该字段在 http://api.rubyonrails.org/classes/ActiveRecord/Timestamp.html 中定义。它们以 UTC 存储。值得一提的是，这是因为我的服务器本地时区是 UTC。 - ifightcrime

换句话说，它们是带有时区的UTC时间戳？ - Denis de Bernardy

Answer 1

好的，借助这篇精彩文章的一点帮助，我想我已经解决了问题。我的问题源于不知道系统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

我可能还能做更多的事情，但现在我对此感觉好多了。

- ifightcrime

Answer 2

假设around_filter正常工作并在代码块中设置了Time.zone，您应该能够将查询重构为以下内容：

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

- Suraj Mulani