在 Rails 和 PostgreSQL 中完全忽略时区

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

带时区的时间戳，简称：timestamptz

无时区的时间戳，简称：时间戳

timestamptz 是日期/时间系列中的首选 类型，字面意思。它在 pg_type 中设置了 typispreferred，这可能是相关的：

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

内部存储和时代

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

Postgres 还具有从 UNIX 纪元 1970 年 1 月 1 日 00:00:00 UTC 开始计数常用的 UNIX time 的内置知识，并在函数 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.)

和：

/* 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，没有明确提供时区。 Postgres 忽略任何错误地添加到输入文字的时区修饰符！

显示时间没有移动。一切都发生在同一时区，这很好。对于不同的时区，含义会发生变化，但值和显示保持不变。

时间戳记

timestamptz 的处理方式略有不同。 I quote the manual here：

对于带时区的时间戳，内部存储的值始终为 UTC（通用协调时间 ...）

大胆强调我的。 时区本身永远不会被存储。它是一个输入修饰符，用于计算相应的 UTC 时间戳，该时间戳被存储 - 或用于计算本地时间以显示的输出装饰器 - 附加时区偏移量。如果您未在输入中附加 timestamptz 的偏移量，则假定会话的当前时区设置。所有计算均使用 UTC 时间戳值完成。如果您（可能）必须处理多个时区，请使用 timestamptz。换句话说：如果对假定的时区有任何疑问或误解，请使用 timestamptz。适用于大多数用例。

psql 或 pgAdmin 之类的客户端或通过 libpq 进行通信的任何应用程序（例如带有 pg gem 的 Ruby）会显示当前时区或根据请求的时间戳加偏移量em> 时区（见下文）。它总是同一时间点，只是显示格式不同。或者，as the manual puts it：

所有可识别时区的日期和时间都以 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 的众多方法之一。在我的测试中，显示当前时区设置 Vienna/Austria 的查询结果，冬季偏移 +1，夏季偏移 +2 （“夏令时”，DST）。所以 2012-03-05 18:00:00+01 因为 DST 只是稍后才开始。

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

在时区

现在缺少的只是一个根据特定时区解释或表示时间戳文字的工具。这就是 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。第二个将给定时区“EST”中的 timestamptz 转换为 timestamp - 此时挂钟在时区 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）行 相同 行，其中包含相同 UTC 时间戳 2012-03-05 17:00:00 的 timestamptz 列。第 9 行恰好在我的时区工作，但这是一个邪恶的陷阱。见下文。

① 第 6 - 8 行的时区 name 和时区 缩写 为夏威夷时间，以 DST 为准（夏令时节省时间）并且可能会有所不同，尽管目前没有。像 'US/Hawaii' 这样的时区名称会自动识别 DST 规则和所有历史变化，而像 HST 这样的缩写只是固定偏移量的愚蠢代码。您可能需要为夏季/标准时间附加不同的缩写。 name 正确解释了给定时区的 any 时间戳。 缩写很便宜，但必须是给定时间戳的正确缩写：

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

夏令时并不是人类曾经提出的最聪明的想法之一。

② 第 9 行，标记为 loaded footgun为我工作，但这只是巧合。如果您将文字显式转换为 timestamp [without time zone]，则 any time zone offset is ignored！仅使用裸时间戳。然后在示例中将值自动强制为 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

还没有厌倦背景信息吗？ There is more in the manual.

---

如果您想默认以 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，请参阅 full list。

---

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

更短的解决方案：

（至少更短的阅读时间）

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

为什么所有的混乱都会发生

当您尝试实现 .where("created_at > ?", YOUR_DATE_IN_THE_TIMEZONE) 之类的东西时，Rails 仍然使用服务器时间（很可能是 UTC）将您的日期转换为时间戳（没有时区格式的时间戳）。这就是为什么您与 in_time_zone 之类的所有跳舞都是无用的。

为什么 iso8601 有效

当您调用 iso8601 时，您的日期将转换为 Rails 无法“制动”的字符串，并且必须按原样传递给 Postgres。

别忘了点赞！

---

我在 Angular/Typescript/Node API/PostgreSQL 环境中有类似的谜题和时间戳精度，这里是 complete answer and solution

---