Studio + mysql default mode, wrong format related timezone
What version of drizzle-orm are you using?
0.31.2
What version of drizzle-kit are you using?
0.22.8
Describe the Bug
When I
Add a new record in the studio https://local.drizzle.studio/
or insert data in mysql cli by sql raw
createdAt will return the correct time but wrong format
my local time is: 2024-08-04 09:42:20.000
data in the studio: 2024-08-04T02:42:20.000Z
data that I get in backend: 2024-08-04T09:42:20.000Z
data that I get in MySQL: 2024-08-04 09:42:20
my schema
createdAt: timestamp('created_at', { mode: 'date' }).defaultNow().notNull(),
as i understand, mysql is displaying data according to system timezone
studio/backend misunderstands timezone or does not have timezone config which makes it "think" data is utc and display data in utc format
update If I set env is TZ=UTC
studio will show: 2024-08-04T09:42:20.000Z
backend still show: 2024-08-04T09:42:20.000Z
-> still wrong
Expected behavior
orm should show get data with the correct format/time
34 Replies
👋 The team tells me this issue is on the backlog, it’s effectively a TZ issue.
@Raphaël M (@rphlmr) ⚡ thank you for your replay, right now, do we have any solution for it? or I need to handle the format manually?
I am pretty sure that's correct but displayed in a different timezone. Studio should use your timezone.
To be 100% sure: you see this wrong date only in studio but in DB it's good?
@Raphaël M (@rphlmr) ⚡
- mysql-cli correct
- studio will show based on env
TZ, if I don't set env, it will show correct data in UTC, if I set env, it will show data based on env TZ but still include T and Z -> wrong format
- backend will show wrong because it alway convert data to time in system timezone but still include T and Z in format, and the result is 2024-08-04T09:42:20.000Z -> this thing make Date or Moment think that string is UTCthx
So, in date mode, yes, it is utc
11:xx in my local tz:
- studio displays in its TZ
- db saves and returns UTC without TZ
Raw db query
I don't use studio that much but on all my project, I set TZ=UTC on the server (selects and inserts are UTC) and display the date in the front with the user locale
@Raphaël M (@rphlmr) ⚡ in your image i see you have the same problem with me.
created_at and created_at_3 are returning time according to system timezone but still have T and Z inside in backend side.
9:xx on backend
7:xx on studio
yes they are
.toISOString()
dates are always a nightmare 😵💫@Raphaël M (@rphlmr) ⚡ it mean have no config to fix this issue right now? I need to handle it manually and wait for a fix in the future?
If you are only concerned by studio behavior, yes we have to wait.
For the rest it is expected to work like that. In date mode, it’s always serialized in isoString (my db in the screenshot was in utc but not my computer that executed the script)
@Raphaël M (@rphlmr) ⚡
For the rest it is expected to work like that. In date mode, it’s always serialized in isoStringbut as I understand, it's returning wrong data right now, on backend, for me, it returns
2024-08-04T09:42:20.000Z when it should 2024-08-04T02:42:20.000Z if it wants to show in isoStringAre your backend and db in the same timezone?
I do not set the timezone for both, just let it run as default.
if I set env TZ=UTC, data in the studio will change but the backend will still show as before, I do not see any config related to timezone on drizzle-orm
Depending on where is your backend.
1 - assuming the db runs in UTC
2 - backend is on your computer (local dev) it will use your timezone (if it is a nodejs backend, you can set TZ=UTC in some .env. if it uses Vitejs, it has to be added in the vite config because vite erase this specific env)
3 - backend is on a server / docker: env TZ=UTC has to be set in the docker file / server config
Once the backend and the database are in the same time zone, you should get the same result if you compare a query on your backend to one run directly on the database.
Then in the frontend it will be displayed with the user timezone (so, different from the server if your are not in a utc area)