PostgreSQL date（）与时区 [英] PostgreSQL date() with timezone

问题描述

我有一个问题从Postgres正确选择日期 - 它们以UTC存储，但
不能正确地转换为Date（）函数。

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

将时间戳转换为日期给我错误的日期，如果它是PST太多下午4点。

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

2012-06-21 应该是 2012-06-20 。

starts_at 列数据类型是没有时区的时间戳。以下是我的查询：

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

不转换为PST时区：

Select starts_at from schedules where id = 40;

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

转换给出：

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.

推荐答案

在您的问题中，我看不到 starts_at 的确切类型。你真的应该包括这个信息，这是解决方案的关键。我必须猜测。

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

基本上，PostgreSQL 始终存储时间类型为时间戳的UTC时间值区域内部。只有显示因您目前的时区设置而异。 AT TIME ZONE 也会使用底层数据类型进行更改。 此相关答案中的详细信息。

Basically, PostgreSQL always stores the UTC time value for the type timestamp with time zone internally. Only the display varies with your current timezone setting. The effect of the AT TIME ZONE construct also changes with the underlying data type. More details in this related answer.

如果您提取来自 的日期时间戳[无时区] ，您将获取当前时区的日期。输出中的日期与显示 timestamp 值相同。

If you extract a date from 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 timestamp value.

如果您提取来自 的日期时间戳与时区 （ timestamptz ），时区偏移首先被应用。您仍然可以获取当前时区的日期，这与时间戳的显示一致。同样的时间点在欧洲的部分地区，例如在加利福尼亚州的下午4点。要获取某个时区的日期，请首先应用 AT TIME ZONE 。

If you extract a date from type timestamp with time zone (timestamptz for short), the time zone offset is "applied" first. You still get the date for the current time zone, which agrees with the display of 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 ZONE first.

因此，您在

鉴于 starts_at 是一个 timestamp [无时区] ，服务器上的时间设置为本地时间。测试：

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

SELECT now();

它与墙上的时钟显示时间相同吗？如果是，您当前会话的时区设置与您当地的时区一致。如果否，您可能需要在 postgresql.conf 中访问 timezone 的设置。或者你的客户端可以做什么呢？ （可以每个会话设置）。手册中的详细信息。

Does it display the same time as a clock on your wall? If yes, the timezone setting of your current session agrees with your local time zone. If no, you may want to visit the setting of timezone in your postgresql.conf. Or maybe your client does something to the setting? (Can be set per session.) Details in the manual.

要从 starts_at 获取您当地的日期

SELECT starts_at::date

Tantamount to：

Tantamount to:

SELECT date(starts_at)

当你的当地时间是UTC-7，而不是UTC-8，因为夏令时是有效的（不在人类的更明亮的想法之中）。

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).

太平洋标准时间（PST）通常比UTC（通用时区）早8小时，但在夏令时期间（如现在）为7小时。这就是为什么 timestamptz 显示为 2012-06-21 02:00:00 -07 。结构 AT TIME ZONE'PST'需要考虑夏令时。这两个表达式产生不同的结果（一个在冬天，一个在夏天），并且可能导致不同的日期：

Pacific Standard TIME (PST) is normally 8 hours earlier than UTC (Universal time zone), but during daylight saving periods (like now) it's 7 hours. That's why timestamptz is displayed as 2012-06-21 02:00:00-07 in 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:

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

这篇关于PostgreSQL date（）与时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！