Ruby-on-rails 在 Rails 和 PostgreSQL 中完全忽略时区

Ignoring time zones altogether in Rails and PostgreSQL

提问by 99miles

I'm dealing with dates and times in Rails and Postgres and running into this issue:

我正在处理 Rails 和 Postgres 中的日期和时间并遇到这个问题：

The database is in UTC.

数据库采用 UTC。

The user sets a time-zone of choice in the Rails app, but it's only to be used when getting the users local time for comparing times.

用户在 Rails 应用程序中设置了一个选择的时区，但它只在获取用户本地时间以比较时间时使用。

User stores a time, say March 17, 2012, 7pm. I don't want timezone conversions or the timezone to be stored. I just want that date and time saved. That way if the user changed their time zone, It would still show March 17, 2012, 7pm.

用户存储一个时间，比如 2012 年 3 月 17 日晚上 7 点。我不希望存储时区转换或时区。我只想保存那个日期和时间。这样，如果用户更改了他们的时区，它仍然会显示 2012 年 3 月 17 日，晚上 7 点。

I only use the users specified time zone to get records 'before' or 'after' the current time in the users local time zone.

我只使用用户指定的时区来获取用户本地时区当前时间“之前”或“之后”的记录。

I'm currently using 'timestamp without time zone' but when I retrieve the records, rails (?) converts them to the time zone in the app, which I don't want.

我目前正在使用“没有时区的时间戳”，但是当我检索记录时，rails (?) 将它们转换为应用程序中的时区，这是我不想要的。

Appointment.first.time
 => Fri, 02 Mar 2012 19:00:00 UTC +00:00 

Because the records in the database seem to come out as UTC, my hack is to take the current time, remove the time zone with 'Date.strptime(str, "%m/%d/%Y")' and then do my query with that:

因为数据库中的记录似乎以 UTC 格式出现，所以我的技巧是取当前时间，使用 'Date.strptime(str, "%m/%d/%Y")' 删除时区，然后执行我的操作查询：

.where("time >= ?", date_start)

It seems like there must be an easier way to just ignore time zones all around. Any ideas?

似乎必须有一种更简单的方法来忽略周围的时区。有任何想法吗？

回答by Erwin Brandstetter

The data type timestampis the short name for timestamp without time zone.
The other option timestamptzis short for timestamp with time zone.

数据类型timestamp是 的简称timestamp without time zone。
另一个选项timestamptz是timestamp with time zone.

timestamptzis the preferredtype in the date/time family, literally. It has typispreferredset in pg_type, which can be relevant:

timestamptz从字面上看，是日期/时间系列中的首选类型。它已typispreferred设置为pg_type，这可能是相关的：

• Generating time series between two dates in PostgreSQL

• 在 PostgreSQL 中生成两个日期之间的时间序列

Internal storage and epoch

内部存储和纪元

Internally, timestamps occupy 8 bytesof storage on disk and in RAM. It is an integer value representing the count of microseconds from the Postgres epoch, 2000-01-01 00:00:00 UTC.

在内部，时间戳在磁盘和 RAM 中占用8 个字节的存储空间。它是一个整数值，表示 Postgres 纪元 2000-01-01 00:00:00 UTC 的微秒计数。

Postgres also has built-in knowledge of the commonly used UNIX timecounting seconds from the UNIX epoch, 1970-01-01 00:00:00 UTC, and uses that in functions to_timestamp(double precision)or EXTRACT(EPOCH FROM timestamptz).

Postgres 还内置了有关 UNIX 时代 1970-01-01 00:00:00 UTC 中常用的UNIX 时间计数秒数的知识，并将其用于函数to_timestamp(double precision)或EXTRACT(EPOCH FROM timestamptz).

The source code:

源代码：

* Timestamps, as well as the h/m/s fields of intervals, are stored as
* int64 values with units of microseconds.  (Once upon a time they were  
* double values with units of seconds.)

And:

和：

/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */  
#define UNIX_EPOCH_JDATE        2440588 /* == date2j(1970, 1, 1) */  
#define POSTGRES_EPOCH_JDATE    2451545 /* == date2j(2000, 1, 1) */  

The microsecond resolution translates to a maximum of 6 fractional digits for seconds.

微秒分辨率最多可转换为 6 位小数位数。

timestamp

timestamp

A value typed as timestamp[without time zone]tells Postgres that no time zone is provided explicitly. The current time zone is assumed. Postgres ignoresany time zone modifier added by mistake!

类型为 as 的值告诉 Postgres 没有明确提供时区。假定当前时区。Postgres忽略任何错误添加的时区修饰符！timestamp[without time zone]

No hours are shifted for display. With the same time zone setting all is fine. For a different time zone setting the meaning changes, but valueand displaystay the same.

没有改变显示时间。使用相同的时区设置一切都很好。对于不同的时区设置，含义会发生变化，但值和显示保持不变。

timestamptz

timestamptz

Handling of timestamp with time zoneis subtly different. I quote the manual here:

