时区感知的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"（我不关心时区表示，但这只是错误的结果）

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

我卡住的主要问题是，我想能够按照用户请求查询的时区将出版物分组/聚合到每一天。 我不在乎返回哪个时区，因为前端可以将其转换为用户时区。

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

- hibe

---

你在服务器上配置了哪个时区？http://serverfault.com/questions/554359/postgresql-timezone-does-not-match-system-timezone - devanand

显示时区：欧洲/维也纳；同时，Rails服务器已配置为维也纳时区。 - hibe

查询不应依赖于服务器设置的时区，而应使用用户传递的时区。 - hibe

数据存储在与PostgreSQL服务器相同的时区。 - hibe

所以所有记录都使用Europe/Vienna时区。这意味着是+1？删除所有时区语句并更改start_date和end_date参数以反映用户的时区？然后只将时区添加到显示数据中。 - devanand

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)

我对这个解决方案并不满意，因为它给我的感觉更像是一种权宜之计。

- hibe