从“没有时区的时间”中获取“带有时区的时间”和时区名称

Question

首先，我意识到不推荐使用 time with time zone。但我打算使用它来将多个基于time with time zone的值与当前系统时间进行比较，而不考虑日期。例如，用户说每天从他们所在时区的08:00开始到12:00结束。因此，在一个表中有一个 time without time zone 列，假设为 SCHEDULES.time，在另一个表中有一个UNIX 时区名称列，假设为 USERS.tz。

我的系统时区是 'America/Regina'，不使用夏令时，因此偏移始终为 -06。

假设有一个时间为'12:00:00' 和一个时区为'America/Vancouver'，我想选择数据到一个类型为 time with time zone 的列中，但我不想将时间转换为我的时区，因为用户实际上要求在温哥华12:00开始，而不是在里贾纳开始。

因此，执行以下操作：

SELECT SCHEDULES.time AT TIME ZONE USERS.tz
FROM SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID;

目前的结果为：

'10:00:00-08'

但是我真的想要：

'12:00:00-08'

除了使用AT TIME ZONE之外，我找不到任何关于将时区应用于时间的文档。是否有一种方法可以在不进行字符操作或其他技巧的情况下完成此操作？

更新： 可以通过使用字符串连接、类型转换和Postgres时区视图来实现此操作，例如：

select ('12:00:00'::text || utc_offset::text)::timetz
from pg_timezone_names
where name = 'America/Vancouver';

然而，这种方法相当慢。一定有更好的方法，不是吗？

更新2：我很抱歉造成混淆。 "SCHEDULES"表没有使用"time with time zone"，我正在尝试通过组合"time without time zone"和"text"时区名称的值来选择"time with time zone"。

更新3：感谢所有参与讨论的人（激烈的）:) 我已经被说服放弃使用"time with time zone"作为我的输出，改用"timestamp with time zone"，因为它的性能好，更易读，并解决了我将要遇到的另一个问题，即跨越新日期的时区。例如，在“America / Vancouver”中，“2011-11-21 23:59”是“America / Regina”中的“2011-11-22”。

更新4：如我在最后一次更新中所说，我选择了@MichaelKrelin-hacker首先提出并由@JonSkeet完成的答案。也就是说，"timestamp with time zone"是更好的解决方案作为我的最终输出。最终我使用了像下面这样的查询：

SELECT timezone(USERS.tz, now()::date + SCHEDULES.time)
FROM SCHEDULES
JOIN USERS ON USERS.ID = SCHEDULES.USERID;

在我将(current_date + SCHEDULES.time) AT TIME ZONE USERS.tz输入到视图中后，Postgres重新编写了timezone()的格式。

- Damien Gabrielson

---

@ruakh：在实际查询中，连接是绝对必要的 - 显然，如果出了问题，它会影响其他所有内容。 - Jon Skeet

这让我很感兴趣。听起来应该可以工作。我正在下载和安装PostgreSQL来尝试它。准备好迎接愚蠢的问题了... - Jon Skeet

他的观点是他并不真正知道它是如何工作的，但试图在其他地方找到问题。但他很好奇，想要弄清楚，我喜欢这种态度。说真的。 - Michael Krelin - hacker

@JonSkeet，他声明时间，并表示他选择了带有时区的时间。这意味着，时间以UTC时间和时区存储。 - Michael Krelin - hacker

@JonSkeet：好的，没问题。这不算过分，呵呵。 :-) - ruakh

Answer 1

警告：PostgreSQL 新手（请参见问题评论！）。我对时区有一些了解，因此知道该问什么。

在我看来，这基本上是一个不受支持的情况（不幸的是），当涉及到AT TIME ZONE时。查看AT TIME ZONE文档时，它提供了一个表格，其中“input”值类型仅为：

• 没有时区的时间戳
• 带时区的时间戳
• 带时区的时间

我们缺少你想要的一个：没有时区的时间。虽然您正在询问的内容可能是有点合理的，但这取决于日期......因为不同的时区可能会根据日期具有不同的偏移量。例如，欧洲/伦敦的 12:00:00 可能意味着 12:00:00 UTC，或者可能意味着 11:00:00 UTC，这取决于它是冬季还是夏季。

在我的系统上，将系统时区设置为 America/Regina 后，查询

SELECT ('2011-11-22T12:00:00'::TIMESTAMP WITHOUT TIME ZONE) 
                               AT TIME ZONE 'America/Vancouver'

我的结果是2011-11-22 14:00:00-06，这并不完美，但至少它给出了时间点（我想）。我认为如果你使用客户端库获取它，或将其与另一个TIMESTAMP WITH TIME ZONE进行比较，你会得到正确的结果。只是在输出时，它使用了系统时区进行文本转换。

对你来说是否足够好呢？你可以改变你的SCHEDULES.time字段为TIMESTAMP WITHOUT TIME ZONE字段，或者（在查询时）结合日期和该字段的时间来创建没有时区的时间戳。

编辑：如果你满意“当前日期”，那么看起来你可以只需更改查询为：

SELECT (current_date + SCHEDULES.time) AT TIME ZONE USERS.tz
from SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

当然，当前的系统日期可能与本地时区的当前日期不同。我认为这将解决这个问题...

SELECT ((current_timestamp AT TIME ZONE USERS.tz)::DATE + schedules.time)
       AT TIME ZONE USERS.tz
from SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

换句话说：

• 获取当前时刻
• 计算用户所在时区的本地日期/时间
• 取该日期
• 将预定时间加到该日期得到一个“没有时区的TIMESTAMP”
• 使用AT TIME ZONE将时区应用于该本地日期/时间

我相信有更好的方法，但我认为这是合理的。

您应该知道，在某些情况下，这种方法可能会失败:

