javascript - Knex 静默转换带有时区的 Postgres 时间戳并返回不正确的时间

Question

我的 psql 数据库中有一个表，其中包含类型为“TIMESTAMP WITH TIME ZONE DEFAULT now()”的“trigger_time”列

我行中的数据是这个 2018-06-27 15:45:00-03。

从 psql 控制台运行时

SELECT trigger_time AT TIME ZONE 'UTC' 
FROM tasks 
WHERE task_id = 1;

此查询返回“2018-06-27 18:45:00”。

我运行的时候也是这样

SELECT trigger_time AT TIME ZONE 'America/Glace_Bay' 
FROM tasks 
WHERE task_id = 1;

我得到 2018-06-27 15:45:00

使用 knex.raw("SELECT trigger_time AT TIME ZONE 'America/Glace_Bay' FROM tasks WHERE task_id = 1") 我得到 2018-06-27T18:45:00.000Z 当运行 knex.raw("SELECT trigger_time AT TIME ZONE 'UTC' FROM tasks WHERE task_id = 1") 我得到 2018-06-27T21:45:00.000Z

knex 的这两个结果都不正确，如何让 knex 停止悄悄更改我的数据？

Answer 1

可能事情失败了，因为当您在特定时区从数据库查询日期时间并将时间戳类型有效地转换为没有时区的时间戳时。在那种情况下，数据库不会向 knex 发送有关返回时间所在时区的信息。

因此 knex(或者更确切地说，knex 使用的 pg 驱动程序)将您的时间戳解释为本地时间，这取决于运行 knex 的应用程序服务器的时区设置。

您可以像 UTC 一样获取时间，并在 JavaScript 端使用 moment 或 luxon 库进行时区转换(IMO 后者更适合时区处理)。

其他解决方案是告诉 pg 驱动程序时间戳和时区类型的时间戳不应转换为 JavaScript Date 对象。

可以这样做(https://github.com/brianc/node-pg-types):

const types = require('pg').types;
const TIMESTAMPTZ_OID = 1184;
const TIMESTAMP_OID = 1114;
types.setTypeParser(TIMESTAMPTZ_OID, val => val);
types.setTypeParser(TIMESTAMP_OID, val => val);

此代码可将所有时间戳作为字符串返回，例如可以添加到 knexfile.js 的开头。这些返回的字符串将与数据库服务器本身返回的格式完全相同。

编辑:

在原始帖子的代码中，当时间戳被转换为时区 UTC 数据库服务器将 timestamp with time zone 类型转换为正常的 timestamp without时区 所以返回值没有时区信息。要添加回时区信息，您可以像这样将 +02 附加到返回的时间戳的末尾:

select ('2010-01-01T00:00:00.000Z'::timestamptz AT TIME ZONE 'UTC')::text || '+00';

返回 2010-01-01 00:00:00+00 给 pg 驱动也可以正确读取的驱动。

这将有效地完成与在创建连接时在数据库服务器中设置 SET TIME ZONE 'UTC'; 并直接返回 timestamptz 列相同的事情:

SET TIME ZONE 'UTC';
select '2010-01-01T00:00:00.000+02:00'::timestamptz;

这将返回 2009-12-31 22:00:00+00。