在PostgreSQL中，我们可以直接将两个时间戳与不同的时区进行比较吗？

SELECT * FROM table_a 
WHERE time_1 >= to_timestamp('11/01/2014 10:00 PDT', 'MM/DD/YYYY HH24:MI TZ')

time_1 is in UTC time zone and it is a timestamp with timezone. So, will this give me what I want or I need to do exact UTC time in the function to_timestamp()?

time_1是UTC时区，是带时区的时间戳。那么，这会给我提供我想要的东西，或者我需要在函数to_timestamp（）中执行确切的UTC时间吗？

1 个解决方案

#1

Postgresql has two different timestamp data types and it is confusing which one should be used when. The two types are:

Postgresql有两种不同的时间戳数据类型，令人困惑的是应该使用哪一种。这两种类型是：

timestamp (also known as timestamp without time zone) It is most likely that this is the type in table_a
timestamp（也称为没有时区的时间戳）很可能这是table_a中的类型
timestamp with time zone This is the data type returned by to_timestamp()
带时区的时间戳这是to_timestamp（）返回的数据类型

You must be sure that you are comparing apples with apples or pairs with pairs and not mix them or you may get undesirable results.

您必须确保将苹果与苹果或成对进行比较，而不是将它们混合，否则可能会产生不良结果。

If your table_a.time_1 is a timestamp with time zone then the code you give in your question will work fine.

如果您的table_a.time_1是带时区的时间戳，那么您在问题中提供的代码将正常工作。

If your table_a.time_1 is a timestamp then you will need to change your code:

如果您的table_a.time_1是时间戳，那么您需要更改代码：

SELECT * 
  FROM table_a 
 WHERE time_1 >= to_timestamp('11/01/2014 10:00 PDT', 'MM/DD/YYYY HH24:MI TZ') at time zone 'utc';

The last part of this (at time zone 'utc') will strip the timezone (PDT) off the specified timestamp and translate the timestamp to UTC.

最后一部分（在时区'utc'）将从指定的时间戳中删除时区（PDT）并将时间戳转换为UTC。

Edit: to help with your comments in this answer...

编辑：在这个答案中帮助您的评论......

In order to understand how to translate time zones you need to understand the difference between the two forms of time stamp. It will become clear why you need to understand this below. As I indicate above the difference between the two forms of time stamp is confusing. There is a good manual page but for now just read on.

为了理解如何翻译时区，您需要了解两种时间戳形式之间的区别。很明显为什么你需要在下面理解这一点。如上所述，两种形式的时间戳之间的差异令人困惑。有一个很好的手册页，但现在只需阅读。

The main thing to understand is that neither version actually stores a time zone (despite the name). The naming would make much more sense if you added an extra word "translation". Think "timestamp without time zone translation" and "timestamp with time zone translation".

要理解的主要是两个版本都没有实际存储时区（尽管名称）。如果你添加一个额外的单词“翻译”，命名会更有意义。想想“没有时区转换的时间戳”和“带时区转换的时间戳”。

A timestamp with time zone translation doesn't store a time zone at all. It is designed to store time stamps which could come from anywhere in the world and not loose track of their meaning. So when entering one you must provide the time zone it came from or postgresql will assume it came from the time zone of your current session. Postgresql automatically translates it out of the given time zone into an internal time zone for the server. You don't need to know what time zone that is because postgresql will always translate it back from this internal time zone before giving you the value. When you retrieve the value (eg: SELECT my_time FROM foo) postgresql translates the time stamp to the time zone of your current session. Alternatively you can specify the time zone to translate into (eg: SELECT my_time AT TIME ZONE 'PDT' FROM foo).

具有时区转换的时间戳根本不存储时区。它旨在存储时间戳，这些时间戳可以来自世界上任何地方，而不是松散的意义。因此，当输入一个时，你必须提供它来自的时区，或者postgresql将假定它来自你当前会话的时区。 Postgresql自动将其从给定时区转换为服务器的内部时区。您不需要知道什么时区，因为postgresql将始终从此内部时区将其转换回来，然后再为您提供值。当您检索该值（例如：SELECT my_time FROM foo）时，postgresql会将时间戳转换为当前会话的时区。或者，您可以指定要翻译的时区（例如：SELECT my_time AT TIME ZONE'PPT'FROM foo）。

With that in mind it's easier to understand that a timestamp without time zone translation will never be changed from the time you specify. Postgresql will regard 11:00:00 as happening before 12:00:00 even if you meant 11 in America and 12 in England. It's easy to see why that may not be what you want.

考虑到这一点，更容易理解没有时区转换的时间戳永远不会从您指定的时间更改。 Postgresql会将11:00:00视为在12:00:00之前发生，即使你的意思是美国的11和英格兰的12。很容易理解为什么这可能不是你想要的。

A very common programming error is to think that a timestamp with time zone is at a particular time zone. It isn't. It is at whatever time zone you ask for it to be. And if you don't specify what time zone you want it at then postgresql will assume you want it at your current session time zone.

一个非常常见的编程错误是认为带有时区的时间戳位于特定时区。事实并非如此。它是在你要求它的任何时区。如果你没有指定你想要的时区，那么postgresql将假设你想要它在你当前的会话时区。

You've stated that your field is a timestamp with time zone which are all at UTC. This isn't technically correct. Most likely your session time zone is UTC and postgresql is giving you everything in UTC as a result.

您已声明您的字段是带时区的时间戳，均为UTC。这在技术上是不正确的。很可能您的会话时区是UTC，postgresql会以UTC结尾为您提供所有内容。

So you have a timestamp with time zone and you want to know what these times are in PDT? Easy: SELECT my_time AT TIME ZONE 'PDT' FROM foo.

所以你有一个带时区的时间戳，你想知道这些时间在PDT中是什么？简单：从foo选择my_time AT TIME ZONE'PPT'。

It's important to understand that the AT TIME ZONE '...' syntax toggles between timestamp and timestamp with time zone.

重要的是要了解AT TIME ZONE'...'语法在时间戳和时区之间切换时区。

timestamp AT TIME ZONE 'PDT' converts into a timestamp with time zone and tells postgresql to convert to the PDT time zone.
时间戳AT TIME ZONE'PDT'转换为带时区的时间戳，并告诉postgresql转换为PDT时区。
timestamp with time zone AT TIME ZONE 'PDT' converts into a timestamp telling postgresql to interpret it as coming from 'PDT'.
带有时区的时间戳AT TIME ZONE'PDT'转换为时间戳，告诉postgresql将其解释为来自'PDT'。

This symetry means that to reverse AT TIME ZONE 'foo' you just use AT TIME ZONE 'foo'. Put another way SELECT anything AT TIME ZONE 'PDT' AT TIME ZONE 'PDT' will always leave anything unchanged.

这个symetry意味着要反转AT TIME ZONE'foo'你只需使用AT TIME ZONE'foo'。另一种方式选择任何时间区域'PDT'时间区域'PDT'将始终保持不变。

#1