Timestamp type parser breaks if timezone is not GMT

[NicolasCaous]

I'm from São Paulo, so my default timezone is UTC-3. Whenever I try to use the timestamp field, its values are returned ahead of time (exactly the time difference between my local time and GMT).

Expected Behavior

The timestamp returned from a select query should be the time stored inside the database.

Current Behavior

The timestamp returned from a select query is ahead of the true value stored in the database by exactly the difference from my local time to GMT.

Possible Solution

Timezone conversion error, can't pinpoint because I couldn't understand the library source code. However, timestamptz works flawlessly. So a possible solution is to check why timestamptz behavior is different from timestamp when the GMT timezone is used.

Steps to Reproduce

1 - Be in a system with a different timezone from GMT

2 -

// https://momentjs.com/timezone/
const moment = require("moment-timezone");

await pool.query(sql`create table "test" ( "field" timestamp )`)

let date = new Date()
await pool.query(sql`insert into "test" ("field") values (${moment.utc(date).format("YYYY-MM-DD HH:mm:ss.SSS")})`)

let dbDate = new Date((await pool.query(sql`select * from "test"`)).rows[0].field)

console.log(date)
console.log(dbDate)

Logs

Welcome to Node.js v14.15.1.      
Type ".help" for more information.
> const moment = require("moment-timezone");
undefined
> await pool.query(sql`create table "test" ( "field" timestamp )`)
{ command: 'CREATE', fields: [], notices: [], rowCount: 0, rows: [] }
> let date = new Date()
undefined
> await pool.query(sql`insert into "test" ("field") values (${moment.utc(date).format("YYYY-MM-DD HH:mm:ss.SSS")})`)
{ command: 'INSERT', fields: [], notices: [], rowCount: 1, rows: [] }
> let dbDate = new Date((await pool.query(sql`select * from "test"`)).rows[0].field)
undefined
> console.log(date)
2020-12-04T22:26:14.113Z
undefined
> console.log(dbDate)
2020-12-05T01:26:14.113Z
undefined

My local time: 2020-12-04 19:26:00
UTC time: 2020-12-04 22:26:00

[aklotos]

So apparently the database has the correct UTC timestamp stored and the difference comes from library conversion logic.

By default slonik is configured with set of default type parsers including the timestamp type parser. The default timestamp type parser implementation converts a string representation of the timestamp from the database into unix timestamp using Date.parse(). As you can see in the docs Date.parse supports number of different formats and can parse date-time strings with and without the timezone indicator. And since postgres timestamp data type doesn't store the timezone indicator the library receives the timestamp string as is in the format: YYYY-MM-DD hh:mm:ss.sss. So when Date.parse parses this date-time string it parses it as datetime in your local timezone (not as UTC datetime). The solution is to "tell" the date parser that the datetime string is in UTC timezone before parsing it:

const timestampParser = { name: 'timestamp', parse: value => Date.parse(`${value} UTC`) }; // or `${value}Z`
const clientConfiguration = { typeParsers: [timestampParser] };
const pool = createPool(connectionUri, clientConfiguration);

or even better
if you need a Date object returned you don't need to convert string to unix timestamp and then unix timestamp to Date explicitly, type parser can do it for you:

const timestampParser = { name: 'timestamp', parse: value => new Date(`${value} UTC`) };
const clientConfiguration = { typeParsers: [timestampParser] };
const pool = createPool(connectionUri, clientConfiguration);

[gajus]

🎉 This issue has been resolved in version 24.0.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀

[C-l-o-u-d]

@gajus here UTC was wrongly added to the timestamp with time zone

1e9a484#diff-967766d7babb268d6e22ea686e9fe005f2bd15b2235d762cc61db0479590a278R6

> select TIMESTAMP '2004-10-19 10:23:54';
      timestamp      
---------------------
 2004-10-19 10:23:54
(1 row)

> select TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54';
      timestamptz       
------------------------
 2004-10-19 10:23:54+04
(1 row)

i.e. result expession will be Date.parse('2004-10-19 10:23:54+04 UTC')

> Date.parse('2004-10-19 10:23:54+04')
1098167034000
> Date.parse('2004-10-19 10:23:54+04 UTC')
1098181434000