Drizzle Team•4mo ago

How does Drizzle ORM map PostgreSQL dates with regard to timezones?

Sorry for reposting this as I initially posted it in discussions. So how does Drizzle actually handle dates and timestamps for Postgres. It is mentioned here that drizzle provides string or "mapped dates": https://orm.drizzle.team/learn/latest-releases/drizzle-orm-v0300 What exactly does "mapped dates" mean? When using a timestamp without timezone in postres with drizzle, based on the statements on that page, I assume it converts the passed date object to utc format using .toISOString() and then passes it to postgres. Since it is without timezone, Postgres does no offset adjustment. So what happens when retrieving such fields inside of drizzle when using mode: "date" ? Does drizzle pass the date string retrieved from postgres to new Date() directly? In this case, the date (since it has no timezone information) will be assumed to be in the timezone of the call to new Date(). On the other hand, does Drizzle parse the date string and append the UTC timezone info before passing to new Date() so that new Date() treats the date as UTC? My understanding is this: if the time is 2:00AM GMT + 1, then toISOString() effectively gives 1:00AM GMT and the timezone is ignored for timestamp without timezone. This 1:00AM is stored and retrieved from Postgres as is. Now when this gets retrived at a GMT+3 timezone, what happens? Which Date is returned by drizzle - 1:00AM GMT + 3 (if the time is passed to new Date() without timezone info) or - 4:00AM GMT + 3 (if Drizzle modifies the date string to include timezone or adds manually adds the current timezone offset of GMT + 3). I believe it has to be one of these two since JavaScript dates only have one data field which is the timestamp and contain no timezone information in them. new Date() will take the passed time as though it is for the local timezone (rather than UTC) if the passed time string itself doesn't include a timezone info. Thanks in advance

6 Replies

Abdulramon JemilOP•4mo ago

I'm really sorry to tag you here @Andrii Sherman. I've been hoping someone would check this out since yesterday but no luck so far. Can you please check it out? 🙏

Angelelz•4mo ago

Both Date and Timestamp types have different handling

Angelelz•4mo ago

For Date is as simple as new Date(value) when coming from the driver and value.toISOString() when sending to the driver. See here: https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/date.ts#L40

GitHub

drizzle-orm/drizzle-orm/src/pg-core/columns/date.ts at main · drizz...

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅 - drizzle-team/drizzle-orm

Angelelz•4mo ago

For timestamp is similar but it will add the gmt timezone depending on the configuration. See here https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/columns/timestamp.ts#L62

GitHub

drizzle-orm/drizzle-orm/src/pg-core/columns/timestamp.ts at main · ...

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅 - drizzle-team/drizzle-orm

Abdulramon JemilOP•4mo ago

Thanks

Chris•4w ago

I need advice on how to work around a problem with this mapping. When mapping date from postgres back to JS Date, new Date(aPostgresDateString) will produces a Date object with timezone. new Date assumes the date string to be UTC. i.e., 2024-11-24 is equivalent to "2024-11-24 00:00:000Z" So new Date produces time object really. 2024-11-24 becomes Sun Nov 23 2024 19:00:00 GMT-0500. The problem arises when date manipulation library and UI components truncate the time part of the Date object and only works with the date part. When I use date-fns's format function to format a date return from drizzle, it's always one day less than the stored date in the database. How do I override the mapping of date in the Pg driver? I use a custom type localDate to work around that.

import { formatISO, parse } from 'date-fns';
import { customType } from 'drizzle-orm/pg-core';

export const localDate = customType<{
  data: Date;
  driverData: string;
  config: {};
}>({
  dataType() {
    return 'date';
  },
  fromDriver(value: string): Date {
    return parse(value, 'yyyy-MM-dd', new Date());
  },
  toDriver(value: Date): string {
    return formatISO(value, { representation: 'date' });
  },
});

import { formatISO, parse } from 'date-fns';
import { customType } from 'drizzle-orm/pg-core';

export const localDate = customType<{
  data: Date;
  driverData: string;
  config: {};
}>({
  dataType() {
    return 'date';
  },
  fromDriver(value: string): Date {
    return parse(value, 'yyyy-MM-dd', new Date());
  },
  toDriver(value: Date): string {
    return formatISO(value, { representation: 'date' });
  },
});

Gaming

Programming

How does Drizzle ORM map PostgreSQL dates with regard to timezones?