Javascript Question posted in
A very good W3school tutorial can be found here.

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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


  2. 0

    In a nutshell TIMESTAMP WITHOUT TIME ZONE doesn’t save any timezone related information. if you give date time with timezone info,it takes date & time only and ignores timezone

    const { Pool } = require('pg');
const pgTypes = require('pg').types;

// PostgreSQL timestamp without timezone type ID
const TIMESTAMP_OID = 1114; // Correct OID for 'timestamp without time zone'
// Override the default parser for 'timestamp without time zone'
pgTypes.setTypeParser(TIMESTAMP_OID, (stringValue) => {
  // Append 'Z' to indicate UTC
  return new Date(`${stringValue}Z`);
});

const pool = new Pool({
  // connection parameters
});

async function getTimestamps() {
  const { rows } = await pool.query('SELECT created_at FROM public.order_invoices');
  rows.forEach(row => {
    console.log(row.created_at.toISOString()); // This should now correctly display UTC time
  });
}

getTimestamps();

    see this documentation here: https://www.postgresql.org/docs/current/datatype-datetime.html

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search