带有时区的 PostgreSQL date()

PostgreSQL date() with timezone

提问by Matthew Lehner

I'm having an issue selecting dates properly from Postgres - they are being stored in UTC, but not converting with the Date() function properly.

我在从 Postgres 正确选择日期时遇到问题 - 它们以 UTC 格式存储，但没有使用 Date() 函数正确转换。

Converting the timestamp to a date gives me the wrong date if it's past 4pm PST.

如果超过太平洋标准时间下午 4 点，将时间戳转换为日期会给我错误的日期。

2012-06-21should be 2012-06-20in this case.

2012-06-21应该2012-06-20在这种情况下。

The starts_atcolumn datatype is timestamp without time zone. Here are my queries:

该starts_at列数据类型是timestamp without time zone。以下是我的疑问：

Without converting to PST timezone:

不转换为 PST 时区：

Select starts_at from schedules where id = 40;

      starts_at      
---------------------
 2012-06-21 01:00:00

Converting gives this:

转换给出了这个：

Select (starts_at at time zone 'pst') from schedules where id = 40;
        timezone        
------------------------
 2012-06-21 02:00:00-07

But neither convert to the correct date in the timezone.

但都没有转换为时区中的正确日期。

采纳答案by Erwin Brandstetter

I don't see the exacttype of starts_atin your question. You really should include this information, it is the key to the solution. I'll have to guess.

我在你的问题中没有看到确切的类型starts_at。你真的应该包括这些信息，它是解决方案的关键。我得猜。

Basically, PostgreSQL alwaysstores the UTC time value for the type timestamp with time zoneinternally. Only the display varies with your current timezonesetting. The effect of the AT TIME ZONEconstruct also changes with the underlying data type. More details:

基本上，PostgreSQL总是在timestamp with time zone内部存储该类型的 UTC 时间值。只有显示会因您当前的timezone设置而异。AT TIME ZONE构造的效果也随基础数据类型而变化。更多细节：

• Ignoring time zones altogether in Rails and PostgreSQL

• 在 Rails 和 PostgreSQL 中完全忽略时区

If you extract a datefrom type timestamp [without time zone], you get the date for the current time zone. The day in the output will be the same as in the display of the timestampvalue.

如果您date从 type 中提取 a timestamp [without time zone]，您将获得当前时区的日期。输出中的日期将与timestamp值显示中的相同。

If you extract a datefrom type timestamp with time zone(timestamptzfor short), the time zone offset is "applied" first. You still get the date for the current time zone, which agrees with the displayof the timestamp. The same point in time translates to the next day in parts of Europe, when it is past 4 p.m. in California for instance. To get the date for a certain time zone, apply AT TIME ZONEfirst.

如果date从类型timestamp with time zone（timestamptz简称）中提取 a ，则首先“应用”时区偏移量。您仍然会获得当前时区的日期，这与时间戳的显示一致。在欧洲部分地区，相同的时间点会转化为第二天，例如，在加利福尼亚州是下午 4 点。要获取某个时区的日期，AT TIME ZONE请先申请。

Therefore, what you describe at the top of the question contradicts your example.

因此，您在问题顶部描述的内容与您的示例相矛盾。

Given that starts_atis a timestamp [without time zone]and the time on your server is set to the local time. Test with:

鉴于这starts_at是一个timestamp [without time zone]并且您服务器上的时间设置为本地时间。测试：

SELECT now();

Does it display the same time as a clock on your wall? If yes (and the db server is running with correct time), the timezonesetting of your current session agrees with your local time zone. If no, you may want to visit the setting of timezonein your postgresql.confor your client for the session. Details in the manual.

它是否与墙上的时钟显示相同的时间？如果是（并且数据库服务器以正确的时间运行），则timezone您当前会话的设置与您当地的时区一致。如果没有，您可能需要访问timezone您postgresql.conf或您的客户端中的设置以进行会话。手册中的详细信息。

Be aware that the timezoneoffset used the opposite signof what's displayed in timestamp literals. See:

请注意，timezone偏移量使用了时间戳文字中显示的相反符号。看：

