fixPostgresTimezone: true doesn't correctly fix TIMESTAMPTZ parsing - timestamps shifted by local timezone offset

[tonxxd]

When using ZenStack v3.x with PostgreSQL (via Supabase pooler), the fixPostgresTimezone: true option doesn't correctly handle TIMESTAMPTZ columns. Timestamps are shifted by the local timezone offset (e.g., +1 hour in CET timezone).

Environment

• ZenStack version: 3.1.1
• Database: PostgreSQL via Supabase (pooler connection)
• Node.js: 20.x
• Timezone: CET (UTC+1)

Steps to Reproduce

1. Create a table with a TIMESTAMPTZ column:

CREATE TABLE api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2. Insert a row (database stores correct UTC time, e.g., 09:08:57+00)

3. Query using ZenStack client with fixPostgresTimezone: true:

import { ZenStackClient } from "@zenstackhq/orm";
import { PostgresDialect } from "kysely";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

const db = new ZenStackClient(schema, { 
  dialect: new PostgresDialect({ pool }),
  fixPostgresTimezone: true,
});

const keys = await db.apiKey.findMany();
console.log(keys[0].createdAt.toISOString());
// Expected: "2026-01-14T09:08:57.186Z" (correct UTC)
// Actual:   "2026-01-14T10:08:57.186Z" (shifted by +1 hour)

Expected Behavior

Database stores 09:08:57+00 (UTC), API should return 2026-01-14T09:08:57.186Z

Actual Behavior

Database stores 09:08:57+00 (UTC), API returns 2026-01-14T10:08:57.186Z (1 hour ahead - local timezone offset added)

The issue is in how node-pg parses TIMESTAMPTZ. PostgreSQL returns:

"2026-01-14 09:08:57.186674+00"

node-pg creates a Date object interpreting this as local time instead of UTC. The fixPostgresTimezone option attempts to fix this, but it seems to apply the fix incorrectly or at the wrong stage.

Workaround

Use a custom type parser for TIMESTAMPTZ (OID 1184) and disable fixPostgresTimezone:

import { Pool, types } from "pg";

// Fix node-pg timezone issue
types.setTypeParser(1184, (val: string) => {
  // PostgreSQL returns: "2026-01-14 09:03:40.287162+00"
  // Convert to ISO format: "2026-01-14T09:03:40.287162Z"
  return new Date(val.replace(" ", "T").replace("+00", "Z"));
});

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

const db = new ZenStackClient(schema, { 
  dialect: new PostgresDialect({ pool }),
  fixPostgresTimezone: false, // Disable - we handle it manually
});

This correctly returns 2026-01-14T09:08:57.186Z.

Additional Context

• Reference: Wrong timestamp! brianc/node-postgres#429
• The custom type parser runs BEFORE ZenStack processes the data
• With fixPostgresTimezone: true, ZenStack appears to "fix" an already-correct Date, causing double-conversion

[alexbatis]

@tonxxd - im experiencing the same. this is the zenstack 2.x repository. i created an issue in the zenstack 3.x repository here linking to your issue: zenstackhq/zenstack-v3#606