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)
GitHub
drizzle-orm/drizzle-orm/src/mysql-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
@Raphaël M (@rphlmr) ⚡
I dont set timezone for db/backend/studio
-> all will run by default
db will auto parse timestamp when we query and yeah, it show correct time based on my system timezone
studio show timestamp as iso string in utc
backend show timestamp as
iso string in my system timezone -> this is the problem.
when I set TZ=UTC
studio show timestamp as ios string in my system timezone -> wrong
backend still show timestamp as ios string in my system timezone -> wrong
I can make sure my env was effect because I tryed logger.info(new Date()) on backend and it return correct time by TZ that was settedYes this is what I shared, the value that Drizzle receives is instantiated as a new Date so it applies the timezone it has in env
Ah let me re reread with your edits
@Raphaël M (@rphlmr) ⚡ but my problem is the data that get from database was parse to my system timezone, then return as iso string format
it was return 2024-08-04T09:42:20.000Z
while correct is 2024-08-04T02:42:20.000Z if it want to show it in iso string
even I set TZ=UTC, it still return 2024-08-04T09:42:20.000Z
loook like timezone do not effect to drizzle-orm timestamp?
The previous dates you see wrong was defaultNow or set by a query?
createdAt alway create by the system, I don't set it
I don't think data in mysql is wrong, because I tried with insert data by mysq-cli, it still return the same result
Can we check some things?
What is the tz of the db? (Local db or remote?)
SELECT @@global.time_zone;
What is the tz of the backend?
console.log(process.env.TZ)
You can test with and without TZ=UTC to check
db: SYSTEM
data: 2024-08-04 09:42:20
without TZ=UTC
backend: null
Data: 2024-08-04T09:42:20.000Z
with TZ=UTC
backend: UTC
data: 2024-08-04T09:42:20.000Z
hum System for db but where is it?
my system in +7 GMT
I edited data return for more clear
oh ok so in DB it is not in UTC but +7 and the date is pushed assuming it is UTC
as I understand, in db, timezone only meaning it will auto parse when select query.
because when I do not set TZ
db return 2024-08-04 09:42:20
studio return 2024-08-04T02:42:20.000Z
this is correct -> I think data in db is correct and it saved as UTC, only when we select data, it will parse by "tool" timezone
My guess is that it is admitted that a DB is in UTC, Drizzle (
new Date(value + '+0000'))
this is what make the assumptionthis part
not matter what you set as TZ in the backend, it is converted to an UTC
this explains why both server and DB have to be in UTC. I will ask if it is expected and what are the recommandations
oh, I seeee. that mean this is a issue come from drizzle-orm, right?if I want to handle this, I need to change mysql to utc, right?
yes, if DB is in UTC it should be definitely fixed (except studio without tz of course)
in production, I guess that 100% of cloud providers set TZ to UTC for you
I often have issues on local (with db set to UTC) when I use Date() in my server code and I forget to set its TZ to UTC
so now, I set that everywhere (I broke my prod, running my server on local but with prod db ...)
I have asked the team about what is the expected conf. Then I think it should be on the documentation
Thank you so much for your efforts to help me, you are so kind 🙏
No problem, and sorry it took some time.