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

Get "time with time zone" from "time without time zone" and the time zone name

提问by Damien Gabrielson

First off, I realize time with time zoneis not recommended. I am going to use it because I'm comparing multiple time with time zonevalues to my current system time regardless of day. I.e. a user says start everyday at 08:00 and finish at 12:00 with THEIR time zone, not the system time zone. So, I have a time without time zonecolumn in one table, let's call it SCHEDULES.timeand I have a UNIX time zone namecolumn in another table, let's call it USERS.tz.

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

My system time zone is 'America/Regina', which does not use DST and so the offset is always -06.

我的系统时区是'America/Regina'，它不使用 DST，所以偏移量总是-06。

Given a time of '12:00:00' and a tz of 'America/Vancouver' I would like to select the data into a column of type time with time zonebut I DO NOT want to convert the time to my time zone because the user has effectively said begin at when it is 12:00 in Vancouver, not in Regina.

给定时间为“12:00:00”和“America/Vancouver”的 tz，我想将数据选择到类型列中，time with time zone但我不想将时间转换为我的时区，因为用户已经有效地说从温哥华的 12:00 开始，而不是在里贾纳。

Thus, doing:

因此，做：

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

results (at the moment) in:

结果（目前）在：

'10:00:00-08'

but I really want:

但我真的想要：

'12:00:00-08'

I can't find any documentation relating to applying a time zone to a time, other then AT TIME ZONE. Is there a way to accomplish this without character manipulation or other hacks?

我找不到任何与将时区应用于时间相关的文档，除此之外AT TIME ZONE。有没有办法在没有字符操作或其他黑客的情况下实现这一目标？

UPDATE:This can be accomplished by using string concatenation, casting, and the Postgres time zone view as such:

更新：这可以通过使用字符串连接、转换和 Postgres 时区视图来完成：

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

However, this is fairly slow. There must be a better way, no?

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

UPDATE 2:I apologize for the confusion. The SCHEDULEStable DOES NOT use time with time zone, I am trying to SELECT a time with time zoneby combining values from a time without time zoneand a texttime zone name.

更新 2：我为造成的混乱道歉。该SCHEDULES表不使用time with time zone，我试图time with time zone通过组合来自 atime without time zone和text时区名称的值来选择 a 。

UPDATE 3:Thanks to all those involved for their (heated) discussion. :) I have been convinced to abandon my plan to use a time with time zonefor my output and instead use a timestamp with time zoneas it performs well, is more readable, and solves another problem that I was going to run into, time zones that roll into new dates. IE. '2011-11-21 23:59' in 'America/Vancouver' is '2011-11-22' in 'America/Regina'.

更新 3：感谢所有参与（热烈）讨论的人。:) 我已经被说服放弃使用 atime with time zone作为我的输出的计划，而是使用 atimestamp with time zone因为它表现良好，更具可读性，并解决了我将遇到的另一个问题，时区滚动到新日期。IE。“美国/温哥华”中的“2011-11-21 23:59”是“美国/里贾纳”中的“2011-11-22”。

UPDATE 4:As I said in my last update, I have chosen the answer that @MichaelKrelin-hacker first proposed and @JonSkeet finalized. That is, a timestamp with time zoneas my final output is a better solution. I ended up using a query like:

更新 4：正如我在上次更新中所说，我选择了@MichaelKrelin-hacker 最初提出并@JonSkeet 最终确定的答案。也就是说， atimestamp with time zone作为我的最终输出是一个更好的解决方案。我最终使用了如下查询：

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

The timezone()format was rewritten by Postgres after I entered (current_date + SCHEDULES.time) AT TIME ZONE USERS.tzinto my view.

该timezone()格式是由Postgres的后我进入改写(current_date + SCHEDULES.time) AT TIME ZONE USERS.tz了我的看法。

采纳答案by Jon Skeet

WARNING: PostgreSQL newbie (see comments on the question!). I know a bit about time zones though, so I know what makes senseto ask.

警告：PostgreSQL 新手（请参阅对问题的评论！）。不过，我对时区略知一二，所以我知道问什么是有意义的。

It looks to me like this is basically an unsupported situation (unfortunately) when it comes to AT TIME ZONE. Looking at the AT TIME ZONEdocumentation it gives a table where the "input" value types are only:

在我看来，当涉及到AT TIME ZONE. 查看AT TIME ZONE文档，它给出了一个表，其中“输入”值类型仅为：

• timestamp without time zone
• timestamp with time zone
• time with time zone

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

We're missing the one you want: time withouttime zone. What you're asking is somewhatlogical, although it does depend on the date... as different time zones can have different offsets depending on the date. For example, 12:00:00 Europe/London maymean 12:00:00 UTC, or it may mean 11:00:00 UTC, depending on whether it's winter or summer.

