PostgreSQL：选择在特定时区的某一天的某一天发生的行

I've got a table that stores "snapshot" data - the number of staff at work is captured every 10 minutes and stored in it. I'd like to generate a report to show the number of staff at work over the course of a day on a specific weekday (eg. for the last four Sundays).

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

In Rails my query looks like this:

在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")

And that translates to this SQL:

这转换为这个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

In this case, time_zone_offset would differ based on the user I am doing the lookup for:

在这种情况下,time_zone_offset将根据我正在查找的用户而有所不同:

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

(The current time zone is also set in an around_filter, so that the 1.week.ago etc. are in the correct zone.)

(当前时区也设置在around_filter中,因此1.week.ago等位于正确的区域。)

My database's time zone is UTC (set TIME ZONE 'UTC').

我的数据库的时区是UTC(设置TIME ZONE'UTC')。

This works, but I'm sure there's a better way to do find records on a particular day of the week then by manipulating the interval by hand. I also don't think that will work with DST in time zones where that is applicable. I know PostgreSQL is capable of converting a time with time zone to a particular time zone, but that doesn't include a date, so I can't figure out the day of the week! Other WHERE clauses I have tried:

这有效,但我确信有更好的方法可以在一周中的某一天查找记录,然后手动操作间隔。我也认为这不适用于适用时区的DST。我知道PostgreSQL能够将带时区的时间转换为特定的时区,但这不包括日期,所以我无法弄清楚星期几!我试过的其他WHERE条款:

EXTRACT (dow from time') = 0 - this gives me snapshots between 10 AM Sunday and 10 AM Monday

EXTRACT(从时间开始)= 0 - 这给了我周日上午10点到周一上午10点之间的快照
EXTRACT (dow from time at time zone 'Brisbane/Australia') = 0 - this gives me actions between 8pm Sunday and 8pm Monday. My understanding is that this happens because Postgres treats the time field as if it is in Brisbane time, instead of converting it from UTC to Brisbane time.

提取(从布里斯班/澳大利亚时区的时间开始)= 0 - 这给了我星期一晚上8点到星期一晚上8点之间的行动。我的理解是,这是因为Postgres将时间字段视为布里斯班时间,而不是将其从UTC转换为布里斯班时间。

So I'd love to know if there is something I should be doing to get this query working.

所以我想知道我是否应该做些什么来使这个查询起作用。

1 个解决方案

#1

AT TIME ZONE when applied to a timestamp without timezone produces a timestamp with timezone (and the other way around). And this timestamp with timezone is interpreted in your session's timezone (which in your case is forced to UTC).

应用于没有时区的时间戳时,AT TIME ZONE会生成带时区的时间戳(反之亦然)。这个带时区的时间戳在会话的时区中解释(在您的情况下强制为UTC)。

So the expression EXTRACT (dow from time at time zone 'Brisbane/Australia') does not extract the day in Brisbane at time (UTC), it extracts the day corresponding to the converted time from the point of view of someone virtually living in the UTC time zone.

因此表达式EXTRACT(从时区'布里斯班/澳大利亚'开始)并不提取布里斯班时间(UTC)的那一天,它从实际居住在布里斯班的人的角度提取对应于转换时间的日期。 UTC时区。

As an example, when I'm typing this, if pretending to be in 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

好的,它是UTC的周日18:01和布里斯班的周一04:01

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.

请注意第二列与先前结果的不同之处。它实际上是以布里斯班表示的20小时在UTC中表达的:它可能是对一个没有多大意义的问题的技术上正确的答案。

Presumably you want this:

大概你想要这个:

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

which should answer: does the date and time time as measured in UTC corresponds to a Sunday in Brisbane?

应该回答:以UTC为单位测量的日期和时间是否与布里斯班的星期日相对应?

#1