在 Rails 和 PostgreSQL 中完全忽略时区

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

• 带时区的时间戳，短名称：timestamptz
• 不带时区的时间戳，短名称：timestamp

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

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

内部存储和纪元

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

Postgres 还内置了 UNIX 纪元 1970-01-01 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

对于时间戳，没有明确提供时区。Postgres 会忽略错误地添加到输入文字中的任何时区修饰符！

不会因显示而移动任何内容。由于一切都发生在同一个时区，这很好。对于不同的时区，含义会发生变化，但值和显示保持不变。

timestamptz

timestamptz 的处理略有不同。手册：

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

大胆强调我的。时区本身从不存储。它是一个输入修饰符，用于计算存储的相应UTC时间戳。或者输出装饰器，根据当前会话的设置进行时区偏移。timezone

对于没有附加偏移量的输入文本，假定设置了当前会话。所有计算均使用 UTC 时间戳值完成。如果可能涉及多个时区，或者可能存在任何疑问或误解，请使用 。适用于大多数用例。timezonetimestamptz

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

所有可识别时区的日期和时间都以 UTC 格式在内部存储。他们 在向客户端显示之前，将转换为 TimeZone 配置参数指定的区域中的本地时间。

psql 中的示例：

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

这里发生了什么？
带有（任意）时区偏移量的输入文字只是输入 UTC 时间戳的另一种方式。在我的测试中，将显示当前时区设置维也纳/奥地利的查询结果，该时区在冬季和夏令时（“夏令时”，DST）具有偏移量。因此，由于 DST 仅在今年晚些时候开始。+032012-03-05 17:00:00+01+022012-03-05 18:00:00+01

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

AT TIME ZONE

若要根据特定时区解释或表示时间戳文本，请使用 AT TIME ZONE 构造。 转换为，反之亦然。timestamptztimestamp

要将 UTC 获取为：2012-03-05 17:00:00+0timestamptz

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'

没错，两次。第一个将值解释为（给定的）UTC 时间戳，返回类型 。第二个转换为给定时区“EST”，即挂钟在此时间点在 EST 时区中显示的内容。AT TIME ZONE 'UTC'timestamptimestamptztimestamptztimestamp

例子

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 时间戳。第 9 行恰好在我的时区工作，但是一个邪恶的陷阱。timestamptz2012-03-05 17:00:00

（1） 第 6 - 8 行的时区名称和时区缩写为夏威夷时间，受 DST（夏令时）的约束，可能会有所不同，但目前不会。时区名称 like 会自动识别 DST 规则和所有历史班次，而缩写 like 只是固定偏移量的哑代码。您可能需要为夏令时/标准时间附加不同的缩写。该名称可正确解释给定时区的任何时间戳。缩写很便宜，但需要是给定时间戳的正确缩写：'US/Hawaii'HST

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

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

（2） 第 9 行，标记为上膛的脚枪恰好对我有用。对于输入，任何时区偏移都将被忽略！仅使用裸时间戳。然后，在示例中强制该值以匹配列类型。对于此步骤，假设当前会话的设置，对我来说恰好是欧洲/维也纳并匹配。但可能不是你的情况 - 这将导致不同的值。简而言之：不要将文字转换为，否则会丢失时区偏移量。timestamp [without time zone]timestamptztimezone+1timestamptztimestamp

您的问题

用户存储一个时间，比如 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)

为什么会发生所有的混乱

当你尝试实现类似的东西时，Rails 介入并将时间转换为服务器时间（很可能是 UTC），以将日期转换为时间戳（没有时区格式的时间戳）。这就是为什么所有跳舞和周围跳舞都是毫无意义的。.where("created_at > ?", YOUR_DATE_IN_THE_TIMEZONE)in_time_zone

为什么 iso8601 有效

当你调用时，你的日期被转换为字符串，Rails 无法“刹车”，必须按原样传递给 Postgres。iso8601

别忘了点赞！

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