Timezone not set on pool event 'connect'

[sebastian-motavita]

Hello, I'm dealing with the Timezone setup for my app:

I'm using "pg": "^8.11.5", and PostgreSQL 16.2 DB version. Everything runs just fine, except when I'm trying to set a session TimeZone. In a SQL editor, you can set the TimeZone using:

SET TIME ZONE 'America/Bogota';

And similarly, that's what this documentation on Pool events suggests to do:

const pg = require("pg");
const dbConfig = require("../config/db.config.js");

const pool = new pg.Pool({
  user: dbConfig.USER,
  database: dbConfig.DB,
  password: dbConfig.PASSWORD,
  port: dbConfig.PORT,
  host: dbConfig.HOST,
});

// 'America/Bogota' = 'GMT-5' = 'UTC-5'
pool.on('connect', (client) => {
  client.query(`SET TIME ZONE 'America/Bogota'`)
})

exports.pool = pool;

However, that's not the case, the subsequent queries containing timestamp fields will yield the timestamp in UTC. For instance, with the configuration above, the next query will yield the timezone:

const pool = require('./pool');
const res = await pool.pool.query('SELECT *, now() as n FROM my_table');
console.log('DB timestamp is: ', res.rows[0].n); // yields DB timestamp is: 2024-06-19T19:13:11.919Z

But I'm expecting the returned timestamp to be 2024-06-19T14:13:11.919Z

I was able to verify the event was indeed called: I changed the TimeZone to 'America/Bogotaaaa', which raises an error in the DB as it's a non valid TimeZone:

ERROR:  invalid value for parameter "TimeZone": "America/Bogotaaaa"

Finally, I also tried to create a pool instance using this configuration:

const pool = new pg.Pool({
  Client: class extends pg.Client {
    getStartupConf() {
      return Object.assign(super.getStartupConf(), {
          TIMEZONE: 'America/Bogota', // Yields the same error if changed to an invalid value
      });
    }
  },
  user: dbConfig.USER,
  database: dbConfig.DB,
  password: dbConfig.PASSWORD,
  port: dbConfig.PORT,
  host: dbConfig.HOST,
});

[charmander]

The connection’s time zone affects things like the default cast of a timestamp to timestamptz, but JavaScript Date objects don’t have a time zone. (If you used .toString(), you’d see your local time instead of UTC.)

You might be interested in casting the value to text on the server with now()::text.

[sebastian-motavita]

That's definitely the reason, thank you, @charmander. Just as a follow up, I used the solution mentioned here to avoid the now()::text or .toString():

pg.types.setTypeParser(pg.types.builtins.TIME, (timeStr) => timeStr);
pg.types.setTypeParser(pg.types.builtins.TIMESTAMP, (timeStr) => timeStr);
pg.types.setTypeParser(pg.types.builtins.TIMESTAMPTZ, (timeStr) => timeStr);