MySQL DATETIME time-zone conversion?

[hyunbinseo]

Question

Does the Drizzle ORM convert the JavaScript Date object into a MySQL DATETIME value (and vice-versa) using the system time-zone? Not the DB server's global or session time-zone?

Environment

• MySQL +00:00
• macOS +09:00 or KST

SELECT @@global.time_zone, @@session.time_zone;
/*
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00             | +00:00              |
+--------------------+---------------------+
1 row in set (0.02 sec) */

SHOW VARIABLES WHERE Variable_name LIKE '%time_zone%';
/*
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | KST    |
| time_zone        | +00:00 |
+------------------+--------+
2 rows in set (0.01 sec) */

Reproduction

1. db.insert a Date whose value is 2023-06-30T05:45:48.194Z.
2. db.query returns a Date whose value is 2023-06-30T05:45:48.000Z
3. mysql and MySQL Workbench shows a value of 2023-06-30 14:45:48.

import { eq } from 'drizzle-orm';
import { bigint, datetime, mysqlTable } from 'drizzle-orm/mysql-core';
import { createDbClient } from './drizzle.client';

const db = await createDbClient();

const users = mysqlTable('users', {
  id: bigint('id', { mode: 'bigint' }).notNull().autoincrement().primaryKey(),
  joinedAt: datetime('joined_at').notNull()
});

const date = new Date();

console.log(date.toISOString()); // 2023-06-30T05:45:48.194Z

await db.insert(users).values({
  id: 1n,
  joinedAt: date
});

const result = await db.query.users.findFirst({
  where: eq(users.id, 1n)
});

if (result) console.log(result.joinedAt.toISOString());
// 2023-06-30T05:45:48.000Z

Workaround

Had to revert the database server's time-zone to SYSTEM since the following two values were different.

• INSERT a new Date() -- 2023-06-30 17:41:31
• sqlCURRENT_TIMESTAMP -- 2023-06-30 06:41:31

export const users = mysqlTable('users', {
  id: bigint('id', { mode: 'bigint' }).notNull().autoincrement().primaryKey(),
  joinedAt: datetime('joined_at')
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`) // uses the database server's time-zone
});

[205g0]

you shouldn't use TIMESTAMP with mysql, check/watch: https://planetscale.com/courses/mysql-for-developers/schema/dates and all is intended, the video explains very well how to deal with dates

[hyunbinseo]

The question is wholly based on the datetime column type, not timestamp.

Should I not use the CURRENT_TIMESTAMP SQL to fill the datetime column?

[hyunbinseo]

The mismatch is expected. In the reproduction,

• The MySQL server is using a +00:00 timezone
• The MySQL2 client assumed it was +09:00, since the default value is local.

Since the macOS's local time-zone is (KST | GMT+9 | +09:00),

• 2023-06-30T05:45:48.000Z → correctly saved as 2023-06-30 14:45:48 ¹
• 2023-06-30 14:45:48 ¹ → correctly retrieved as 2023-06-30T05:45:48.000Z

---

node-mysql2 uses the local timezone by default.

/**
 * The timezone used to store local dates. (Default: 'local')
 */
timezone?: string | 'local';

— node-mysql2 type definition

---

Node-MySQL2 aims to be a drop in replacement for node-mysql. Please check node-mysql for full documentation.

— node-mysql2 documentation

---

timezone: The timezone configured on the MySQL server. This is used to type cast server date/time values to JavaScript Date object and vice versa. This can be 'local', 'Z', or an offset in the form +HH:MM or -HH:MM. (Default: 'local')

— mysql README

Footnotes

1. String value saved in the database table. It is considered a KST date string by MySQL2 when converted to and from a JavaScript Date object. ↩ ↩²

[miketromba]

Would be great if there was a universal way to explicitly configure this when instantiating the drizzle client

[miketromba]

Wanted to share my solution here in case it's useful to anyone else. I implemented a custom drizzle type called datetimeUtc so that I could have explicit control over how datetimes are handled in my app. Here's the implementation (MySQL):

import { customType } from 'drizzle-orm/mysql-core'

export const datetimeUtc = (name: string, precision: number) =>
	customType<{ data: Date; driverData: string }>({
		dataType() {
			return `datetime(${precision})`
		},
		toDriver(value: Date): string {
			return mysqlDatetimeUtc(value)
		},
		fromDriver(value: string): Date {
			return mysqlDatetimeUtcToDate(value)
		}
	})(name)

function mysqlDatetimeUtc(date: Date = new Date()) {
	return date.toISOString().slice(0, 19).replace('T', ' ')
}
// Use this function instead of new Date() when converting a MySQL datetime to a
// Date object so that the date is interpreted as UTC instead of local time (default behavior)
function mysqlDatetimeUtcToDate(mysqlDatetimeUtc: string) {
	return new Date(mysqlDatetimeUtc.replace(' ', 'T') + 'Z')
}

To use it, simply replace "datetime" columns in your schema to this "datetimeUtc" column type.

import { datetimeUtc } from './datetimeUtc'

export const post = mysqlTable("Post", {
    // ...
    createdAt: datetimeUtc("createdAt", 3).default(sql`(CURRENT_TIMESTAMP(3))`).notNull(),
    updatedAt: datetimeUtc("updatedAt", 3).default(sql`(CURRENT_TIMESTAMP(3))`).notNull(),
    // ...
})

Now, when you store datetimes, you can be certain they will be stored as UTC, and when you retrieve them, they will be the same UTC date that you stored.

This was the most elegant solution I could find for ensuring datetimes work as expected in my codebase. For more info, see drizzle's custom types docs: https://orm.drizzle.team/docs/custom-types

[miketromba]

Opened issue #920 to add { utc?: boolean } config to the datetime column so that users can get this (important) behavior without having to implement it themselves as a custom column type.

[binaryartifex]

just appealing to folks like me that are on less than 8 hours sleep in the last 3 days and would like the mysql 'date' equivalent without thinking too much about it....(although i believe drizzle will be ensuring utc date output soonish).

export const dateUtc = (name: string) =>
  customType<{ data: Date; driverData: string }>({
    dataType() {
      return `date`;
    },
    toDriver(value: Date): string {
      // Split the MySQL date string into its components
      return value.toISOString().split("T")[0];
    },
    fromDriver(value: string): Date {
      // Create a JavaScript Date object in UTC
      const [year, month, day] = value.split("-").map(Number);
      const date = new Date(Date.UTC(year, month - 1, day));
      return date;
    },
  })(name);

be kinda awkward if this solution was riddled with bugs...so tired im seeing sounds atm.... @miketromba hope i didn't butcher :/

[hyunbinseo]

Note: #1082 is merged and is released in 0.28.6

MySQL datetime with mode: 'date' will now store dates in UTC strings and retrieve data in UTC as well to align with MySQL behavior for datetime. If you need a different behavior and want to handle datetime mapping in a different way, please use mode: 'string' or Custom Types implementation

https://github.com/drizzle-team/drizzle-orm/releases/tag/0.28.6

[205g0]

Thanks for sharing. So, now the next riddle is about how we pair your "UTC-time-safety" with created_at and updated_at auto-filled by MySQL? Latter only needs DEFAULT and DEFAULT ON UPDATE to auto-fill them.

But since Drizzle doesn't support ON UPDATE yet, we would need a custom type, right? And latter will use the time-zone neutral server-time anyway (if I hardcode e.g. DEFAULT ON UPDATE CURRENT_TIMESTAMP() into the custom type)

But now I cannot use mode: date anymore (or could I?), so wouldn't retrieval to the client not be messed up?