[BUG]: Inconsistencies when working with timestamps and corresponding datetime objects in javascript.

[xoldd]

What version of drizzle-orm are you using?

0.28.5

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

Here are my findings:-

drizzle schema:- mode: "date", precision: 6
pgadmin: 2023-09-05 10:25:30.708225
drizzle studio/query:- 2023-09-05T10:25:30.000Z

drizzle schema:- mode: "date", precision: 3
pgadmin: 2023-09-05 10:28:46.346
drizzle studio/query:- 2023-09-05T10:28:46.000Z

drizzle schema:- mode: "date", precision: 0
pgadmin: 2023-09-05 10:30:05
drizzle studio/query:- 2023-09-05T10:30:05.000Z

drizzle schema:- mode: "string", precision: 6
pgadmin: 2023-09-05 10:31:55.286583
drizzle studio/query:- 2023-09-05T05:01:55.286Z

drizzle schema:- mode: "string", precision: 3
pgadmin: 2023-09-05 10:34:13.681
drizzle studio/query:- 2023-09-05T05:04:13.681Z

drizzle schema:- mode: "string", precision: 0
pgadmin: 2023-09-05 10:35:48
drizzle studio/query:- 2023-09-05T05:05:48.000Z

When mode: "date" is used the last 6 digit microseconds information is lost from the datetime object received in javascript after querying the database. The datetime information up till the seconds is retained in the corresponding javascript date object.

When mode: "string" is used microseconds information upto starting 3 digits are retained in the datetime object received after querying the database. The datetime information in the corresponding javascript object is mutated to be 5 hours 30 mintues behind of what's stored in the database. I'm not passing any custom configuration for this mutation to take place and also my timezone is ahead of UTC not behind so this doesn't make sense.

Expected behavior

The microseconds information should be retained when using mode: "date" with timestamps and accurate datetime information should be retained in the corresponding javascript date object when using mode: "string" with timestamps.

Environment & setup

OS: Fedora Linux 38 (Workstation Edition) x86_64
Kernel: 6.4.13-200.fc38.x86_64
Node version: 18.17.1

[xoldd]

Enabling withTimezone: true in timestamp schema definition solved the problem. Also, this problem is specific to postgres.js library, it doesn't happen with node-postgres(pg) library.

After enabling that option both mode: "date" and mode: "string" return the correct results.

Also one thing to mind is that javascript date cannot represent the datetime information upto the last 3 microseconds like most databases do. When datetime information is retrieved from the database information pertaining to last 3 microseconds of the timestamp is lost. So, it's probably better to use precision: 3 when declaring timestamps in drizzle schema.

[wSedlacek]

The line of code that seems to cause the problem is here.

drizzle-orm/drizzle-orm/src/pg-core/columns/timestamp.ts

Line 66 in 0d833d1

return this.withTimezone ? value.toUTCString() : value.toISOString();

The issue is that toUTCString() does not have precision included but toISOString() does.

[tammo]

For MySQL this line in the mysql-core datetime column may be problematic?

https://github.com/drizzle-team/drizzle-orm/blob/0a4e3b265ce121675e7baa14f3a39669ea387e6d/drizzle-orm/src/mysql-core/columns/datetime.ts#L61C3-L61C3

It just adds 'Z' and assumes the returned date is UTC, but MySQL does not change the timezone by itself to UTC when just selecting a datetime field.

This maybe problematic if these dates are parsed to ISO and returned to a client, which will interpret the datetime ias UTC. This results in logic date erros for example when someone compares the date to local "now".

[AndriiSherman]

Should be fixed in drizzle-orm@beta. I would appreciate some feedback to confirm whether this issue has been resolved in this tag.

I plan to release it in version 0.30.0 tomorrow or within the next few days; I simply aim to address this substantial set of issues we're encountering. I'll be duplicating this message across all similar issues we're facing.

[AndriiSherman]

Fixed in drizzle-orm@0.30.0

Please check release notes for more info