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可以将a转换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当应用于a时timestamp without timezone会产生一个timestamp with timezone（反之亦然）。这timestamp with timezone将在您会话的时区（在您的情况下为UTC）中进行解释。

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

例如，当我键入此内容时，如果假装为UTC：

=> set timezone to 'UTC';
=> select now(),now() at time zone 'Australia/Brisbane';
             now              |         timezone          
------------------------------+---------------------------
 2013-10-27 18:01:03.15286+00 | 2013-10-28 04:01:03.15286    

好的，今天是UTC的星期日18:01，布里斯班的星期一04:01

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

select now(),now()::timestamp at time zone 'Australia/Brisbane';
              now              |           timezone            
-------------------------------+-------------------------------
 2013-10-27 18:01:57.878541+00 | 2013-10-27 08:01:57.878541+00

请注意第二列与上一结果有何不同。实际上，它与布里斯班以UTC表示的时间相距20小时：大概是对没有太大意义的问题的技术上正确的答案。

大概您想要这样：

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

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