sql - 从 "time with time zone"和时区名称中获取 "time without time zone"

Question

首先，我意识到不推荐使用 time with time zone。我要使用它是因为我将多个 time with time zone 值与我当前的系统时间进行比较，而不管是哪一天。 IE。用户说每天 08:00 开始，12:00 结束，使用他们的时区，而不是系统时区。所以，我在一个表中有一个 time without time zone 列，我们称它为 SCHEDULES.time 并且我有一个 UNIX time zone name 列在另一个表中，我们称它为 USERS.tz。

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

给定时间“12:00:00”和时间“美国/温哥华”，我想将数据选择到 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 时区 View 来完成:

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

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

更新 2:对于造成的困惑，我深表歉意。 SCHEDULES 表不使用 time with time zone，我试图通过组合 中的值来选择 time with time zone time without time zone 和 text 时区名称。

更新 3:感谢所有参与(激烈)讨论的人。 :) 我已经被说服放弃我的计划，使用 time with time zone 作为我的输出，而是使用 timestamp with time zone 因为它表现良好，更具可读性，并解决了我将要遇到的另一个问题，即进入新日期的时区。 IE。 “美国/温哥华”中的“2011-11-21 23:59”是“美国/里贾纳”中的“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 到我的 View 后，Postgres 重写了 timezone() 格式。

Answer 1

警告:PostgreSQL 新手(请参阅问题评论!)。不过，我对时区了解一些，所以我知道问什么有意义。

在我看来，当涉及到 AT TIME ZONE 时，这基本上是一种不受支持的情况(不幸的是)。看着 AT TIME ZONE文档它给出了一个表，其中“输入”值类型仅为:

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

我们缺少您想要的:没有时区的时间。你问的是有点合乎逻辑的，虽然它确实取决于日期......因为不同的时区可以有不同的偏移量，具体取决于日期。例如，12:00:00 Europe/London可能表示 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 WITHOUT TIME ZONE
• 使用 AT TIME ZONE 将时区应用于本地日期/时间

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

你应该知道在某些情况下这可能会失败:

• 当时钟从 01:00 跳到 02:00 时，您希望一天 01:30 的结果是什么，所以 01:30 根本不会出现？
• 当时钟从 02:00 倒回到 01:00，所以 01:30 出现两次时，您希望一天 01:30 的结果是什么？