Prisma•14mo ago

Weird bug in dates

Hello guys im facing a weird bug im using MySQL, the date stored in the db is : 2023-11-08 04:37:53.231 -> readable ( 2023-11-08 04:37:53 ) and if i use prisma to retrive it i get: 2023-11-08T04:37:53.231Z -> readable ( 2023-11-08 06:37:53 ) any idea on why this is happening and what might the problem be and how to fix it?

const lesson = await prisma.lesson.findUnique({
    where: { id: +id }
})

{
  id: 3,
  link: 'video.mp4',
  title: 'some title',
  description: 'some desc',
  courseId: 1,
  rating: 4,
  views: 454,
  createdAt: 2023-11-08T04:37:53.231Z,
  updatedAt: 2023-11-08T04:37:53.231Z
}

const lesson = await prisma.lesson.findUnique({
    where: { id: +id }
})

{
  id: 3,
  link: 'video.mp4',
  title: 'some title',
  description: 'some desc',
  courseId: 1,
  rating: 4,
  views: 454,
  createdAt: 2023-11-08T04:37:53.231Z,
  updatedAt: 2023-11-08T04:37:53.231Z
}

24 Replies

Nurul•14mo ago

Hey 👋 If I understand correctly, the value that is stored in db has time component as "04:37:53" but when you retrieve the value it is shown as "06:37:53", so 2 hours are added to the time?

H₂OOP•14mo ago

yes exactly, im not sure what the problem is when i retrive data 2 hours will be added to the date for some weird reason while nodejs, and db have the same date which is both up to date

Olyno•14mo ago

Hi :vmathi: This is probably a timezone issue. You should precise the right timezone in your prisma schema

H₂OOP•14mo ago

Thanks for the answer! any idea how can i do that ? cant really find anything in the docs for timezone

Olyno•14mo ago

Okay so you can't really precise the timezone directly in your schema... but you can precise it for the whole db directly by adding ?serverTimezone=<timezone in your connection string Basically, when you store your date, the database converts it to the database's timezone. That's why you have not the same hour between both dates. When retrieving the date, you need to convert it with the right timezone using the Date class in js/ts

H₂OOP•14mo ago

i added the serverTimezone ill give it a shot, but the DB timezone is correct, the client date is also correct both dates are corret its just prisma is adding 2 hours on top of the db date

Olyno•14mo ago

Prisma should only store what you give it, and convert the necessary. Try to use the native database attribute: https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#mysql-6

Prisma

Prisma schema API (Reference)

API reference documentation for the Prisma Schema Language (PSL).

H₂OOP•14mo ago

let me give native a shot thanks again!

Olyno•14mo ago

Any time!

H₂OOP•14mo ago

but wait im really confused, this is my lessons:

model Course {
  id             Int              @id @default(autoincrement())
  title          String           @db.VarChar(255)
  img            String           @db.VarChar(255)
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @default(now()) @updatedAt
  lessons        Lesson[]
  Lesson_watched LessonsWatched[]
}

model Course {
  id             Int              @id @default(autoincrement())
  title          String           @db.VarChar(255)
  img            String           @db.VarChar(255)
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @default(now()) @updatedAt
  lessons        Lesson[]
  Lesson_watched LessonsWatched[]
}

i mean in the db it stores it correctly, 2023-11-11 05:09:37.478 which was stores 4 minttues ago, but how come the .find() retunrs something else? i dont think its native or something

Olyno•14mo ago

Maybe Prisma is trying to convert it to a timezone you won't Possibily the timezone of your server/api

H₂OOP•14mo ago

it is also correct here let me show you a clear picture

H₂OOP•14mo ago

u see? the date in the db is correct, the new Date() is also correct its my current time but the 7:09am that is what prisma returns also as i saw when i retrive it, prisma is returning: 2023-11-11T05:09:37.478Z which i think its correct? but whats happening here

Olyno•14mo ago

It could also be a bug from Prisma, a weird bug, but a bug

H₂OOP•14mo ago

im really lost here to be honest

H₂OOP•14mo ago

Olyno•14mo ago

Maybe open an issue, this is probably the best to do. What do you think @Nurul (Prisma)?

H₂OOP•14mo ago

yep its a bug i think, since prisma is adding +2 hours on the date i just asked some people and yeah current ISO: 2023-11-11T03:41:41.930Z DB stored ISO: 2023-11-11T03:09:37.478Z PRISMA returns ISO: 2023-11-11T05:09:37.478Z its just timezone different, its UTC instead of UTC+2 so there is no way to change the timezone of prisma?

Olyno•14mo ago

I don't think so, that's why i did a ping for Nurul, so he can check that on his side

H₂OOP•14mo ago

Awesome ill wait for an answer, thanks!

Nurul•14mo ago

Hey 👋 Sorry for the late reply. This conversation slipped through the cracks. I think you might be running into this issue: https://github.com/prisma/prisma/issues/5051 Can you try suggestions provided in the linked GitHub Issue?

GitHub

Improve Timezone Support for Existing MySQL Databases configured wi...

Bug description The OS and MySQL time both are set to KST(Korea Standard Time) which is UTC+9:00. But when create or update records with new Date(), DATETIME string doesn't respect the database...

martin_gluten_free•2mo ago

omart16

Gaming

Programming

Weird bug in dates