我有一个存储“快照”数据的表-每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转换为布里斯班时间。
因此,我很想知道是否应该做些什么才能使此查询正常工作。
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:
=>将时区设置为“ 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
:
选择时区'Australia / Brisbane'的now(),now():: timestamp; 现在| 时区 ------------------------------- + ----------------- -------------- 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度量的日期和时间是否对应于布里斯班的星期日?
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句