Support for string based timestamp field using ISO formatting

[swelham]

Describe what you want

Right now we are able to insert or update our records using an ISO formatted UTC string (ie '2024-01-04T12:54:11Z'). However, when we select that date back out of the database, it's returning it in the format that our database (PG) is storing it (ie '2024-01-04 12:54:11'). This causes problems when using this return value with new Date() since the time zone will be inferred from the server or client. Additionally, this also means we have to parse the string and reformat it before serializing the value when sending it over HTTP.

Ideally, we would like to be able to work with timestamps as string, but always have these strings ISO formatted.

To workaround this, we are currently using our own custom type that is a combination of both the date and string versions to achieve the result we would like.

The main piece of making this work is.

override mapFromDriverValue = (value: string): string => {
  const date = new Date(this.withTimezone ? value : value + '+0000');

  return date.toISOString();
};

Would it be considerable to support an ISO string timpstamp in drizzle?

[clementohNZ]

This works for me at this stage:

When using Select

import { SQL, sql } from "drizzle-orm"
import { MySqlColumn } from "drizzle-orm/mysql-core"

export function getISOFormatDateQuery(dateTimeColumn: MySqlColumn): SQL<string> {
  return sql<string>`DATE_FORMAT(${dateTimeColumn}, '%Y-%m-%dT%TZ')`
}

Then you can use it in your query like so:

return await db
      .select({
        ...
        createdAt: getISOFormatDateQuery(table.createdAt),
        updatedAt: getISOFormatDateQuery(table.updatedAt),
      })
      .from(table)
      .prepare()
      .execute()

In the frontend, you can simply put the returned value directly into the new Date(<ISO string from backend>) constructor.

---

When using Query

export type GenericObject = { [key: string]: any }

/**
 * MYSQL stores zulu time dates without using the ISO 8601 format. If the MYSQL datetime is used in the
 * new Date() constructor, it will result in the wrong time. So this function takes a single DB record and
 * converts the dates to the ISO 8601 format.
 *
 * @param {GenericObject} record - Record returned from the database.
 * @returns {GenericObject} Record from the DB, but modified so that the dates are in the ISO 8601 format.
 */
export function convertRecordDateValuesToISOFormat<DbRecord extends GenericObject>(record: DbRecord): DbRecord {
  const keys = Object.keys(record)

  keys.forEach((key) => {
    if (key.toLowerCase().endsWith(`at`)) {
      Reflect.set(record, key, moment(`${record[key]}+0000`).toISOString())
    }
  })

  return record
}

/**
 * Similar to {@link convertRecordDateValuesToISOFormat} except it takes in an array of records instead.
 *
 * @param {Array<GenericObject>} records - Array of records returned from the database.
 * @returns {Array<GenericObject>} Record from the DB, but modified so that the dates are in the ISO 8601 format.
 */
export function convertRecordsDateValuesToISOFormat<DbRecords extends Array<GenericObject>>(
  records: DbRecords,
): DbRecords {
  records.forEach((record) => {
    const keys = Object.keys(record)

    keys.forEach((key) => {
      if (key.toLowerCase().endsWith(`at`)) {
        Reflect.set(record, key, moment(`${record[key]}+0000`).toISOString())
      }
    })
  })

  return records
}

Usage:

public async getAll(): Promise<Model[] | null> {
  const queryResult = await db.query.table
    .findMany()
    .prepare()
    .execute()

  return convertRecordsDateValuesToISOFormat(queryResult)
}

[gabrielstellini]

Postgres equivalent of @clementohNZ's solution:

import { SQL, sql } from 'drizzle-orm';
import { PgColumn } from 'drizzle-orm/pg-core';

export function getISOFormatDateQuery(dateTimeColumn: PgColumn): SQL<string> {
  return sql<string>`to_char(${dateTimeColumn}, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')`;
}

(usage stays the same, so):

...
.select({
        created: getISOFormatDateQuery(myTable.created),

I wish there was a way to add this at the schema level/drizzle configuration level, instead of doing it for each field (kind of similar to SET DateStyle)