ruby-on-rails - 时区感知 postgres 查询为分钟、小时、天创建时间序列

Question

我很难弄清楚如何处理以下问题:

我们公司正在向社交媒体平台发布帖子。这些帖子一旦成功发布，就会存储在数据库中。

我们想提供一个仪表板，显示用户在一段时间内按分钟、小时和天分组发布了多少帖子的概览。

我想将结果显示为时间序列图。

这会工作得很好，但是当我按天进行聚合/分组时必须支持多个时区时，它会变得非常棘手。 (显然午夜前后的帖子属于不同的日子，具体取决于您所在的时区)

我当前的解决方案使用 rails ActiveRecord 构建 postgres 查询。我面临的问题是我正在努力处理时区转换......另外我不是特别擅长postgres ...当前的实现基本上是这样的(我删除了不相关的代码):

Publication.select(                                                                                                                                                         
    %{date_trunc('#{interval}',
      published_at::timestamptz at time zone interval '#{time_zone_offset}')::timestamptz as time,
      count(published_at)})
      .where(%(published_at BETWEEN
          timestamptz '#{start_date}' AND
          timestamptz '#{end_date}'))
          .group("1")
          .order('time').limit(LIMIT)

例如:

我在 2016-03-15 10:19:24.219258 发布了一篇文章(这就是它在数据库中的存储方式，因此是 UTC 时间)我创建以下查询:

SELECT  date_trunc('hour',
      published_at::timestamptz at time zone interval '+01:00')::timestamptz as time,
      count(published_at) FROM "publications" WHERE (published_at BETWEEN
          timestamptz '2016-03-15 10:00:00 +0100' AND
          timestamptz '2016-03-15 12:00:00 +0100') GROUP BY 1 
;

结果是:

          time          | count 
------------------------+-------
 2016-03-15 10:00:00+01 |     1
(1 row)

应该是:

时间:“2016-03-15 10:00:00 UTC”或“2016-03-15 11:00:00+01”(我不关心时区表示，但这完全是错误的结果)

有人知道我在这里做错了什么吗？

我遇到的主要问题是我希望能够根据请求查询的用户的时区每天对出版物进行分组/聚合。我不关心返回哪个时区，因为前端可以将其转换为用户时区。

非常感谢任何反馈、帮助或回答。非常感谢

Answer 1

感谢我与 devanand 的讨论一种解决方案是拆分代码并使用问题中使用的查询处理每日间隔。

对于其他时间间隔，我使用以下查询:

    Publication.select(
      %{date_trunc('#{interval}',
      published_at::timestamptz) as time,
      count(published_at)})
               .where(%(published_at BETWEEN
          timestamptz '#{start_date}' AND
          timestamptz '#{end_date}'))
               .group('1')
               .order('time').limit(LIMIT)

我对这个解决方案并不满意，因为它对我来说更像是一种解决方法