处理timestamp with time zone方式略有不同。我在这里引用手册：

For timestamp with time zone, the internally stored value is always in UTC(Universal Coordinated Time ...)

对于timestamp with time zone，内部存储的值始终采用 UTC（世界协调时间...）

Bold emphasis mine. The time zone itself is never stored. It is an input modifier used to compute the according UTC timestamp, which is stored - or and output modifier used to compute the local time to display - with appended time zone offset. If you don't append an offset for timestamptzon input, the current time zone setting of the session is assumed. All computations are done with UTC timestamp values. If you have to (or may have to) deal with more than one time zone, use timestamptz.

大胆强调我的。该时区本身永远不会保存。它是一个输入修饰符，用于计算相应的 UTC 时间戳，该时间戳被存储 - 或用于计算要显示的本地时间的输出修饰符 - 附加时区偏移。如果您不为timestamptzon 输入附加偏移量，则假定会话的当前时区设置。所有计算均使用 UTC 时间戳值完成。如果您必须（或可能必须）处理多个时区，请使用timestamptz.

Clients like psql or pgAdmin or any application communicating via libpq(like Ruby with the pg gem) are presented with the timestamp plus offset for the current time zoneor according to a requestedtime zone (see below). It is always the same point in time, only the display format varies. Or, as the manual puts it:

像 psql 或 pgAdmin 这样的客户端或任何通过libpq 进行通信的应用程序（如 Ruby 与 pg gem）都会显示时间戳加上当前时区的偏移量或根据请求的时区（见下文）。它始终是同一时间点，只是显示格式不同。或者，正如手册所说：

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZoneconfiguration parameter before being displayed to the client.

所有时区感知日期和时间都以 UTC 内部存储。 在显示给客户端之前，它们将转换为TimeZone配置参数指定的区域中的本地时间。

Consider this simple example (in psql):

考虑这个简单的例子（在 psql 中）：

db=# SELECT timestamptz '2012-03-05 20:00+03';
      timestamptz
------------------------
 2012-03-05 18:00:00+01

Bold emphasis mine. What happened here?
I chose an arbitrary time zone offset +3for the input literal. To Postgres, this is just one of many ways to input the UTC timestamp 2012-03-05 17:00:00. The result of the query is displayedfor the current time zone setting Vienna/Austriain my test, which has an offset +1during winter and +2during summer time: 2012-03-05 18:00:00+01, because it falls into winter time.

大胆强调我的。这里发生了什么？
我+3为输入文字选择了任意时区偏移量。对于 Postgres，这只是输入 UTC 时间戳的众多方法之一2012-03-05 17:00:00。在我的测试中，针对当前时区设置Vienna/Austria显示查询结果，在冬季和夏季时间有偏移：，因为它属于冬季时间。+1+22012-03-05 18:00:00+01

Postgres has already forgotten how this value has been entered. All it remembers is the value and the data type. Just like with a decimal number. numeric '003.4', numeric '3.40'or numeric '+3.4'- all result in the exact same internal value.

Postgres 已经忘记了这个值是如何输入的。它只记住值和数据类型。就像十进制数一样。numeric '003.4',numeric '3.40'或numeric '+3.4'- 都产生完全相同的内部值。

AT TIME ZONE

AT TIME ZONE

As soon as you get a grasp on this logic, you can do anything you want. All that's missing now, is a tool to interpret or represent timestamp literals according to a specific time zone. That's where the AT TIME ZONEconstruct comes in. There are two different use cases. timestamptzis converted to timestampand vice versa.

一旦你掌握了这个逻辑，你就可以做任何你想做的事情。现在缺少的只是一种根据特定时区解释或表示时间戳文字的工具。这就是AT TIME ZONE构造的用武之地。有两种不同的用例。timestamptz转换为timestamp，反之亦然。

To enter the UTC timestamptz2012-03-05 17:00:00+0:

输入UTC timestamptz2012-03-05 17:00:00+0：

SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC'

... which is equivalent to:

...相当于：

SELECT timestamptz '2012-03-05 17:00:00 UTC'

To display the same point in time as EST timestamp(Eastern Standard Time):

要显示与 EST timestamp（东部标准时间）相同的时间点：

SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'EST'

That's right, AT TIME ZONE 'UTC'twice. The first one interprets the timestampvalue as (given) UTC timestamp returning the type timestamptz. The second one converts the timestamptzto the timestampin the given time zone 'EST' - what a clock in the time zone EST displays at this unique point in time.

没错，AT TIME ZONE 'UTC'两次。第一个将timestamp值解释为（给定的）UTC 时间戳，返回类型timestamptz。第二个转换timestamptz到timestamp在给定的时间区“EST” -什么在这个时间点独特的时区EST显示时钟。

Examples

例子

