9 replies

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

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()

.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"

mode: "date"

? Does drizzle pass the date string retrieved from postgres to

new Date()

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()

new Date()

. On the other hand, does Drizzle parse the date string and append the UTC timezone info before passing to

new Date()

new Date()

so that

new Date()

new Date()

treats the date as UTC?

My understanding is this: if the time is

2:00AM GMT + 1

2:00AM GMT + 1

, then

toISOString()

toISOString()

effectively gives

1:00AM GMT

1:00AM GMT

and the timezone is ignored for

timestamp without timezone

timestamp without timezone

. This

1:00AM

1:00AM

is stored and retrieved from Postgres as is. Now when this gets retrived at a

GMT+3

GMT+3

timezone, what happens? Which

Date

Date

is returned by drizzle

-

1:00AM GMT + 3

1:00AM GMT + 3

(if the time is passed to

new Date()

new Date()

without timezone info) or
-

4:00AM GMT + 3

4:00AM GMT + 3

(if Drizzle modifies the date string to include timezone or adds manually adds the current timezone offset of

GMT + 3

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()

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

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

timestamp without timezone

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()

.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"

mode: "date"

? Does drizzle pass the date string retrieved from postgres to

new Date()

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()

new Date()

. On the other hand, does Drizzle parse the date string and append the UTC timezone info before passing to

new Date()

new Date()

so that

new Date()

new Date()

treats the date as UTC?

My understanding is this: if the time is

2:00AM GMT + 1

2:00AM GMT + 1

, then

toISOString()

toISOString()

effectively gives

1:00AM GMT

1:00AM GMT

and the timezone is ignored for

timestamp without timezone

timestamp without timezone

. This

1:00AM

1:00AM

is stored and retrieved from Postgres as is. Now when this gets retrived at a

GMT+3

GMT+3

timezone, what happens? Which

Date

Date

is returned by drizzle

-

1:00AM GMT + 3

1:00AM GMT + 3

(if the time is passed to

new Date()

new Date()

without timezone info) or
-

4:00AM GMT + 3

4:00AM GMT + 3

(if Drizzle modifies the date string to include timezone or adds manually adds the current timezone offset of

GMT + 3

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()

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

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

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

Similar Threads

Similar Threads

Similar Threads