Timestamps are inconsistent with Postgres behavior

[nscott]

Hi there,

I have a table with the TIMESTAMP(6) column type. I run SET TIME ZONE 'UTC'; on PGlite at the start of each connection, although this happens with or without that command. I write a date to the database in that table, and unexpectedly, PGlite gives back a date that is the difference between UTC and my local time.

The WASM Postgres module itself seems to appropriately be storing and returning the data from the DB as expected, but I think the TS package is handling dates incorrectly.

pglite/packages/pglite/src/types.ts

Lines 131 to 146 in f94d591

	date: {
	to: TIMESTAMPTZ,
	from: [DATE, TIMESTAMP, TIMESTAMPTZ],
	serialize: (x: Date | string | number) => {
	if (typeof x === 'string') {
	return x
	} else if (typeof x === 'number') {
	return new Date(x).toISOString()
	} else if (x instanceof Date) {
	return x.toISOString()
	} else {
	throw new Error('Invalid input for date type')
	}
	},
	parse: (x: string | number) => new Date(x),
	},

When the date is serialized, it's called with .toISOString(), which converts the local date to UTC. When that string gets stored in a TIMESTAMP column the timezone is dropped.

Then, in parse() it returns new Date(x). This is parsing a zone-less timestamp, giving back a date that is the difference between UTC and the local node timezone.

This differs from standard Postgres behavior (at least in my testing), where the read and write gives the same time, regardless of zone of client. It instead gives back a datetime based on whatever zone Postgres is in.

My suggestion would be to avoid writing toISOString(), which does the UTC conversion, and instead write it in ISO8601 format with the TZ offset included:

function formatDate(d: Date): string {
    const year = d.getFullYear();
    const month = zeroPrefix(d.getMonth() + 1); // 0-indexed for Jan
    const day = zeroPrefix(d.getDate());

    const hour = zeroPrefix(d.getHours());
    const mins = zeroPrefix(d.getMinutes());
    const secs = zeroPrefix(d.getSeconds());
    const millis = zeroPrefix(d.getMilliseconds(), 3);

    // Note that `.getTimezoneOffset` is misleading.
    // No matter what 'timezone' the date is in, it always gives back the
    // difference between UTC and what the current machine's TZ would display on the date.
    // E.g. if it was parsed as America/Los Angeles and you're in America/New York, it will always
    // show a difference in either EST or EDT depending on the date, NOT PST/PDT.
    // Facepalm, right? All vanilla JS dates are just unix epochs with sugar.
    // https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/getTimezoneOffset
    let timezone = 'Z';
    const offsetMins = d.getTimezoneOffset();
    if (offsetMins !== 0) {
        const tzMins = offsetMins % 60;
        const tzHours = Math.floor(offsetMins / 60);
        let sign = '+';
        if (offsetMins > 0) {
            // It's the diffence in what you need to add get to GMT, so if you're e.g. GMT -5,
            // the offset is 300, not -300.
            sign = '-';
        }
        timezone = `${sign}${zeroPrefix(tzHours)}:${zeroPrefix(tzMins)}`
    }

    return `${year}-${month}-${day}T${hour}:${mins}:${secs}.${millis}${timezone}`;
}

function zeroPrefix(n: number, places: number = 2): string {
    const nStr = n + '';
    return `${'0'.repeat(Math.max(0, places - nStr.length))}${nStr}`
}

I tested this locally by overriding PGlite's serializers for types.TIMESTAMP and used the code above. Indeed, it fixed my problem.

In the mean time, the simplest work around is to make sure the TZ environment variable is set to UTC. This will tell node to interpret everything as UTC, and then everything clicks without a problem.

That works fine on servers, but it's unexpected for local development.