SELECT ts AT TIME ZONE 'UTC'
FROM  (
   VALUES
      (1, timestamptz '2012-03-05 17:00:00+0')
    , (2, timestamptz '2012-03-05 18:00:00+1')
    , (3, timestamptz '2012-03-05 17:00:00 UTC')
    , (4, timestamp   '2012-03-05 11:00:00'  AT TIME ZONE '+6') 
    , (5, timestamp   '2012-03-05 17:00:00'  AT TIME ZONE 'UTC') 
    , (6, timestamp   '2012-03-05 07:00:00'  AT TIME ZONE 'US/Hawaii')  -- ①
    , (7, timestamptz '2012-03-05 07:00:00 US/Hawaii')                  -- ①
    , (8, timestamp   '2012-03-05 07:00:00'  AT TIME ZONE 'HST')        -- ①
    , (9, timestamp   '2012-03-05 18:00:00+1')  -- ② loaded footgun!
      ) t(id, ts);

Returns 8 (or 9) identicalrows with a timestamptz columns holding the same UTC timestamp 2012-03-05 17:00:00. The 9th row sort of happens to work in my time zone, but is an evil trap. See below.

返回 8（或 9）个相同的行，其中 timestamptz 列包含相同的 UTC timestamp 2012-03-05 17:00:00。第 9 行恰好在我的时区工作，但它是一个邪恶的陷阱。见下文。

① Rows 6 - 8 with time zone nameand time zone abbreviationfor Hawaii time are subject to DST (daylight saving time) and might differ, though not currently. A time zone name like 'US/Hawaii'is aware of DST rules and all historic shifts automatically, while an abbreviation like HSTis just a dumb code for a fixed offset. You may need to append a different abbreviation for summer / standard time. The namecorrectly interprets anytimestamp at the given time zone. An abbreviationis cheap, but needs to be the right one for the given timestamp:

① 带有夏威夷时间时区名称和时区缩写的第6 - 8 行受 DST（夏令时）的约束，可能会有所不同，但目前没有。时区名称 like'US/Hawaii'自动了解 DST 规则和所有历史变化，而缩写 likeHST只是固定偏移量的愚蠢代码。您可能需要为夏季/标准时间附加不同的缩写。该名称正确解释给定时区的任何时间戳。一个缩写是便宜，但需要为给定的时间戳是正确的：

• Time zone names with identical properties yield different result when applied to timestamp

• 具有相同属性的时区名称在应用于时间戳时会产生不同的结果

_{Daylight Saving Time is not among the brightest ideas humanity ever came up with.}

_{夏令时并不是人类有史以来最聪明的想法之一。}

② Row 9, marked as loaded footgunworks for me, but only by coincidence. If you explicitly cast a literal to timestamp [without time zone], any time zone offset is ignored! Only the bare timestamp is used. The value is then automatically coerced to timestamptzin the example to match the column type. For this step, the timezonesetting of the current session is assumed, which happens to be the same time zone +1in my case (Europe/Vienna). But probably not in your case - which will result in a different value. In short: Don't cast timestamptzliterals to timestampor you lose the time zone offset.

②第9行，标记为加载footgun的作品对我来说，只是巧合。如果您显式地将文字转换为timestamp [without time zone]，则忽略任何时区偏移量！仅使用裸时间戳。然后timestamptz在示例中自动强制该值以匹配列类型。对于这一步，timezone假设当前会话的设置，+1在我的情况下恰好是相同的时区（欧洲/维也纳）。但在您的情况下可能不是 - 这将导致不同的值。简而言之：不要将timestamptz文字转换为timestamp或丢失时区偏移量。

Your questions

你的问题

User stores a time, say March 17, 2012, 7pm. I don't want timezone conversions or the timezone to be stored.

用户存储一个时间，比如 2012 年 3 月 17 日晚上 7 点。我不希望存储时区转换或时区。

Time zone itself is never stored. Use one of the methods above to enter a UTC timestamp.

时区本身永远不会被存储。使用上述方法之一输入 UTC 时间戳。

I only use the users specified time zone to get records 'before' or 'after' the current time in the users local time zone.

我只使用用户指定的时区来获取用户本地时区当前时间“之前”或“之后”的记录。

You can use one query for all clients in different time zones.
For absolute global time:

您可以对不同时区的所有客户端使用一个查询。
对于绝对全球时间：

SELECT * FROM tbl WHERE time_col > (now() AT TIME ZONE 'UTC')::time

For time according to the local clock:

根据当地时钟的时间：

SELECT * FROM tbl WHERE time_col > now()::time

Not tired of background information, yet? There is more in the manual.

还没有厌倦背景信息？手册中有更多内容。

回答by Dorian

If you want to deal in UTC by default:

如果您想默认使用 UTC 处理：

In config/application.rb, add:

在config/application.rb，添加：

config.time_zone = 'UTC'

Then, if you store the current user timezone name is current_user.timezoneyou can say.

然后，如果您存储当前用户的时区名称，current_user.timezone您可以说。

post.created_at.in_time_zone(current_user.timezone)

current_user.timezoneshould be a valid timezone name, otherwise you will get ArgumentError: Invalid Timezone, see full list.

current_user.timezone应该是有效的时区名称，否则您会得到ArgumentError: Invalid Timezone，请参阅完整列表。