Prisma date off by one

Question

Using prisma 2.24.1 and MySQL 8.0.25. Whenever I insert a date value, it gets stored in the database with a day off by one, for example:

Insert Tue May 30 2006 00:00:00 GMT+0200 (Central European Summer Time) Database: 2006-05-29

I am using the DATE data type of MySQL. I also tried DATETIME to no avail.

My setup is:

myTable (
  id int primary key autoincrement,
  ...
  birthdate date,
  ...
)

schema.prisma according to prisma db pull

model myTable {
  id         Int       @id @default(autoincrement()) @db.UnsignedInt
  ...
  birthdate  DateTime? @db.Date
  ...
}

Insert

await prisma.$transaction(
    myTable.map((obj) => {
      const dataObject = {
        ...
        birthdate: Date object, containing the date,
        ...
      };
      return prisma.myTable.create({
        data: dataObject,
      });
  })
);

Any hints are welcome

"Prisma automatically converts the Timestamp you sent to UTC" , see: https://stackoverflow.com/questions/64535044/timestamp-with-timezone-column-in-prisma — Luuk, Jun 04 '21 at 15:50
In my view a timezone problem would result in a time one or more hours off. In my case the *date* is one *day* off so unless I am on the transition between two days, the date should be OK. — Robert Hufsky, Jun 05 '21 at 16:34
If you local time is in timezone `-02:00` and what will happen if current time is `2021-06-05 00:10:00`?.... Indeed the server running on UTC will say that the date is still `2021-06-04` — Luuk, Jun 05 '21 at 19:03
Funny thing is: I create `const now = new Date(1960, 8 ,24)` to yield a date 24.9.1960. When saving it in a `date` attribute, it gives me 23.9.1960, when I save it in a `datetime` attribute, it gives 24.9.1960. I would expect a `date` to be timezone agnostic. — Robert Hufsky, Jun 06 '21 at 08:32
Disregarding the 1 month difference in `...new Date(1960, 8 ,24) to yield a date 24.9.1960..`, you seem to be right! — Luuk, Jun 06 '21 at 08:48
The month difference comes from the fact that Javascript follows the tradition of other programming languages to start numbering month IDs by 0. ( see monthindex – https://developer.mozilla.org/de/docs/Web/JavaScript/Reference/Global_Objects/Date) — Robert Hufsky, Jun 06 '21 at 09:49
After some more research, prisma seems to use datetime with timezone even if I only want to store a date such as a birthdate. There seems to be no real DATE support, which is quite a surprise :-). — Robert Hufsky, Jun 06 '21 at 09:53

score 2 · Answer 1 · answered Dec 08 '21 at 14:44

2

I might be late to a party, but I have had similar issue few days ago. Here is how I solved it.

function storeCorrectDate(date) {
    return new Date(
      Date.parse(date.toUTCString()) - date.getTimezoneOffset() * 60000
    );
  }

answered Dec 08 '21 at 14:44

Zenik

89
3

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 08 '21 at 16:21

Prisma date off by one

1 Answers1