在 Rails 和 PostgreSQL 中完全忽略时区

我正在处理 Rails 和 Postgres 的日期和时间，遇到了这个问题:

数据库是 UTC 格式的。

用户在 Rails 应用程序中设置选择的时区，但只有在获取用户本地时间以进行比较时才能使用。

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

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

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

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

因为数据库中的记录看起来像 UTC，所以我的方法是利用当前时间，删除带有‘ Date.strptime (str，“% m/% d/% Y”)’的时区，然后使用它进行查询:

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

似乎有更简单的方法可以忽略周围的时区，有什么办法吗？

51950 次浏览
小开
最佳答案

Postgres 有两种不同的时间戳数据类型:

timestamptz是日期/时间家族中的 更喜欢类型，字面意思是 typispreferred设置在 pg_type中，它可以是相关的:

内部存储和 新纪元

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

Postgres 还内置了从 UNIX 时代(1970-01-0100:00:00:00 UTC)开始通常使用的 UNIX 时间计秒器的知识，并在函数 to_timestamp(double precision)或 EXTRACT(EPOCH FROM timestamptz)中使用它。

源代码:

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

还有:

/* 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) */

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

timestamp

对于 timestamp，没有明确提供时区。Postgres 无视任何时区修饰符添加到输入文字错误！

展览时间不会改变。所有的事情都发生在同一个时区，这很好。对于不同的时区，意义会发生变化，但是 价值和 展示保持不变。

timestamptz

对 timestamptz的处理略有不同。 我在这里引用说明书:

对于 timestamp with time zone，内部存储的值是 总是在协调世界时(协调世界时...)

强调我的。时区本身从不存储.它是一个输入修饰符，用于计算相应的 UTC 时间戳，这个时间戳被存储——或者输出修饰符用于计算显示的本地时间——带有附加的时区偏移量。如果没有为输入追加 timestamptz的偏移量，则假定会话的当前时区设置。所有计算都使用 UTC 时间戳值完成。如果您(可能)需要处理多个时区，请使用 timestamptz。换句话说: 如果对假定的时区有任何疑问或误解，那么使用 timestamptz。适用于大多数用例。

像 psql 或 pgAdmin 这样的客户机，或者任何通过 Libpq通信的应用程序(像 Ruby 使用 pg gem) ，都会显示 当前时区的时间戳加偏移量，或者根据 请求的时区显示(见下文)。它总是 同一时间点，只是显示格式不同。或者，就像手册上说的那样:

所有时区感知的日期和时间都存储在 UTC 内部 转换为 TimeZone指定的区域中的当地时间 配置参数，然后才显示给客户端。

Psql 中的示例:

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

这里发生了什么？
我为输入文字选择了任意时区偏移量 +3。对于 Postgres，这只是输入 UTC 时间戳 2012-03-05 17:00:00的许多方法之一。在我的测试中，查询的结果是当前时区设置为 维也纳/奥地利的 展示，它在冬季偏移了 +1，在夏季偏移了 +2(“夏时制”，dST)。所以 2012-03-05 18:00:00+01作为 DST 只有在以后才会发挥作用。

Postgres 会立即忘记输入文字。它只记住数据类型的值。就像十进制数一样。numeric '003.4'或 numeric '+3.4'-两者的结果完全相同的内部值。

AT TIME ZONE

现在缺少的只是一个根据特定时区解释或表示时间戳文字的工具。这就是 强 > AT TIME ZONE强结构的用武之地。有两种不同的用例。timestamptz被转换成 timestamp，反之亦然。

输入 UTC timestamptz 2012-03-05 17:00:00+0:

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

相当于:

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

显示与 EST timestamp(东部标准时间)相同的时间点:

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

没错 AT TIME ZONE 'UTC' 两次。第一种方法将 timestamp值解释为(给定的) UTC 时间戳，返回类型 timestamptz。第二个转换 timestamptz到 timestamp在给定的时区‘ EST’-什么墙钟显示在时区 EST 在这个时间点。

例子

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

返回8(或9)个 一模一样行，其中 timestamptz 列包含相同的 UTC 时间戳 2012-03-05 17:00:00。第九排刚好在我的时区，但是是个邪恶的陷阱。请看下面。

第6-8行，夏威夷时区的时区为 < strong > name 和时区为 缩写，这两个时区会受到夏令时(夏时制)的影响，可能会有所不同，不过目前还没有。像 'US/Hawaii'这样的时区名称会自动识别 DST 规则和所有历史变化，而像 HST这样的缩写只是固定偏移量的哑代码。您可能需要为夏季/标准时间添加一个不同的缩写。姓名在给定的时区正确地解释 任何时间戳。缩写很便宜，但需要在给定的时间戳内是正确的:

夏时制不是人类有史以来最聪明的想法之一。

2排9，标记为 上膛的步枪工程 为了我，但只是巧合。如果您显式地将一个文字转换为 timestamp [without time zone]，忽略任何时区偏移量！只使用空时间戳。然后在示例中将该值自动强制转换为 timestamptz以匹配列类型。对于这个步骤，假设当前会话的 timezone设置，在我的例子(欧洲/维也纳)中，这恰好是相同的 +1时区。但可能不是在你的情况下-这将导致一个不同的价值。简而言之: 不要将 timestamptz字面值强制转换为 timestamp，否则会丢失时区偏移量。

你的问题

用户存储一个时间，比如2012年3月17日下午7点，我不想要时区 转换或要存储的时区。

时区本身从不存储。请使用上述方法之一输入 UTC 时间戳。

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

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

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

根据当地时钟的时间:

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

还不厌倦背景信息吗

小开

如果您希望默认处理 UTC:

在 config/application.rb中，添加:

config.time_zone = 'UTC'

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

post.created_at.in_time_zone(current_user.timezone)

current_user.timezone应该是一个有效的时区名称，否则将得到 ArgumentError: Invalid Timezone，请参阅 完整清单。

小开

不知道欧文的答案是否包含问题的解决方案(仍然包含大量有用的信息) ，但我有一个

短期解决方案:

(阅读时至少短一些)

.where("created_at > ?", (YOUR_DATE_IN_THE_TIMEZONE).iso8601)

为什么会这样

当您尝试实现像 .where("created_at > ?", YOUR_DATE_IN_THE_TIMEZONE) Rails 这样的东西时，可以将时间转换为服务器时间(很可能是 UTC) ，以便将日期转换为时间戳(没有时区格式的时间戳)。这就是为什么所有与 in_time_zone和周围的舞蹈是无意义的。

为什么 iso8601可以工作

当你调用 abc 0时，你的日期被转换成字符串，而 Rails 不能“刹车”，只能传递给 Postgres。

别忘了投赞成票！

小开

在我的 Angular/Type/Node API/PostgreSQL 环境中，我也有类似的难题和时间戳精度，这里是 完整的答案和解决方案


提交答案