• Peculiar time zone handling in a Postgres database

• Postgres 数据库中的特殊时区处理

To get your local date from starts_atjust

starts_at仅从本地获取日期

SELECT starts_at::date

Tantamount to:

相当于：

SELECT date(starts_at)

BTW, your local time is at UTC-7 right now, not UTC-8, because daylight savings time is in effect (not among the brighter ideas of the human race).

顺便说一句，您的当地时间现在是 UTC-7，而不是 UTC-8，因为夏令时已经生效（不是人类更聪明的想法之一）。

Pacific Standard TIME (PST) is normally 8 hours "earlier" (bigger timestampvalue) than UTC (Universal Time Zone), but during daylight saving periods (like now) it can be 7 hours. That's why timestamptzis displayed as 2012-06-21 02:00:00-07in your example. The construct AT TIME ZONE 'PST'takes daylight saving time into account. These two expressions yield different results (one in winter, one in summer) and may result in different dates when cast:

太平洋标准时间 (PST) 通常timestamp比 UTC（通用时区）“早”8 小时（更大的值），但在夏令时（例如现在）可能是 7 小时。这就是为什么在您的示例中timestamptz显示的原因2012-06-21 02:00:00-07。该构造AT TIME ZONE 'PST'考虑了夏令时。这两个表达式产生不同的结果（一个在冬天，一个在夏天）并且可能会导致转换时的不同日期：

SELECT '2012-06-21 01:00:00'::timestamp AT TIME ZONE 'PST'
     , '2012-12-21 01:00:00'::timestamp AT TIME ZONE 'PST'

回答by AmitF

Basically what you want is:

基本上你想要的是：

$ select starts_at AT TIME ZONE 'UTC' AT TIME ZONE 'US/Pacific' from schedules where id = 40

I got the solution from this article is below, which is straight GOLD!!! It explains this non-trivial issue very clearly, give it a read if you wish to understand pstgrsql TZ management better.

我从下面的这篇文章中得到了解决方案，这是纯金！！！它非常清楚地解释了这个重要问题，如果您想更好地理解 pstgrsql TZ 管理，请阅读它。

Expressing PostgreSQL timestamps without zones in local time

在本地时间表示没有区域的 PostgreSQL 时间戳

Here is what is going on. First you should know that 'PST timezone is 8 hours behind UTC timezone so for instance Jan 1st 2014, 4:30 PM PST (Wed, 01 Jan 2014 16:00:30 -0800) is equivalent to Jan 2nd 2014, 00:30 AM UTC (Thu, 02 Jan 2014 00:00:30 +0000). Any time after 4:00pm in PST slips over to the next day, interpreted as UTC.

这是发生了什么。首先，您应该知道“太平洋标准时间时区比 UTC 时区晚 8 小时，因此例如太平洋标准时间 2014 年 1 月 1 日下午 4:30（2014 年 1 月 1 日星期三 16:00:30 -0800）”相当于 2014 年 1 月 2 日 00:30 AM UTC（2014 年 1 月 2 日星期四 00:00:30 +0000）。在 PST 下午 4:00 之后的任何时间都会滑到第二天，解释为 UTC。

Also, as Erwin Brandstetter mentioned above, postresql has two type of timestamps data type, one with a timezone and one without. If your timestamps include a timezone, then a simple:

此外，正如上面提到的 Erwin Brandstetter，postresql 有两种类型的时间戳数据类型，一种有时区，一种没有。如果您的时间戳包含时区，那么一个简单的：

$ select starts_at AT TIME ZONE 'US/Pacific' from schedules where id = 40

will work. However if your timestamp is timezoneless, executing the above command will not work, and you must FIRST convert your timezoneless timestamp to a timestamp with a timezone, namely a UTC timezone, and ONLY THEN convert it to your desired 'PST' or 'US/Pacific' (which are the same up to some daylight saving time issues. I think you should be fine with either).

