adapter-pg: Date objects incorrectly stored in Timestamptz fields

[Maxime-J]

Bug description

Follow up to #26786 with some precisions,
it's not a retrieval issue, dates are not stored correctly in db if db timezone isn't set to UTC.

Kinda severe for a pushed up feature which is now the default with v7, and a clear regression.
That's why I create a new issue.
It's mitigated though as DBs are commonly set to UTC.

Issue:
Date object intended to be stored in a Timestamptz field is stored as UTC in db timezone, see problematic behavior below.

Severity

⚠️ Major: Breaks core functionality (e.g., migrations fail)

Reproduction

With a database set to a timezone other than UTC, here America/New_York (-5), see docker-compose.

schema.prisma

generator client {
  provider = "prisma-client"
  output   = "generated"
}

datasource db {
  provider = "postgresql"
}

model Test {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @map("created_at") @db.Timestamptz(6)
}

test.js

import 'dotenv/config';
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from './prisma/generated/client.js';

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
const prisma = new PrismaClient({ adapter })

await prisma.test.create({
  data: {
    createdAt: new Date(),
  },
});

Problematic Behavior

Prisma logs an UTC date "2025-11-21T10:23:41.021Z"
But db receives and stores that time as if was in its own timezone

SELECT * FROM "Test";
 id |        created_at
----+----------------------------
  1 | 2025-11-21 10:23:41.021-05
(1 row)

Without PrismaPg, old v6 way using 'prisma-client-js' generator,
it's correctly stored as UTC

SELECT * FROM "Test";
 id |         created_at
----+----------------------------
  1 | 2025-11-21 05:23:41.021-05
(1 row)

Frequency

Consistently reproducible

Does this occur in development or production?

Both development and production

Is this a regression?

Yes, works correctly the old way, without adapters.

Environment & Setup

docker-compose.yml

---
services:
  db:
    image: postgres:15-alpine
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: db
      POSTGRES_USER: db
      POSTGRES_PASSWORD: db
      TZ: America/New_York
    command:
      - "postgres"
      - "-c"
      - "log_statement=all"
    volumes:
      - db-data:/var/lib/postgresql/data
volumes:
  db-data:

Prisma Version

7.0.0, but every version using Query Compiler is concerned.

[dchenk]

Seeing the same corruption issue here.

Package versions

• @prisma/client: 7.0.0
• @prisma/adapter-pg: 7.0.0
• Node: v24.4.0
• PostgreSQL :16.4 (on MacOS)

Problem

When querying a TIMESTAMPTZ column through a nested relation, the value Prisma returns is shifted to UTC without applying the original offset. Direct queries on the same column return the correct instant. That discrepancy shows up as a six‑hour difference (our local offset) between two Date objects that reference the same row.

Minimal schema

model Page {
  id          String   @id @default(uuid())
  siteId      String
  path        String
  publishedAt DateTime? @db.Timestamptz(6)
  site        Site     @relation(fields: [siteId], references: [id])
}

model Site {
  id    String  @id @default(uuid())
  domain String @unique
  pages Page[]
}

Reproduction

const site = await prisma.site.create({
  data: {
    domain: 'example.com',
    pages: {
      create: [
        {
          path: '',
          publishedAt: new Date(),
        },
      ],
    },
  },
  include: { pages: { select: { publishedAt: true } } },
});

const direct = await prisma.page.findFirstOrThrow({
  select: { publishedAt: true },
  where: { siteId: site.id, path: '' },
});

console.log('direct', direct.publishedAt instanceof Date, direct.publishedAt?.toISOString());
// direct true 2025-11-24T09:26:34.887Z

console.log(
  'relation',
  site.pages[0].publishedAt instanceof Date,
  site.pages[0].publishedAt?.toISOString(),
);
// relation true 2025-11-24T15:26:34.887Z  <-- shifted by +6h (local offset)

Suspected cause

@prisma/adapter-pg/dist/index.js defines:

function normalize_timestamptz(time) {
  return time.replace(" ", "T").replace(/[+-]\d{2}(:\d{2})?$/, "+00:00");
}

Instead of converting the value to UTC, it simply swaps whatever offset Postgres returned for +00:00. So a session-local 2025-11-24 09:26:34.887-06 turns into the string 2025-11-24T09:26:34.887+00:00, which describes a different instant. When Node parses that string, the resulting Date ends up six hours early.

[biryusal]

@prisma/client: 7.2.0
@prisma/adapter-pg: 7.2.0
NodeJS: v22.13.0
PostgreSQL: 15 (MacOS)

Date from Prisma:  2025-12-22T08:34:53.542Z
Date from Kysely:  2025-12-26T05:09:03.240Z

Both dates are UTC so the should be equal (column is timestamptz) – but Prisma shifted it by 5h (local offset)

[jalik]

Suspected cause

@prisma/adapter-pg/dist/index.js defines:

function normalize_timestamptz(time) {
return time.replace(" ", "T").replace(/[+-]\d{2}(:\d{2})?$/, "+00:00");
}

Instead of converting the value to UTC, it simply swaps whatever offset Postgres returned for +00:00. So a session-local 2025-11-24 09:26:34.887-06 turns into the string 2025-11-24T09:26:34.887+00:00, which describes a different instant. When Node parses that string, the resulting Date ends up six hours early.

This is so ridiculous... how this code could land in a release ?
There must be a test for this specific case so it won't happen again by inadvertence or lack of experience.

Can we get a workaround while waiting for an official fix ?

[matingathani]

Hi — I've submitted a fix for both the write and read bugs in #29481.

Write bug (this issue): formatDateTime emitted no timezone suffix, so PostgreSQL treated the value as being in the server's local timezone. Fix: always append +00:00 to the formatted string. For TIMESTAMP WITHOUT TIME ZONE columns PostgreSQL silently ignores timezone suffixes, so this is a safe no-op for those columns.

Read bug (#26786): normalize_timestamptz was also fixed in the same PR — it was swapping the offset label without converting the time, causing the symmetric read-side shift.

Both fixes are covered by updated unit tests and a new functional regression test that sets the session timezone to America/New_York to force both bugs to manifest.

[julish13]

Confirming this on @prisma/adapter-pg@7.4.1 with Node 20 and Postgres 17 (Homebrew on macOS). Macbook system TZ is Europe/Moscow, so postgres started by brew services inherits non-UTC as session TZ by default — the bug manifests silently in the local dev environment without any explicit ALTER DATABASE, which makes it easy to miss until data hits a UTC-configured staging/prod.

Repro with schema model Thing { createdAt DateTime @default(now()) @db.Timestamptz(3) }: row written via PrismaPg stores created_at AT TIME ZONE 'UTC' shifted by -3h from the new Date() value observed in the client at insert time.

Workaround while waiting for #29481: pin session TZ on the adapter itself, independently of the server config —

new PrismaPg({
  connectionString: DATABASE_URL,
  options: "-c timezone=UTC",
});

This makes Postgres interpret the naive string the driver sends as UTC, so the stored instant matches the input. Zero-risk on UTC-configured servers, fully transparent on non-UTC ones. Happy to drop this once #29481 lands.