Resolving Timezone Mismatch Issues Between Prisma and MySQL

How to resolve timezone (JST/UTC) mismatch issues when using Prisma with MySQL, covering DB auto-generation, app-side generation, and raw SQL patterns.

3 min read

Introduction

This article explains the timezone (particularly JST/UTC) mismatch issues that commonly occur when combining Prisma and MySQL, and provides solutions.

Background and Prerequisites

In many Japanese development environments, MySQL server timezone settings are commonly set to JST (Japan Standard Time). On the other hand, Prisma (Node.js ORM) handles DateTime values as “ISO 8601 strings without timezone information” and does not automatically interpret or convert the DB server’s timezone settings or the timezone of values.

Timezone inconsistencies are particularly likely to occur under the following conditions:

  • When the DB is a cloud managed service where the DB server’s timezone setting cannot be changed to JST, or when UTC-fixed operation is required
  • When Prisma and the application handle times in UTC, but raw SQL operations or DB defaults/triggers generate JST times
  • When the team or project has a culture of executing raw SQL, and operations that bypass the ORM coexist

Under these circumstances, time discrepancies and inconsistencies between the application, ORM, and DB become likely.

In practice, having multiple methods for generating and storing times makes it easy for time discrepancies to occur between the application, ORM, and DB. The three main patterns are:

Main Patterns for DB Time Generation and Storage

1. DB Auto-Generation

  • createdAt and updatedAt are auto-generated in UTC via Prisma schema’s @default(now()).
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @default(now()) @map("updated_at")

2. App-Side Generation (new Date())

  • Cases where the application generates times using new Date() (UTC) and saves them to the DB.
const now = new Date();
await prisma.someModel.create({
	data: {
		// ...other fields
		deletedAt: now,
	}
});

3. Direct DB/SQL

  • Cases where raw SQL is executed and DB-side mechanisms like ON UPDATE or DEFAULT CURRENT_TIMESTAMP auto-generate JST times.

Main Solutions

Design and operate so that regardless of which path (app, ORM, DB) generates and stores times, JST-consistent time management is achieved.

Revising DB Auto-Generation

Adopt dbgenerated("CURRENT_TIMESTAMP(3)") for Prisma schema @default values to generate times in JST.

createdAt DateTime @default(dbgenerated("CURRENT_TIMESTAMP(3)")) @map("created_at")
updatedAt DateTime @default(dbgenerated("CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)")) @map("updated_at")

Notes:

  • dbgenerated("NOW(3)") behaves the same way, but CURRENT_TIMESTAMP is recommended for standardization and portability.
  • Note that there is a known bug in Prisma’s MySQL support where setting @default dbgenerated("CURRENT_TIMESTAMP(3)") on DATETIME(3) columns can cause prisma migrate dev to repeatedly generate migration files with the same content even when there are no actual schema changes. In this case, you need to check the migration file content and manually delete it if there is no substantive difference.

Correcting App-Side Generation

Use Prisma Client extensions ($extends) to automatically convert UTC to JST on writes and JST to UTC on reads.

const setOffsetTime = (object: any, offsetTime: number) => {
	if (object === null || typeof object !== 'object') return;
	for (const key of Object.keys(object)) {
		const value = object[key];
		if (value instanceof Date) {
			object[key] = new Date(value.getTime() + offsetTime);
		} else if (value !== null && typeof value === 'object') {
			setOffsetTime(value, offsetTime);
		}
	}
};

return new PrismaClient().$extends({
	query: {
		$allModels: {
			async $allOperations({args, query}) {
				const offsetTime = 9 * 60 * 60 * 1000; // JST offset
				setOffsetTime(args, offsetTime);
				const result = await query(args);
				setOffsetTime(result, -offsetTime);
				return result;
			},
		},
	},
});

Direct DB/SQL

When generating or updating times directly via DB or SQL, if the MySQL server’s timezone setting is unified to JST, auto-generated times via DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP will also be in JST. Therefore, there is no discrepancy between JST-unified times from the app/ORM and times generated via direct DB/SQL, maintaining consistency.

Results and Summary

With these measures, timezone inconsistencies between DB, application, and Prisma are resolved.

関連記事