我们缺少您想要的：没有时区的时间。你问的有点合乎逻辑，虽然它取决于日期......因为不同的时区可以根据日期有不同的偏移量。例如，12: 00:00 Europe/London可能表示 12:00:00 UTC，也可能表示 11:00:00 UTC，具体取决于是冬天还是夏天。

On my system, having set the system time zone to America/Regina, the query

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

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

gives me 2011-11-22 14:00:00-06as a result. That's not ideal, but it does at least give the instant point in time (I think). I believe that if you fetched that with a client library - or compared it with another TIMESTAMP WITH TIME ZONE- you'd get the right result. It's just the text conversion that then uses the systemtime zone for output.

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

Would that be good enough for you? Can you either change your SCHEDULES.timefield to be a TIMESTAMP WITHOUT TIME ZONEfield, or (at query time) combine the time from the field with a date to create a timestamp without time zone?

这对你来说足够好吗？您是否可以将您的SCHEDULES.time字段更改为一个TIMESTAMP WITHOUT TIME ZONE字段，或者（在查询时）将字段中的时间与日期结合起来以创建一个没有时区的时间戳？

EDIT: If you're happy with the "current date" it lookslike you can just change your query to:

编辑：如果您对“当前日期”感到满意，您似乎可以将查询更改为：

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

Of course, the current systemdate may not be the same as the current date in the local time zone. I thinkthis will fix that part...

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

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

In other words:

换句话说：

• Take the current instant
• Work out the local date/time in the user's time zone
• Take the date of that
• Add the schedule time to that date to get a TIMESTAMP WITHOUT TIME ZONE
• Use AT TIME ZONEto apply the time zone to that local date/time

• 获取当前瞬间
• 计算用户所在时区的本地日期/时间
• 取那个日期
• 将计划时间添加到该日期以获得 TIMESTAMP WITHOUT TIME ZONE
• 使用AT TIME ZONE时间带适用于本地的日期/时间

I'm sure there's a better way, but I thinkit makes sense.

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

You should be aware that in some cases this could fail though:

您应该知道，在某些情况下，这可能会失败：

• What do you want the result to be for a time of 01:30 on a day when the clock skips from 01:00 to 02:00, so 01:30 doesn't occur at all?
• What do you want the result to be for a time of 01:30 on a day when the clock goes back from 02:00 to 01:00, so 01:30 occurs twice?

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

回答by Erwin Brandstetter

Here is a demo how to calculate the times without casting to text:

这是一个演示如何在不转换为文本的情况下计算时间：

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;

Basically you have to subtract the UTC offset and add the offset of your local time zone to arrive at the given time zone.

基本上，您必须减去 UTC 偏移量并添加本地时区的偏移量才能到达给定的时区。

You can speed up the calculation by hardcoding your local offset. In your case (America/Regina) that should be:

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

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;

As pg_timezone_namesis a view and not actually a system table, it is rather slow - just like the demonstrated variant with casting to text representation and back.

由于pg_timezone_names是视图而不是系统表，它相当慢 - 就像演示的变体一样，转换为文本表示并返回。

I would store the time zone abbreviations and take the double cast via textwithout joining in pg_timezone_namesfor optimum performance.

我会存储时区缩写并在text不加入的情况下pg_timezone_names进行双重转换以获得最佳性能。

---

---

FAST solution

快速解决方案

The culpritthat's slowing you down is pg_timezone_names. After some testing I found that pg_timezone_abbrevsis far superior. Of course, you have to save correcttime zone abbreviationsinstead of time zone names to achieve this. Time zone namestake DST into consideration automatically, time zone abbreviationsare basically just codes for a time offset. The documentation:

让你慢下来的罪魁祸首是pg_timezone_names。经过一些测试，我发现这pg_timezone_abbrevs要好得多。当然，您必须保存正确的时区缩写而不是时区名称才能实现此目的。时区名称会自动考虑 DST，时区缩写基本上只是时间偏移的代码。文档：

A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well.

时区缩写，例如PST。这样的规范仅定义了与 UTC 的特定偏移量，与完整时区名称形成对比，后者也可以暗示一组夏令时转换日期规则。

Have a look at these test results or try yourself:

看看这些测试结果或自己尝试：

SELECT * FROM  pg_timezone_names;

Total runtime: 541.007ms

总运行时间：541.007毫秒

SELECT * FROM pg_timezone_abbrevs;

Total runtime: 0.523ms

总运行时间：0.523毫秒

Factor 1000. Whether you go with your idea to cast to textand back to timetzor with my method to compute the time is not important. Both methods are very fast. Just don't use pg_timezone_names.

因子 1000。你是按照你的想法来text转换timetz还是用我的方法来计算时间并不重要。这两种方法都非常快。只是不要使用pg_timezone_names.

Actually, as soon as you save time zone abbreviations, you can take the casting route without anyadditional joins. Use the abbreviation instead of the utc_offset. Results are accurate as per your definition.

实际上，只要您保存了时区缩写，您就可以在没有任何额外连接的情况下采用投射路线。使用缩写代替 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;