Prisma DateTime handling and PostgreSQL TIMESTAMPTZ synchronization

Handling Timezone Synchronization

No, the Prisma Client does not provide a mechanism to respect database-level session timezone offsets during serialization. Prisma is designed to enforce UTC normalization across all supported providers to ensure consistency. When using PostgreSQL with TIMESTAMPTZ, Prisma always treats the data as UTC, regardless of the timezone setting configured in your PostgreSQL session or server.

Consistency Between Native SQL and Prisma

To guarantee consistency when mixing native SQL date functions with Prisma's UTC-enforced DateTime objects, you must normalize the execution context. Since PostgreSQL stores TIMESTAMPTZ internally as UTC, the discrepancy usually arises from how the database session formats the output or how native functions (like CURRENT_DATE or now()) interact with the session's local timezone.

Follow these steps to ensure alignment:

• Use UTC for Native Functions: When writing raw SQL via prisma.$queryRaw, explicitly cast or set the timezone to UTC to match Prisma's application-layer behavior.
• Avoid TIMESTAMP (Without Time Zone): Ensure your schema uses DateTime (which maps to TIMESTAMPTZ in Postgres). Using TIMESTAMP without a timezone can lead to ambiguous data that ignores UTC normalization.
• Handle Presentation at the Edge: Treat the Prisma Client as a UTC-only transport layer. Perform all timezone conversions to local time only at the final presentation layer using libraries like date-fns or luxon.

Verification and Implementation

To verify that your data is stored and retrieved consistently, you can compare a raw database query with a Prisma query:

// 1. Check DB session timezone
// SQL: SHOW timezone;

// 2. Compare raw vs Prisma
const raw = await prisma.$queryRaw`SELECT "createdAt" FROM "User" LIMIT 1`;
const prisma = await prisma.user.findFirst();

console.log(raw[0].createdAt); // Formatted by DB session timezone
console.log(prisma.createdAt); // JavaScript Date object (UTC)

Note on Assumptions: This guidance assumes you are using a recent version of Prisma (v4+) and PostgreSQL. If you are using a custom database middleware that alters session settings per request, the behavior of $queryRaw may diverge from the standard Prisma Client methods.

Diagnostic Detail Needed: Are you utilizing $queryRaw for date arithmetic, or are you relying on database-level triggers/defaults (e.g., DEFAULT now())? This determines if the fix should be applied in the Prisma schema or via SQL casting.