PostgreSQL:选择在一周中的某一天、特定时区发生的行

Question

我有一个存储"快照"数据的表 - 每 10 分钟捕获一次工作员工数量并存储在其中。我想生成一份报告，以显示特定工作日（例如，过去四个星期日）一天中工作的员工数量。

在 Rails 中，我的查询如下所示：

<model>.where("EXTRACT(dow FROM (time + interval '#{time_zone_offset} hours')) = ?", Time.zone.now.wday)
       .where('time BETWEEN ? AND ?', 5.weeks.ago.end_of_week, 1.week.ago.end_of_week)
       .select("organisation_id, date_trunc('hour', time) as grouped_time, avg(staff) as staff")
       .group("grouped_time").order("grouped_time")

这转化为这个 SQL：

SELECT date_trunc('hour', time) as grouped_time, avg(staff) as staff
FROM <model>
WHERE (EXTRACT(dow FROM (time + interval '10 hours')) = 0)
AND (time BETWEEN '2013-09-22 13:59:59.999999' AND '2013-10-20 13:59:59.999999')
GROUP BY grouped_time
ORDER BY grouped_time

在这种情况下，time_zone_offset会根据我正在查找的用户而有所不同：

def time_zone_offset
  @tzoffset ||= ActiveSupport::TimeZone[current_user.organisation.time_zone].utc_offset / 60 / 60
end

（当前时区也设置为around_filter，以便1.week.ago等位于正确的区域中。

我的数据库的时区是 UTC （set TIME ZONE 'UTC' ）。

这有效，但我相信有一种更好的方法来查找一周中特定日期的记录，然后手动操作interval。我也不认为这在适用的时区中适用于 DST。我知道PostgreSQL能够将time with time zone转换为特定的时区，但这不包括日期，所以我无法弄清楚星期几！我尝试过的其他 WHERE 子句：

• EXTRACT (dow from time') = 0 - 这为我提供了周日上午 10 点到周一上午 10 点之间的快照

• EXTRACT (dow from time at time zone 'Brisbane/Australia') = 0 - 这让我在周日晚上 8 点到周一晚上 8 点之间采取行动。我的理解是，发生这种情况是因为 Postgres 将time字段视为布里斯班时间，而不是将其从 UTC 转换为布里斯班时间。

所以我很想知道我是否应该做些什么来让这个查询工作。

Answer 1

AT TIME ZONE应用于timestamp without timezone时会产生timestamp with timezone（反之亦然）。并且此timestamp with timezone以会话的时区进行解释（在您的情况下，时区将被迫UTC）。

因此，表达式 EXTRACT (dow from time at time zone 'Brisbane/Australia') 不会提取布里斯班time （UTC） 的一天，而是从实际居住在 UTC 时区的人的角度提取与转换后的time相对应的日期。

例如，当我键入以下内容时，如果假装是UTC：

=> 将时区设置为"UTC";=> 在时区"澳大利亚/布里斯班"选择 now（），now（）;             现在 |        时区         ------------------------------+--------------------------- 2013-10-27 18：01：03.15286+00 |2013-10-28 04:01:03.15286

很好，现在是UTC的周日18：01和布里斯班的周一04：01

但是，如果将时区位移应用于timestamp without timezone：

选择 now（），now（）：：时区"澳大利亚/布里斯班"的时间戳;              现在 |          时区           -------------------------------+------------------------------- 2013-10-27 18：01：57.878541+00 |2013-10-27 08:01:57.878541+00

请注意第二列与上一个结果的不同之处。实际上，它比布里斯班少了20个小时，以UTC表示：这可能是一个没有多大意义的问题的技术正确答案。

大概你想要这个：

EXTRACT (dow from (time AT TIME ZONE 'UTC') at time zone 'Brisbane/Australia')=0

哪个应该回答：以UTC为单位测量的日期和时间time是否对应于布里斯班的星期日？