选择计划的项目时在Postgres中考虑DST [英] Accounting for DST in Postgres, when selecting scheduled items

问题描述

我有一个 Postgres 表，其中包含时钟警报（不是真的，但这是相似的，并且更容易解释）。警报是由用户以1小时的分辨率设置的，并且用户可以来自许多不同的时区。警报每天重复。我想可靠地获取应该在一天中特定时间响起的警报，并且我在夏令时方面遇到问题。如何以最佳方式做到这一点？

示例

Alfred和洛塔都住在斯德哥尔摩（距离UTC +1小时，但DST时+ 2h
）。
沙龙住在新加坡（离UTC +8小时，DST没有
）

在冬季，Alfred会在凌晨4点设置警报。闹钟应在当地时间整年凌晨4点响起
。
在夏季，Lotta会在上午5点设置闹铃
。同样，它应该在全年的凌晨5点关闭。

同时，沙龙为上午11点设置了警报。

所有这些都可以

如果我冬天在数据库中查询应该在
03:00 UTC发出的警报，我想要阿尔弗雷德（Alfred）和沙龙（Sharon）的闹钟。新加坡现在来自瑞典+ 7h
，因此新加坡的上午11点是瑞典的凌晨4点。 Lotta的警报
不应再持续一个小时。

相反，如果我在夏天查询数据库以获取
应当在以下时间发出警报03:00 UTC，我想要Lotta和Sharon的闹钟。
新加坡现在距离瑞典+6小时，因此新加坡
瑞典现在上午11点是凌晨5点。斯文的闹钟在一个小时前响起。

如何存储它并查询数据库？

如有必要，我可以更改数据库模式。目前，我们根本不调整DST，实际上只有一个小时整数字段（这似乎很愚蠢，时间字段会更好）。

似乎我需要同时存储UTC时间和时区信息，但是我不知道如何在Postgres中最好地实现这一点。我发现Postgres具有某种时区概念，但据我所知，没有时区字段类型。另外，我想我需要在SQL中进行一些计算，以根据时区数据和创建日期来确定如何在选择中偏移UTC时间。我对SQL不太满意……

我确实想在Postgres中解决此问题，因为可能会有很多警报，并且我想避免这种性能将它们全部提取到Ruby并在那里进行过滤所带来的问题。 （是的，这是一个Rails应用。）

解决方案

使用带有时区的时间戳（ timestamptz ）进行计算。

警报时间可以为时间[无时区] 。

但是您必须为每行明确保存时区。

从不在带时区的时间中使用时间这是一种逻辑上已损坏的类型，PostgreSQL不鼓励使用。 手册：

带时区的时间的类型由SQL标准定义，但是
定义显示的属性导致有用的问题。
在大多数情况下，日期，时间，时间戳（不带时区）的组合，
和带时区的时间戳应该提供所有应用程序所需的
日期/时间功能的完整范围。 / p>

演示设置：

 创建TABLE闹钟（名称文字，t时间，tz文字）; 
插入警报值
（ Alfred， 04:00， Europe / Stockholm）-Alfred将警报设置为凌晨4点。 
，（乐天， 05:00，欧洲/斯德哥尔摩）-洛塔将闹钟设置为凌晨5点。 
，（莎伦， 11:00，亚洲/新加坡）； -Sharon已将闹钟设置为上午11点。 
  

必须使用时区名称（不是缩写） DST。相关：

• >具有相同属性的时区名称应用于时间戳时会产生不同的结果

获取今天的匹配警报：

  SELECT * 
 FROM警报
在（（'2012-07-01':: date + t ）时区 tz 时区'UTC'）:::时间
 = '03：00':: time  

• （'2012-7-1':: date + t） ...组装时间戳[无时区]
  也可能只是 <今天>的 now（）:: date + t 。


• AT时区tz ...将时间戳放置在已保存的时区，导致 timestamptz 。


• AT时区为'UTC' ...根据UTC获取时间戳


• :: time ...提取时间分量的最简单方法。

