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

Question

我有一个存储“快照”数据的表 - 每 10 分钟捕获一次工作人员的数量并将其存储在其中。我想生成一份报告，以显示特定工作日(例如，最后四个星期天)一天内工作的员 worker 数。

在 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 能够 converting a time with time zone to a particular 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 在您的 session 时区中解释(在您的情况下，它被强制为 UTC)。

所以表达式 EXTRACT (dow from time at time zone 'Brisbane/Australia') 不会在 time (UTC)，它从虚拟生活在 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

Fine, it's Sunday 18:01 in UTC and Monday 04:01 at Brisbane

But if applying the timezone displacement to a 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

Notice how the second column differs from the previous result. It's actually 20 hours off of Brisbane expressed in UTC: it's presumably the technically correct answer to a question that doesn't make much sense.

Presumably you want this:

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

应该回答:以 UTC 测量的日期和时间 time 是否对应于布里斯类的星期日？