将工作。但是，如果您的时间戳是无时区的，则执行上述命令将不起作用，您必须首先将无时区的时间戳转换为带时区的时间戳，即 UTC 时区，然后才将其转换为您想要的“PST”或“美国/ Pacific'（这与某些夏令时问题相同。我认为您应该可以接受）。

Let me demonstrate with an example where I create a timezoneless timestamp. Let's assume for convenience that our local timezone is indeed 'PST' (if it weren't then it gets a tiny bit more complicated which is unnecessary for the purpose of this explanation).

让我用一个例子来演示，我创建了一个无时区时间戳。为了方便起见，让我们假设我们的本地时区确实是“PST”（如果不是，那么它会变得稍微复杂一点，这对于本解释来说是不必要的）。

Say I have:

说我有：

$ select timestamp '2014-01-2 00:30:00' AS a, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AS b,  timestamp '2014-01-2 00:30:00' AT TIME ZONE 'UTC' AT TIME ZONE 'PST' AS c, timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d

This will yield:

这将产生：

"a"=>"2014-01-02 00:30:00"   (This is the timezoneless timestamp)
"b"=>"2014-01-02 00:30:00+00" (This is the UTC TZ timestamp, note that up to a timezone, it is equivalent to the timezoneless one)
"c"=>"2014-01-01 16:30:00" (This is the correct 'PST' TZ conversion of the UTC timezone, if you read the documentation postgresql will not print the actual TZ for this conversion)
"d"=>"2014-01-02 08:30:00+00"

The last timestamp is the reason for all the confusion regarding converting timezoneless timestamp from UTC to 'PST' in postgresql. When we write:

最后一个时间戳是有关在 postgresql 中将无时区时间戳从 UTC 转换为“PST”的所有混淆的原因。当我们写：

timestamp '2014-01-2 00:30:00' AT TIME ZONE 'PST' AS d

We are taking a timezoneless timestamp and try to convert it to 'PST TZ (we indirectly assume that postgresql will understand that we want it to convert the timestamp from a UTC TZ, but postresql has plans of its own!). In practice, what postgresql does is it takes the timezoneless timestamp ('2014-01-2 00:30:00) and treats it as if it WERE ALREADY a 'PST' TZ timestamp (i.e: 2014-01-2 00:30:00 -0800) and converts that to UTC timezone!!! So it actually pushes it 8 hours ahead instead of back! Thus we get (2014-01-02 08:30:00+00).

我们正在采用无时区时间戳并尝试将其转换为“PST TZ”（我们间接假设 postgresql 会理解我们希望它从 UTC TZ 转换时间戳，但 postresql 有自己的计划！）。实际上，postgresql 所做的是采用无时区时间戳 ('2014-01-2 00:30:00) 并将其视为已经是 'PST' TZ 时间戳（即：2014-01-2 00:30） :00 -0800) 并将其转换为 UTC 时区！！！所以它实际上是提前 8 小时而不是后推！因此我们得到 (2014-01-02 08:30:00+00)。

Anyway, this last (un-intuitive) behavior is the cause of all confusion. Read the article if you want a more thorough explanation, I actually got results which are a bit different then their on this last part, but the general idea is the same.

无论如何，这最后一个（不直观的）行为是所有混乱的原因。如果您想要更彻底的解释，请阅读这篇文章，实际上我得到的结果与最后一部分的结果略有不同，但总体思路是相同的。

回答by John Rennpferd

I know this is an old one but You may want to consider using AT TIME ZONE "US/Pacific" when casting to avoid any PST/PDT issues. So

我知道这是一个旧的，但您可能需要考虑在投射时使用 AT TIME ZONE "US/Pacific" 以避免任何 PST/PDT 问题。所以

SELECT starts_at::TIMESTAMPTZ AT TIME ZONE "US/Pacific" FROM schedules WHERE ID = '40';

SELECT starts_at::TIMESTAMPTZ AT TIME ZONE "US/Pacific" FROM schedules WHERE ID = '40';

回答by Bhushan Shimpi

cast(master.Stamp5DateTime as date) >= '05-05-2019' AND 

cast(master.Stamp5DateTime as date) <= '05-05-2019'