PostgreSQL:选择在特定时区的某一天发生的记录[英] PostgreSQL: selecting rows that occur on a certain day of the week, in a specific time zone

本文是小编为大家收集整理的关于PostgreSQL:选择在特定时区的某一天发生的记录的处理方法,想解了PostgreSQL:选择在特定时区的某一天发生的记录的问题怎么解决?PostgreSQL:选择在特定时区的某一天发生的记录问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我有一个存储"快照"数据的表 - 每 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 转换 到布里斯班时间.

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

推荐答案

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 时区的人的观点.

例如,当我输入这个时,如果假装是 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

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

本文地址:https://www.itbaoku.cn/post/1764072.html