How to configure javascript pg library to interpret timestamps without timezone as UTC timestamps when reading from postgres

Question

My computer timezone offset is UTC/GMT +3 hours.

My table in database is defined as such.

CREATE TABLE public.order_invoices (
    created_at timestamp NOT NULL,
    updated_at timestamp NOT NULL,
    order_id uuid NOT NULL,
    document_id varchar(100) NULL,
    status varchar(20) NOT NULL,
    CONSTRAINT order_invoices_pkey PRIMARY KEY (order_id)
);

I execute such query:

  markInvoiceWaitingForApf(order: Order): Promise<void> {
    return this.sql.executeInTransaction(async tx => {
      await tx.executeStatement(
        `INSERT INTO order_invoices(order_id, status, created_at, updated_at) 
         VALUES ($1, $2, $3, CURRENT_TIMESTAMP)
         ON CONFLICT (order_id) DO
         UPDATE SET updated_at = CURRENT_TIMESTAMP, status = $2
         WHERE order_invoices.status = 'WAITING_FOR_APF'`,
        [order.id, OrderInvoiceStatus.WAITING_FOR_APF, parseISO(order.orderDate).toISOString()])
    })
  }

Under the hood this invokes I write to database using pg.ClientBase.query(sql, params), which is created using const client = await pg.Pool.connect()

Then I read it back, but instead of getting the string "2024-07-09T12:32:30.214Z" I get "2024-07-09T09:32:30.227Z". Here is some code that tests that and prints to console.

  // Arrange
  const order = randomOrder({ orderDate: new Date().toISOString() })
  console.log('HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH')
  console.log(parseISO(order.orderDate).toISOString())
  await t.context.sut.markInvoiceWaitingForApf(order)
  const temp = await t.context.testSql.getOrderInvoice(order.id)
  console.log(temp.updatedAt.toISOString())

resulting log:

HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH
2024-07-09T12:32:30.214Z
2024-07-09T09:32:30.227Z

It seems that this pg library for some inexplicable reason by default interprets postgres timestamps without timezone as local timestamps. How to configure pg library client object to retrieve all postgres timestamps without timestamp as either naive timestamps or, better yet, utc timestamps.

Answer 1

Indeed I used similar solution to @Adesoji Alu but a little more refined. I was waiting to see if it would be also possible to fix the issue of writing naive datetime via pg as UTC via Pool config option parseInputDatesAsUTC: true, but that thing does not work.

import * as pg from 'pg'
import { zonedTimeToUtc } from 'date-fns-tz'
import { parseISO } from 'date-fns'

const { types, Pool } = pg.default

export class UtcPoolFactory {
  constructor(private config: pg.PoolConfig) { }

  createPool(): pg.Pool {
    types.setTypeParser(1114, function (stringValue) {
      return zonedTimeToUtc(parseISO(stringValue), 'Etc/UTC');
    });
    return new Pool(this.config)
  }
}

Then instead of new Pool(config) use new UtcPoolFactory(config).create()