MySQL DATETIME time-zone conversion

[FredericLatour]

I had already posted a message on another discussion which is closed. It may therefore not receive the attention I would like :)

I'm curious about the reasonning behind the approach you've taken.
When using MySql2 driver, you can specify a timezone configuration at the connection/pool level.
Connection Options

This option makes it possible to properly handle situations where dates in a mysql database are not stored in utc format.
Basically, all dates going to mysql are converted from UTC (internal javascript format) to the timezone configured. And all dates retrieved are converted back to utc before being converted to javascript date (Javascript date constructor expect an input string as being UTC).

I made some tests trying to change the timezone configuration when creating a MySql2 connection but it didn't change anything. I did not check the source but it's like you were short circuiting this option. What didn't you like with this approach ? that seems rather neat.

The problem with your approach is that it makes it painful to migrate existing apps. Many orm/query builders rely on the driver for retrieving data (at least in the MySql space) and therefore complies with this timezone configuration.
For example, I have a codebase using Typeorm/MySql2. Typeorm is completely transparent and will store/retrieve dates through the driver without any specific correction.

Possible Solution without any breaking change

Wouldn't it be possible to introduce a "transparent" or "driver" mode for date fields?
Using this "transparent" or "driver" mode would mean that basically, the driver does its thing without any correction on your side.

FYI: I don't know for PostgresSql, but the reason why people may store date in their local timezone is that it's way easier to reason about the results of any query using db tools. As far as I know, you don't have any mechanism to display data in a locale timezone with datetime using MySql.

[Angelelz]

I think Drizzle should do a better job in translating data from drivers and make it more transparent to users. A workaround is to override the mapFromDriverValue method and make it do the transformations you'd like. Like this:

const table = mysqlTable("table",  {
  myDatetime: datetime("my_datetime")
})

table.myDatetime.mapFromDriverValue = (val: string) => val;

for example.
You can make it do whatever you want. Maybe new Date(val)

[FredericLatour]

Honestly, I think Drizzle should not have touched the dates or, at least, should have provided a transparent option that would let the driver data untouched. The timezone problem has already been taken care by many Sql drivers.
You must be doing something specific that makes the timezone option not having any influence anymore on the dates being stored and retrieved. Right ?

I don't want anything special. I just want the same date values like if I was querying directly using MySql2 driver or any orm/query builder relying on it.

Now, because you have come up with a very "specific approach" (which I'm not sure was a good idea - but it's done anyway), the idea of a "transparent" mode would solve the problem.

Can you possibly have a discussion internally on this ?

[Angelelz]

Can you open an enhancement issue for this? I honestly don't think it's a bad idea.

[FredericLatour]

Done:

• [FEATURE]: "Transparent" Mode for Datetime Handling compatible with the underlying driver (timezone)

[FredericLatour]

For those facing the exact same problem and looking for a temporary solution, you can map dates using the following approach:

const table = mysqlTable("table",  {
  myDatetime: datetime("my_datetime")
})
table.myDatetime.mapFromDriverValue = (value: any) => new Date(value)
table.myDatetime.mapToDriverValue = (value: any) =>  value

Another approach would be to create a custom type.
Hopefully, the idea of extending existing datetime with an additional mode will be accepted and will solve this problem in a more elegant way.