我正在处理 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)
似乎必须有一种更简单的方法来忽略周围的时区。有任何想法吗?
Postgres 有两种不同的时间戳数据类型:
timestamp with time zone
timestamptz
timestamp without time zone
timestamp
timestamptz从字面上看,是日期/时间系列中的 首选 类型。它已typispreferred设置在 中pg_type
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).
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 没有明确提供时区。Postgres会 忽略 任何错误地添加到输入文字的时区修饰符!
显示时间没有移动。一切都发生在同一时区,这很好。对于不同的时区, 含义 会发生变化,但 值 和 显示 保持不变。
处理 timestamptz 方式略有不同。我在这里引用手册:
对于timestamp with time zone,内部存储的值 始终为 UTC (通用协调时间 …)
大胆强调我的。 时区本身永远不会被 存储。它是一个输入修饰符,用于计算相应的 UTC 时间戳,该时间戳被存储 - 或用于计算本地时间以显示的输出装饰器 - 附加时区偏移量。如果您不为timestamptz输入附加偏移量,则假定会话的当前时区设置。所有计算均使用 UTC 时间戳值完成。如果您(可能)必须处理多个时区,请使用timestamptz. 换句话说:如果对假定的时区有任何疑问或误解,请选择timestamptz. 适用于大多数用例。
像 psql 或 pgAdmin 这样的客户端或通过libpq进行通信的任何应用程序(如带有 pg gem 的 Ruby)会显示 当前时区 的时间戳加上偏移量或根据 请求 的时区(见下文)。它总是 相同的时间点 ,只是显示格式不同。或者,正如手册所说:
所有可识别时区的日期和时间都以 UTC 格式在内部存储。TimeZone 在显示给客户端之前,它们会转换为配置参数指定的区域中的本地时间。
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 只是稍后才开始。
+3
2012-03-05 17:00:00
+1
+2
2012-03-05 18:00:00+01
Postgres 会立即忘记输入文字。它只记住数据类型的值。就像十进制数一样。numeric '003.4'或numeric '+3.4'- 两者都产生完全相同的内部值。
numeric '003.4'
numeric '+3.4'
AT TIME ZONE
现在缺少的只是一个根据特定时区解释或表示时间戳文字的工具。这就是 AT TIME ZONE 构造的用武之地。有两种不同的用例。timestamptz转换为timestamp,反之亦然。
要输入 UTC timestamptz 2012-03-05 17:00:00+0:
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。第二个将 转换为timestamptz给timestamp定时区“EST”中的时间 - 挂钟此时在 EST 时区显示的内容。
AT TIME ZONE 'UTC'
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只是固定偏移量的愚蠢代码。您可能需要为夏季/标准时间附加不同的缩写。该 名称 可以正确解释给定时区的 任何 时间戳。 缩写 很便宜,但必须是给定时间戳的正确缩写
'US/Hawaii'
HST
夏令时并不是人类曾经提出的最聪明的想法之一。
——第 9 行,标记为 上膛 的足枪 对我 有用,但这只是巧合。如果您明确地将文字转换为timestamp [without time zone],则忽略任何时区偏移!仅使用裸时间戳。然后在示例中自动强制该值timestamptz匹配列类型。对于这一步,timezone假设当前会话的设置,在我的情况下恰好是同一时区+1(欧洲/维也纳)。但可能不是您的情况 - 这将导致不同的值。简而言之:不要将timestamptz文字转换为,timestamp否则您会丢失时区偏移量。
timestamp [without time zone]
timezone
用户存储一个时间,例如 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
还没有厌倦背景信息吗?手册中还有更多内容。