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 advance5 Replies
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? 🙏
Both Date and Timestamp types have different handling
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#L40GitHub
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
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
Thanks