• 当时钟从01:00跳跃到02:00时，例如01:30这个时间根本不存在，你希望结果是什么？
• 当时钟从02:00回退到01:00时，例如01:30这个时间出现了两次，你希望结果是什么？

- Jon Skeet

---

@MichaelKrelin-hacker：并不是这样 - 我最终建议SCHEDULES.time应该是一个TIMESTAMP WITHOUT TIME ZONE字段; 我在您（已删除的）答案中没有看到这一点。 - Jon Skeet

是的，因为正如你现在所知道的那样，我（错误地）假设它是没有时区的时间，正如你可以从结果中看到的那样，它的效果是一样的。 - Michael Krelin - hacker

@ruakh，14:00:00-06和14:00:00-06有什么不同？让我重复一遍——我说的是我开始回答的内容。 - Michael Krelin - hacker

@MichaelKrelin-hacker：如果你最初的建议确实给出了14:00:00-06，我认为那就没问题了。但是我相信它实际上给出的是12:00:00-06。无论如何，这是我在我的设备上看到的。你试过并看到了14:00:00-06吗？如果是这样，我会再试一次看看我搞错了什么... - Jon Skeet

@JonSkeet，是的，我试过了。但说真的——算了吧，我并不是真的想争论，而且我认为继续下去也不会对OP有所帮助。我已经尽力提供帮助了，现在轮到你了，你做得很好。 - Michael Krelin - hacker

Answer 2

以下是一个示例，演示如何在不将数字转换为文本的情况下计算时间：

CREATE TEMP TABLE schedule(t time, tz text);
INSERT INTO schedule values
 ('12:00:00', 'America/Vancouver')
,('12:00:00', 'US/Mountain')
,('12:00:00', 'America/Regina');

SELECT s.t AT TIME ZONE s.tz
        - p.utc_offset
        + EXTRACT (timezone from now()) * interval '1s'
FROM   schedule s
JOIN   pg_timezone_names p ON s.tz = p.name;

基本上，您需要减去UTC偏移量并添加本地时区的偏移量，以到达给定的时区。

可以通过硬编码本地偏移量来加快计算速度。在您的情况下（美国/里贾纳），应该是：

```html

基本上，您需要减去UTC偏移量并添加本地时区的偏移量，以到达给定的时区。

您可以通过硬编码本地偏移量来加快计算速度。在您的情况下（America/Regina），应该这样做：

```

SELECT s.t AT TIME ZONE s.tz
        - p.utc_offset
        - interval '6h'
FROM   schedule s
JOIN   pg_timezone_names p ON s.tz = p.name;

作为一个视图而不是系统表，pg_timezone_names 的速度相当慢，就像将其转换为文本表示形式并重新转换的演示变体一样。我建议存储时区缩写，并通过使用text来避免连接pg_timezone_names进行双重转换以获得最佳性能。

---

快速解决方案

你的效率问题主要集中在pg_timezone_names上。经过一些测试，我发现pg_timezone_abbrevs更优秀。当然，你需要保存正确的时区缩写而非时区名称才能实现这一点。时区名称自动考虑DST，时区缩写基本上只是时间偏移量的代码。详见文档:

例如PST的时区缩写。此类规范仅定义与UTC的特定偏移量，不同于可以暗示一组夏令时转换日期规则的完整时区名称。

请查看这些测试结果或自行尝试。

SELECT * FROM  pg_timezone_names;

总运行时间：541.007 毫秒

SELECT * FROM pg_timezone_abbrevs;

总运行时间：0.523 毫秒

放大1000倍。无论您选择将其转换为text并再次转换为timetz，还是使用我的方法计算时间，都没有关系。这两种方法都非常快速。只是不要使用pg_timezone_names。

实际上，一旦保存时区缩写，您可以采用转换路线而无需进行任何其他连接。使用缩写代替utc_offset。根据您的定义，结果是准确的。

CREATE TEMP TABLE schedule(t time, abbrev text);
INSERT INTO schedule values
 ('12:00:00', 'PST')  -- 'America/Vancouver'
,('12:00:00', 'MST')  -- 'US/Mountain'
,('12:00:00', 'CST'); -- 'America/Regina'

-- calculating
SELECT s.t AT TIME ZONE s.abbrev
     - a.utc_offset
     + EXTRACT (timezone from now()) * interval '1s'
FROM   schedule s
JOIN   pg_timezone_abbrevs a USING (abbrev);

-- casting (even faster!)
SELECT (t::text || abbrev)::timetz
FROM   schedule s;

- Erwin Brandstetter

---

我认为OP实际上想避免连接而不是字符操作，他抱怨查询速度慢，我认为这主要是因为连接的原因。 - Michael Krelin - hacker

@MichaelKrelin-hacker：时间偏移或时区的信息必须来自某个地方。 - Erwin Brandstetter

@JonSkeet: 我在这里引用文档：因此，相关信息会随着本地夏令时边界的变化而改变。显示的信息是基于CURRENT_TIMESTAMP当前值计算的。 - Erwin Brandstetter

听起来最好的存储时间的方式可能是使用 varchar。如果使用 timestamp 而不是 time，最好的方法可能是 ('2000-01-01 'time||' '||tz)::timestamp with time zone at time zone 'America/Regina'。 - Michael Krelin - hacker

@MichaelKrelin-hacker：存储text而不是time会带来小的性能优势，特别是使用casting方法时。如果您以hh24:mi格式保存它，它也会在磁盘上占用比time更少的空间（6个字节对于 time的8个字节）。但是您会失去自动类型约束和更快的计算舒适性。无论哪种方式，相对于性能而言都相对不重要。重要的部分在我的修改答案中。 - Erwin Brandstetter