Knex静默转换带时区的Postgres时间戳并返回不正确的时间 [英] Knex silently converts Postgres timestamps with timezone and returns incorrect time

问题描述

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

I have a table in my psql database with a "trigger_time" column of type "TIMESTAMP WITH TIME ZONE DEFAULT now()"

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

从psql控制台运行时

When running from psql console

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

此查询返回"2018-06-27 18:45:00".

this query returns "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

Using knex.raw("SELECT trigger_time AT TIME ZONE 'America/Glace_Bay' FROM tasks WHERE task_id = 1") I get 2018-06-27T18:45:00.000Z and when running knex.raw("SELECT trigger_time AT TIME ZONE 'UTC' FROM tasks WHERE task_id = 1") I get 2018-06-27T21:45:00.000Z

这些来自knex的结果都不正确，如何使knex停止静默更改数据?

Both of these results from knex are incorrect, how do I get knex to stop silently altering my data?

推荐答案

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

Probably things are failing because when you are querying datetimes from database in certain timezone and effectively converting type of timestamp to be timestamp without timezone. In that case database will not send information to knex about in which timezone that returned time was.

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

So knex (or rather pg driver which knex is using) interprets your timestamp as local time, which depends of timezone setup of your application server running knex.

您可以像获取UTC一样获取时间，并在带有时间矩或luxon库的JavaScript端中进行时区转换(IMO后者对于时区处理更好).

You could fetch time just as UTC and do timezone conversion in JavaScript side with moment or luxon libraries (IMO latter is better for timezone handling).

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

Other solution would be to tell pg driver that timestamp and timestamp with timezone types should not be converted to JavaScript Date objects.

可以像这样( 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的开头，可以添加使所有时间戳作为字符串返回的代码.这些返回的字符串将与数据库服务器本身返回的格式完全相同.

This code which makes all timestamps to be returned as strings may be added to for example in start of knexfile.js. Those returned strings will be exactly in the same format that they were returned by database server itself.

在原始帖子的代码中，将时间戳转换为时区UTC时，数据库服务器将timestamp with time zone类型转换为常规timestamp without time zone，因此返回的值不包含时区信息.要向后添加时区信息，您可以例如在返回的时间戳的末尾附加+02，如下所示:

In code in the original post, when timestamp is converted to be in time zone UTC database server converts timestamp with time zone type to be normal timestamp without time zone so returned value doesn't have timezone information. To add timezone information back you can for example append +02 to the end of returned time stamp like this:

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

哪个将2010-01-01 00:00:00+00返回给驱动程序，也可以被pg驱动程序正确读取.

Which returns 2010-01-01 00:00:00+00 to the driver which can be read correctly by pg driver too.

这将有效地执行与创建连接时在db服务器中设置SET TIME ZONE 'UTC';并直接返回timestamptz列相同的操作:

This will effectively do the same thing that just setting SET TIME ZONE 'UTC'; in db server when connection is created and just returning timestamptz column directly:

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

哪个会返回2009-12-31 22:00:00+00.

这篇关于Knex静默转换带时区的Postgres时间戳并返回不正确的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！