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