在这里您可以查找时区名称：

  SELECT * 
 FROM pg_timezone_names 
 WHERE name ~~ *'％sing％'
 LIMIT 10 
  

SQL小提琴 表示夏季/冬季。 / p>

I have a Postgres table of clock alarms (not really, but this is analogous, and easier to explain). Alarms are set by users with a 1 hour resolution, and users can be from many different timezones. The alarms are repeating daily. I want to reliably fetch the alarms that are supposed to go off at a particular hour of the day, and I am having problems with daylight saving time. How do I do this in the best way?

Example

Alfred and Lotta both live in Stockholm (+1 hour from UTC, but +2h when it's DST).
Sharon lives in Singapore (+8 hours from UTC, no DST)

During winter, Alfred sets an alarm for 4 AM. The alarm should go off at 4 AM local time, all year.
During summer, Lotta sets an alarm for 5 AM. Again, it should go off at 5 AM all year round.
Meanwhile, Sharon has set an alarm for 11 AM.

All of these can be stored in the database as 03:00 UTC.

If I query the database in the winter for alarms that should go off at 03:00 UTC, I want Alfred's and Sharon's alarms. Singapore is now +7h from Sweden, so 11 AM in Singapore is 4 AM in Sweden. Lotta's alarm should not go off for another hour.

Conversely, if I query the database in the summer for alarms that should go off at 03:00 UTC, I want Lotta's and Sharon's alarms. Singapore is +6h from Sweden now, so 11 AM in Singapore is 5 AM in Sweden now. Sven's alarm went off an hour ago.

How do I store this, and query the database?

I can change the db schema if necessary. At the moment, we don't adjust for DST at all, and in fact just have an "hour" integer field (which seems dumb, a time field would be better).

It seems I need to store both a UTC time and timezone information, but I don't know how to best achieve this in Postgres. I've found that Postgres has some sort of concept of timezones, but no timezone field type as far as I can tell. Also, I guess I need to do some calculations in SQL to determine how to offset the UTC time in the select, based on the timezone data and the creation date. I'm not great with SQL…

I do want to solve this in Postgres, as there can be a lot of "alarms", and I want to avoid the performance issues that come with fetching all of them into Ruby and filter there. (Yes, this is a Rails app.)

解决方案

Use timestamp with time zone (timestamptz) for calculations.
Times for alarms can be time [without time zone].
But you have to save the time zone explicitly for every row.

Never use time with time zone It's a logically broken type, its use is discouraged by PostgreSQL. The manual:

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, time, timestamp without timezone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.

Demo setup:

CREATE TABLE alarm(name text, t time, tz text);
INSERT INTO alarm VALUES
  ('Alfred', '04:00', 'Europe/Stockholm') -- Alfred sets an alarm for 4 AM.
, ('Lotta',  '05:00', 'Europe/Stockholm') -- Lotta sets an alarm for 5 AM. 
, ('Sharon', '11:00', 'Asia/Singapore');  -- Sharon has set an alarm for 11 AM.

It has to be time zone names (not abbreviations) to account for DST. Related:

• Time zone names with identical properties yield different result when applied to timestamp

Get matching alarms for "today":

SELECT *
FROM   alarm
WHERE  (('2012-07-01'::date + t) AT TIME ZONE tz AT TIME ZONE 'UTC')::time
       = '03:00'::time

• ('2012-7-1'::date + t) ... assemble timestamp [without time zone] Could also just be now()::date + t for "today".
• AT WITH TIME ZONE tz ... place timestamp at the saved time zone, resulting in timestamptz.
• AT WITH TIME ZONE 'UTC' ... get according UTC timestamp
• ::time ... simplest way to extract the time component.

Here you can look up time zone names:

SELECT *
FROM   pg_timezone_names
WHERE  name ~~* '%sing%'
LIMIT  10

SQL Fiddle demonstrating summer / winter.

这篇关于选择计划的项目时在Postgres中考虑DST的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！