Knex silently converts Postgres timestamps with timezone and returns incorrect time

Question

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

I data in the row is this 2018-06-27 15:45:00-03.

When running from psql console

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

this query returns "2018-06-27 18:45:00".

Similarly when I run

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

I get 2018-06-27 15:45:00

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

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

Answer 1

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.

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.

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).

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

It can be done like this (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);

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.

EDIT:

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';

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

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;

Which will return 2009-12-31 22:00:00+00.