小编典典

在 Rails 和 PostgreSQL 中完全忽略时区

all

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

数据库采用 UTC。

用户在 Rails 应用程序中设置选择的时区,但仅在获取用户本地时间以比较时间时使用。

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

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

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

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

因为数据库中的记录似乎以 UTC 的形式出现,所以我的 hack 是采用当前时间,使用 ‘Date.strptime(str, “%m/%d/%Y”)’
删除时区,然后执行我的查询:

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

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


阅读 96

收藏
2022-08-24

共1个答案

小编典典

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

timestamptz从字面上看,是日期/时间系列中的 首选 类型。它已typispreferred设置在
pg_type

内部存储和时代

在内部,时间戳在磁盘和 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).

源代码:

* 时间戳以及间隔的 h/m/s 字段存储为
* int64 值,以微秒为单位。(从前他们是  
* 以秒为单位的双精度值。)

和:

/* Unix 和 Postgres 计算中第 0 天的儒略日期等价物 */  
#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 (通用协调时间 …)

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

像 psql 或 pgAdmin 这样的客户端或通过libpq进行通信的任何应用程序(如带有 pg gem 的
Ruby)会显示
当前时区
的时间戳加上偏移量或根据 请求 的时区(见下文)。它总是 相同的时间点
,只是显示格式不同。或者,正如手册所说

所有可识别时区的日期和时间都以 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'

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

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

没错,AT TIME ZONE 'UTC' 两次 。第一个将timestamp值解释为(给定的)UTC
时间戳,返回类型timestamptz。第二个将 转换为timestamptztimestamp定时区“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)个 相同 的行,其中包含相同 UTC 时间戳的 timestamptz 列2012-03-05 17:00:00。第 9
行恰好在我的时区工作,但这是一个邪恶的陷阱。见下文。

③第6-8行的时区 _ 名称 和夏威夷时间的时区 _ 缩写 受DST(夏令时)影响,可能会有所不同,但目前没有。时区名称
like'US/Hawaii'自动知道 DST 规则和所有历史变化,而缩写
likeHST只是固定偏移量的愚蠢代码。您可能需要为夏季/标准时间附加不同的缩写。该 名称 可以正确解释给定时区的 任何 时间戳。 缩写
很便宜,但必须是给定时间戳的正确缩写

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

——第 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

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

2022-08-24