text:
Improve Timezone Support for Existing MySQL Databases configured with a Non-UTC Timezone

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's timezone and always filled with UTC time.
For example, when the current time is 2020-06-20 13:00:00 in Korea, I expect the data should be stored like below

id
created_at

1
2020-06-20 13:00:00

However, what I get is UTC time which differs -9 hours.

id
created_at

1
2020-06-20 04:00:00

How to reproduce

Set the system and MySQL timezone to Asia/Seoul or UTC+9:00

Create with new Date()

await prisma.myTable.create({
  data: {
    createdAt: new Date()
  }
})

Expected behavior

I expect the DATETIME string to be synced with MySQL's timezone.
Prisma information

model MyTable {
  createdAt DateTime @map(name: "created_at")

  @@map(name: "my_table")
}

Environment & setup

OS:  Ubuntu
Database:  MySQL 8.0.20
Prisma version:  2.0.1
Node.js version:  13.14.0

Sorry for cutting in,
I'm also having trouble with this behavior. (JST)
If we could use timezone option of mariadb, would it be a solution?
https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/41d3926b1deed2fe10e601d13cd954a7f1654a66/lib/config/connection-options.js#L101
setupMysql method doesn't seem to support timezone option.
https://github.com/prisma/prisma/blob/aa17fbc65c403ecc7fb1bf887bc04a4917d53c86/src/packages/client/src/utils/setupMysql.ts
(I'm sorry if it's misplaced...)

Sorry for cutting in,
I'm also having trouble with this behavior. (JST)
If we could use timezone option of mariadb, would it be a solution?
https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/41d3926b1deed2fe10e601d13cd954a7f1654a66/lib/config/connection-options.js#L101
setupMysql method doesn't seem to support timezone option.
https://github.com/prisma/prisma/blob/aa17fbc65c403ecc7fb1bf887bc04a4917d53c86/src/packages/client/src/utils/setupMysql.ts
(I'm sorry if it's misplaced...)

setupMysql method doesn't seem to support timezone option.
https://github.com/prisma/prisma/blob/aa17fbc65c403ecc7fb1bf887bc04a4917d53c86/src/packages/client/src/utils/setupMysql.ts

This is only used for testing actually

setupMysql method doesn't seem to support timezone option.
https://github.com/prisma/prisma/blob/aa17fbc65c403ecc7fb1bf887bc04a4917d53c86/src/packages/client/src/utils/setupMysql.ts

This is only used for testing actually

setupMysql method doesn't seem to support timezone option.
https://github.com/prisma/prisma/blob/aa17fbc65c403ecc7fb1bf887bc04a4917d53c86/src/packages/client/src/utils/setupMysql.ts

This is only used for testing actually

Oh, was it wrong... sorry ><
(so, mariadb is dev dependency 💡 )
I wish I could set the connection timezone somewhere.
Is there any place where I could set it?

setupMysql method doesn't seem to support timezone option.
https://github.com/prisma/prisma/blob/aa17fbc65c403ecc7fb1bf887bc04a4917d53c86/src/packages/client/src/utils/setupMysql.ts

This is only used for testing actually

Oh, was it wrong... sorry ><
(so, mariadb is dev dependency 💡 )
I wish I could set the connection timezone somewhere.
Is there any place where I could set it?

@munepom that's probably in https://github.com/prisma/quaint

@munepom that's probably in https://github.com/prisma/quaint

The problem is how JavaScript's Date is by default represented as UTC. When Prisma only uses DateTime<Tz> underneath, what happens here is the stringified date we get from the client is in UTC, if not explicitly specifying a timezone.
What we could do here is a new parameter for the client for the timezone. If set, the client stringifies the date with this timezone, and the database would then be correct.

Currently the Prisma Client will always convert time into UTC, regardless of where the server or database is located.
We think this actually a feature because then your database doesn't have timestamps that mix in timezones. May I ask why you'd like to see the time in Korean time instead?
Two guesses, but please let me know if either of these are the reason:

It's easier to debug if the database time is in your local time
You'd like to support multiple timezones in your database

For supporting multiple timezones, we recommend storing the UTC time in one column, and the "Olson timezone" in another column. In practice, this looks like this:

ID
Time
Timezone

1
2020-06-20 13:00:00
Asia/Seoul

2
2020-06-20 08:00:00
America/Los_Angeles

Would that work for you?

@matthewmueller
First of all, I have lots of previous data that were stored in Korean time before I started using Prisma. And yes, time should be stored in Korean time to read and debug much easily. Currently, I'm not considering multiple timezones in this issue.
What I want to say is that Prisma's default behaviour is different from what MySQL's CURRENT_TIMESTAMP does.
p.s.
I resolve this issue by adding 9 hours when storing, then use Prisma middleware that subtracts 9 hours from all the Date fields. It's not ideal but working.

@matthewmueller
I am in China (CST/GMT+8) and running into the same issue.
To be clear, MySQL store a timestamp, which also can be read as a [time string] + [time zone], take the example in your table, the time 2020-06-20 13:00:00 is not a string (if filed type is datetime), but a time with timezone.
You can read the value in different timezone. For example, set time_zone = '+09:00'; , then select the value, it may be 2020-06-20 13:00:00, then set time_zone = '+00:00'; and select the value, it should be 2020-06-20 04:00:00.
MySQL provide this feature, If you use mysql cli or Sequelize (with timezone option), or some GUI clients, they always handle this kind of value properly, but prisma does not,  so it's really a bug, not a feature.

Since prisma always treat date value as UTC time, so set time_zone to +00:00 should match the actual datetime value.
This piece of code works:
prisma.$use(async (params, next) => {
    const timezoneSql = 'set time_zone = \'+00:00\'';
    if(params.args.query !== timezoneSql) {
        await prisma.$queryRaw(timezoneSql);
    }
    // Manipulate params here
    const result = next(params);
    // See results here
    return result;
});

@TooBug, thanks for your thoughtful reply!

But, if you save this value to database with prisma, when database's default timezone is +08, the time value becomes 2018-11-03 03:55:05+8, which is not a correct value. The reason is prisma treat the value as 0 zone value(2018-11-03 03:55:05+0), but mysql treat it as +8 zone value(2018-11-03 03:55:05+8).

Woah, this is definitely something worth investigating. I was under the impression that if you pass 2018-11-03 03:55:05+0 to +8 zone value, it would store the correct time, but display it as +8.

Next Steps:

Confirm this issue with MySQL
Check if the connection string can explicitly connect the the timezone

I confirmed this issue with MySQL.
If you add a default-time-zone to your MySQL config (/usr/local/etc/my.cnf)
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
default-time-zone = "+08:00"

Then you run the Prisma Client:
import { PrismaClient } from "@prisma/client"
const prisma = new PrismaClient()

async function main() {
  await prisma.$connect()
  await prisma.user.create({
    data: {
      created_at: new Date(),
    },
  })
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect())

MySQL does not convert that UTC time +08:00, so the times in your database are 8 hours off. This explains why the middleware @TooBug mentioned works.
For reference, I created these two rows at the same time:

id
created_at

8
2021-03-30 19:25:26

9
2021-03-30 11:25:31

The first one was created with a manual INSERT with a default created_at of NOW() and the second one was created by Prisma.
I tried a few suggestions from StackOverflow on setting the timezone in the connection string:
DATABASE_URL="mysql://root@127.0.0.1:3306/prisma?sslmode=disable&useTimezone=true&serverTimezone=Asia/Bangkok"

Unfortunately, none of those seem to work.
Next steps:

Set your database's timezone to UTC. We should document this now. Document the current workaround with middleware.
Think about ways to solve this problem.

@matthewmueller Do you agree that my suggestion is the way to solve this problem?

@FredericLatour yep, https://github.com/prisma/prisma/issues/6341 seems like a possible solution to this problem.

What other solution are you considering?
This solution is by the way completely trivial to implement.

I can confirm the same with postgres.
These three values in the database are inserted almost at same time. The last values is inserted via SQL while first two are inserted via prisma:

I have the same problem

OS: Windows 10
Database: MySQL 8.0.24
Prisma version: 2.21.2
Node.js version: 14.16.1

I have a use case I have not seen mentioned here yet, which I think could be called: Support for arbitrary datetime/date/time values independent of a globally set timezone
I am working with a pre-existing database model, which I am unable to change, where times and dates are not being stored in UTC in the database itself, and the table contains TIME and DATE (mysql) entries that correspond to the user's local timezone. There is no corresponding time zone column on this table. From everything I have read, MySQL operates with the expectation of UTC times and dates (according to the docs) however there is nothing directly enforcing this and so my current use case does exist.
So, in addition to the use cases mentioned above in this issue, it would also be great to be able to insert from a string into datetime/date/time fields for mysql, or perhaps have prisma respect a timezone offset in conjunction with a date object on a per insert basis. I am aware of node's ability to present the date object against a local timezone set at the env level, however my use case requires a per insert/update basis and therefore a global setting is not helpful.
I have come up with a workaround for my situation that involves creating a date using a timestamp that has been altered to reflect utc - timezone offset in seconds and this ends up corresponding to "local time" for the user at the time of insertion. Kind of hacky, seems like another option in the prisma client would be helpful. I imagine I am not the only person with a similar use case, using prisma on top of an existing db model.

I am working with a pre-existing database model, which I am unable to change, where times and dates are not being stored in UTC in the database itself, and the table contains TIME and DATE (mysql) entries that correspond to the user's local timezone.

What did you do before Prisma? I didn't see a way for Sequelize to do this for example. Maybe you ran SET timezone=... before each query?

Unfortunately we did not use an ORM before prisma for these tables in the model. These tables were built by hand and accessed with raw sql until recently.
Thanks,
Alex Hathaway
206.419.3772

From: Matthew Mueller @.>
Sent: Wednesday, June 2, 2021 5:33:06 AM
To: prisma/prisma @.>
Cc: Alex Hathaway @.>; Manual @.>
Subject: Re: [prisma/prisma] DATETIME field in MySQL is always filled with UTC time when create/update with new Date() (#5051)
I am working with a pre-existing database model, which I am unable to change, where times and dates are not being stored in UTC in the database itself, and the table contains TIME and DATE (mysql) entries that correspond to the user's local timezone.
What did you do before Prisma? I didn't see a way for Sequelize to do this for example. Maybe you ran SET timezone=... before each query?
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHubhttps://github.com/prisma/prisma/issues/5051#issuecomment-852951117, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ABOLAA7LISIMCZUMLVGT373TQYJHFANCNFSM4WBJ3QCA.

I also have a timezone issue with prisma even while I'm using it for a new project without inherited DB. The problem is that my database should be consumed by external BI tool that expects tha dates to be stored in the local timezone, so storing all dates in the UTC is not an option for me.
I understand that changing default behavior to storing data in the local timezone can be a major breaking change and that the native JS Date isn't so great for different timezone handling. But what if we can use use some alternative date/time library that is can handle timezones much better than native js Date, like luxon or momentjs.
I think that it would be great if in my prisma model I would just change DateTime to, say, Luxon and I can pass luxon object to the prisma and retrieve the same. In that case prisma could look at the timezone settings of the database and the node server, and perform all nessery convertations back and forth.
It could be even done via external extension if prisma would allow creating custom data types (what would be great feature anyway).

Many overseas users seem to be suffering from this problem. What about the specify in the tutorial guide that the time zone of db should be fixed by utc until the problem is solved? 🤔

Well, I know you won't appreciate this message but honnestly, I really can't understand "Prisma" on that particular subject.
As a matter of fact, that's not really the first time you are having this approach where you seem not to like being explained what the solution is.
You can make circles as long as you want, the solution is to make it possible to pass "timezone" configuration information to the underlying database driver.
How long should it take to make it a reality? A couple hours?
Why would you think months and months about this matter? This is how it is being handled by existing database drivers and Orm as well. By keeping the same approach, not only the problem is solved in no times, but this makes it trivial for people moving to your product.
If at least you were providing some rationale behind the fact that you don't seem to be interested to implement the easy solution used by everybody, that could make sens!!! But there is just nothing.
As interesting as your product may be, I have lost any confidence in using it. Your approach seem weird and hazardous.

Prisma really not have any plans for this issue...?
It should be remembered that not all projects are just for Prisma. If I want to apply Prisma in a project that has already been written, it is very inconvenient for me to edit the existing and current data by fix the UTC settings.
I don't think it's a difficult task, can't we just do it as the existing ORMs do?
I always have to think about whether to migrate the entire DB time zone settings for Prisma. And this isn't very good for Prisma's Developer Experience.
I recommend Prisma to other developers, but I belatedly realized that there was a time zone issue, and I am somewhat disappointed that this part has been left unattended for more than a year.
I tried to recommend Prisma to many companies, but this timezone issue has turned many CTOs' minds.

For anyone suffering from this issue (like me), here is my simple solution I've been using over a year and works perfectly fine.
Create or update with custom now() method
// UTC-9 using dayjs
export const dbNow = (): Date => dayjs().add(9, 'hour').toDate()

const now = dbNow()

await prisma.user.create({
  data: {
    createdAt: now,
    updatedAt: now,
  },
})

Use a middleware that modifies all the times from Prisma's result object
// Subtract 9 hours from all the Date objects recursively
function subtract9Hours(obj: Record<string, unknown>) {
  if (!obj) return

  for (const key of Object.keys(obj)) {
    const val = obj[key]

    if (val instanceof Date) {
      obj[key] = dayjs(val).subtract(9, 'hour').toDate()
    } else if (!isPrimitive(val)) {
      subtract9Hours(val as any)
    }
  }
}

function prismaTimeMod<T>(value: T): T {
  if (value instanceof Date) {
    return dayjs(value).subtract(9, 'hour').toDate() as any
  }

  if (isPrimitive(value)) {
    return value
  }

  subtract9Hours(value as any)

  return value
}

// Create a prisma client instance with timemod
const prisma = new PrismaClient()

prisma.$use(async (params, next) => {
    const result = await next(params)

    return prismaTimeMod(result)
  })
}

UTC-09:00 is KST. Change it to your timezone.
Unfortunately this method adds an unnecessary overhead (not much) where it iterates all the arrays and objects, especially when the result is very large.

For anyone suffering from this issue (like me), here is my simple solution I've been using over a year and works perfectly fine.
Create or update with custom now() method
// UTC-9 using dayjs
export const dbNow = (): Date => dayjs().add(9, 'hour').toDate()

const now = dbNow()

await prisma.user.create({
  data: {
    createdAt: now,
    updatedAt: now,
  },
})

Use a middleware that modifies all the times from Prisma's result object
// Subtract 9 hours from all the Date objects recursively
function subtract9Hours(obj: Record<string, unknown>) {
  if (!obj) return

  for (const key of Object.keys(obj)) {
    const val = obj[key]

    if (val instanceof Date) {
      obj[key] = dayjs(val).subtract(9, 'hour').toDate()
    } else if (!isPrimitive(val)) {
      subtract9Hours(val as any)
    }
  }
}

function prismaTimeMod<T>(value: T): T {
  if (value instanceof Date) {
    return dayjs(value).subtract(9, 'hour').toDate() as any
  }

  if (isPrimitive(value)) {
    return value
  }

  subtract9Hours(value as any)

  return value
}

// Create a prisma client instance with timemod
const prisma = new PrismaClient()

prisma.$use(async (params, next) => {
    const result = await next(params)

    return prismaTimeMod(result)
  })
}

UTC-09:00 is KST. Change it to your timezone.
Unfortunately this method adds an unnecessary overhead (not much) where it iterates all the arrays and objects, especially when the result is very large.

It seems like a neat solution. thx @jhaemin
As you have mentioned, this approach might cause performance issue.
However, I'll take it 👍🏼
Hope that fundamental issue to be fixed by Prisma team in the near future!

Just in case if someone will end here seeking for solution like me.
Alternative variant is to patch Date object on prisma.unpack
No iteration or other drawbacks, but may get broken on prisma version update, since it rely on inner stuff.
As well as @jhaemin solution, this one assumes you treat UTC date as local
const originDate = Date;
const HOURS_OFFSET = -9;

class DateWithOffset extends Date{
  constructor(options) {
    if (options) {
      if (typeof options === 'string'){
        console.log('date called', options);
        const t = new originDate(options);
        const d = new originDate(t.setHours(t.getHours()+HOURS_OFFSET));

        super(d);
      }else{
        super(options);
      }
    } else {
      super(originDate.now());
    }
  }
}

// NOTE: hook on unpack cos it after `await`, to avoid RC
const originUnpack = prisma._fetcher.unpack;
prisma._fetcher.unpack = (...args) => {
  Date = DateWithOffset as DateConstructor;
  const res = originUnpack.apply(prisma._fetcher, args);

  Date = originDate;

  return res;
}

For any application needing to interface with other existing databases / API's being able to set a constant timezone to work in is pretty important and basic. I find fact this issue has been left hanging for so long alarming. Luckily I am not long into my prisma journey, it's not a big issue to swap to another ORM that inspires more confidence.

@pantharshit00 Why not deal with this time zone issue, which is really important to developers in a certain country, can you give an official statement?

I am working with a pre-existing database model, which I am unable to change, where times and dates are not being stored in UTC in the database itself, and the table contains TIME and DATE (mysql) entries that correspond to the user's local timezone.

What did you do before Prisma? For example, I didn't see a way for Sequelize to set timezones on a per-query basis. Did you run SET timezone=... before each query manually?

Sequelize's Solution

https://sequelize.org/master/class/lib/sequelize.js~Sequelize.html#instance-constructor-constructor

@winner106 The previous solution doesn't utilize an ORM and isn't written in Javascript.

Is there a situation that has progressed more?

I think there may be two solutions on prisma/quaint.  One is to find a way to add timezone explicitly to the DB schema, and allow Prisma and quaint pass timezone between them.  The other is to add following enums in quaint::ast::Value
NaiveDateTime
NaiveDate
NaiveTime

which shall be serialized and deserialized as https://docs.rs/chrono/latest/chrono/naive/struct.NaiveDateTime.html, which is ISO 8601 without a timezone, with the assumption that the server node program has the same timezone of the MySQL db.
I think the second method shall be easier to implement and sufficient enough for many cases.  The first one shall solve the problem more completely, but there is two disadvantages for the first solution:

It will add chrono-tz to the quaint dependency.
Perhaps it may involves unnecessary timezone conversion, which can be computational due to all the craziness of timezones like summer/winter division.

I did research a while ago and wrote an internal article how to fix this. It's a bit more of work, and not yet prioritized for development. I remind of this issue every now and then, and of course it'll be more visible if the issue here stays active.
I hope we can fix it for good soon...

That's so easy to fix that I am amazed to see that this is not yet done.
I don't really mind, as I decided not to use Prisma because of this very matter (one year later, it sounds like I took the right decision).
However, I feel for those posting here on a regular basis!!
Anyway...

That's so easy to fix that I am amazed to see that this is not yet done. I don't really mind, as I decided not to use Prisma because of this very matter (one year later, it sounds like I took the right decision). However, I feel for those posting here on a regular basis!! Anyway...

Where is your pull request then?

I have lost any interest in prisma after the following discussion:

https://github.com/prisma/prisma/issues/6341

Hey all. I've been doing some research on this a while ago, and wrote my findings to here: https://github.com/pimeys/writing/blob/main/prisma/Changes to Prisma Default Value Handling.md
It's mostly about default value handling because I'm more on the schema side of Prisma, but also describes a few solutions for the client. Keep commenting and liking this thread, I hope we'll get time to fix the issues soon...

Is Prisma the first ORM out there?
Do the other ORM suffer the exact same problem?
When you answer those questions, you have a solution.

Just in case if someone will end here seeking for solution like me. Alternative variant is to patch Date object on prisma.unpack No iteration or other drawbacks, but may get broken on prisma version update, since it rely on inner stuff.
As well as @jhaemin solution, this one assumes you treat UTC date as local
const originDate = Date;
const HOURS_OFFSET = -9;

class DateWithOffset extends Date{
  constructor(options) {
    if (options) {
      if (typeof options === 'string'){
        const t = new originDate(options);
        const d = new originDate(t.setHours(t.getHours()+HOURS_OFFSET));

        super(d);
      }else{
        super(options);
      }
    } else {
      super(originDate.now());
    }
  }
}

// NOTE: hook on unpack cos it after `await`, to avoid RC
const originUnpack = prisma._fetcher.unpack;
prisma._fetcher.unpack = (...args) => {
  Date = DateWithOffset as DateConstructor;
  const res = originUnpack.apply(prisma._fetcher, args);

  Date = originDate;

  return res;
}

This way can handle the problem of timezone in query result. However, it doesn't work for mutation operation like update or insert.

i have a solution. first you need to edit the schema.prisma file
  change_time  DateTime? @db.DateTime(0)

to
  change_time  String? @db.VarChar(0)

and run prisma generate. then you can pass a string to the time field

You could also write this way, passing dbgenerated with the sql function NOW
createdAt            DateTime  @default(dbgenerated("NOW()"))

You could also write this way, passing dbgenerated with the sql function NOW
createdAt            DateTime  @default(dbgenerated("NOW()"))

Work for createdAt thing but how about
updatedAt  DateTime @updatedAt

It is inconceivable that such a fundamental problem has not been solved in two years.
Is it very weird that all the core R&D team members live in Greenwich(UTC+0)?

It is inconceivable that such a fundamental problem has not been solved in two years.
Is it very weird that all the core R&D team members live in Greenwich(UTC+0)?
Me to
I'm waiting for the official to solve this problem

Is it okay if I make a middleware plug-in that looks like an official plug-in and promote it here? I want to provide a temporary solution for other users who will actually experience this problem.

Is it okay if I make a middleware plug-in that looks like an official plug-in and promote it here? I want to provide a temporary solution for other users who will actually experience this problem.

The timezone setting seems to involve rust code. Maybe it cannot be resolved by middleware.

I'm a big fan of Prisma. Please fix this issue

Internal note: https://www.notion.so/prismaio/Timezone-issues-in-query-engine-and-client-a1a2a9c6096a49f9a63db50c4c68bfe6

I would also like to see this issue resolved, please! Thanks!

My workaround for MySQL Database was:
Set the timezones inside MySQL:
SET time_zone='America/Sao_Paulo';
SET @@global.time_zone = '-3:00';

and in Prisma create Dates using NOW() MySQL Function:
model User {
  id         String    @id @default(uuid())
  name       String
  created_at DateTime  @db.DateTime @default(dbgenerated("NOW()"))
  updated_at DateTime? @updatedAt
}

Now create your user:
const user = await prisma.user.create({
  data: { name: "GGotha" },
});

@GGotha
Your workaround works fine. But there is a problem.
I changed it to dbgenerated("NOW()") . After that, if npx prisma db push is executed, Error: Invalid default value for '{column name}' error occurs. Is there any way to solve this?

@GGotha I'm newbie on prisma. Your workaround works fine. But there is a problem. I changed it to dbgenerated("NOW()") . After that, if npx prisma db push is executed, Error: Invalid default value for '{column name}' error occurs. Is there any way to solve this?

Same problem

@hiuny When you use npx prisma migrate dev occurs the same error?

@GGotha Wow, Thank you! It works. The model tested is as follows. Thank you very much.
model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(dbgenerated("NOW()")) @db.DateTime
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

@GGotha Wow, Thank you! It works. The model tested is as follows. Thank you very much.
model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(dbgenerated("NOW()")) @db.DateTime
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

Thanks createdAt successfully, how about updatedAt ?

@mfarchana
It seems that you cannot use the dbgenerated() function with the @updatedAt attribute. In this case, it will be difficult to use the @updatedAt attribute. It may be that I am a beginner and do not know how.

@hiuny For updated_at you should use: updated_at DateTime? @db.DateTime @default(dbgenerated("NOW() ON UPDATE NOW()")) without @updatedAt

@GGotha WOW~!! It works fine, migrate dev works fine too. Thank you very much!
model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(dbgenerated("NOW()")) @db.DateTime
  updatedAt DateTime? @db.DateTime @default(dbgenerated("NOW() ON UPDATE NOW()"))
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

thank you for trying, but non of these worked for PostgreSQL. Which I don't understand, if the default is CURRENT_TIMESTAMP then all you have to do is, nothing?
Can you explain what happens under the hood so Prisma overtakes database?

Would love to see this issue resolve. I cant use "NOW()" coz I have other DateTime columns where I want to set on specific scenarios only.

any news about this?

This problem has troubled me all day. This is my solution. This may make the performance worse, but I haven't found a better way.
// 时间偏移量 const offsetTime = 8 * 60 * 60 * 1000; prisma.$use(async (params, next) => {   // params转换为[[path.path,value]]数组   const paramsToArray = (obj: any) => {     const arr: [string, any][] = [];     for (const key in obj) {       if (obj[key] instanceof Date) {         arr.push([key, obj[key]]);       }       else if (typeof obj[key] === 'object') {         const arr2 = paramsToArray(obj[key]);         for (const [path, val] of arr2) {           arr.push([${key}.${path}`, val]);
}
}
}
return arr;
}
const whereData = paramsToArray(params.args.where).filter(([,val]) => val instanceof Date);
const orderByData = paramsToArray(params.args.orderBy).filter(([,val]) => val instanceof Date);
// 修改时间字段，减去offsetTime
whereData.forEach(([path, val]) => {
const arr= path.split('.');
const endKey = arr.pop()!;
let obj = params.args.where;
for (const key of arr) {
obj = obj[key];
}
console.log('eee',obj[endKey], new Date(obj[endKey].getTime() + offsetTime));
obj[endKey] = new Date(obj[endKey].getTime() + offsetTime);
});
orderByData.forEach(([path, val]) => {
const arr= path.split('.');
const endKey = arr.pop()!;
let obj = params.args.orderBy;
for (const key of arr) {
obj = obj[key];
}
obj[endKey] = new Date(obj[endKey].getTime() + offsetTime);
});
// 只要是Date类型，保存到数据库时间字段全都外加8小时
if (params.action === 'create' || params.action === 'update') {
console.log('保存和更新');
const { args } = params;
const { data } = args;
for (const key in data) {
if (data[key] instanceof Date) {
data[key] = new Date(data[key].getTime() + offsetTime);
}
}
}
else if (params.action === 'upsert') {
console.log('插入或更新');
const { args } = params;
const { create, update } = args;
for (const key in create) {
if (create[key] instanceof Date) {
create[key] = new Date(create[key].getTime() + offsetTime);
}
}
for (const key in update) {
if (update[key] instanceof Date) {
update[key] = new Date(update[key].getTime() + offsetTime);
}
}
}
else if (params.action === 'updateMany') {
console.log('批量更新');
const { args } = params;
const { data } = args;
for (const key in data) {
if (data[key] instanceof Date) {
data[key] = new Date(data[key].getTime() + offsetTime);
}
}
}
const res = await next(params);
if (res instanceof Array) {
for (const item of res) {
if (item instanceof Date) {
item.setTime(item.getTime() - offsetTime);
}
}
}
else{
for (const key in res) {
if (res[key] instanceof Date) {
res[key].setTime(res[key].getTime() - offsetTime);
}
}
}
return res;
});`
 DateTime  @db.DateTime @default(dbgenerated("NOW()"))

It seems that this can be achieved. I am really too sleepy. There is a lot of nonsense written on it. I finally solved this damn problem. I hope this will help you, although the performance may not be very high.
// 时间偏移量
const offsetTime = 8 * 60 * 60 * 1000;
const offsetKey = Symbol('key');
prisma.$use(async (params, next) => {
  console.log('params', params.args.select);
  // params转换为[[path.path,value]]数组
  const paramsToArray = (obj: any) => {
    const arr: [string, any][] = [];
    for (const key in obj) {
      if (obj[key] instanceof Date) {
        arr.push([key, obj[key]]);
      }
      else if (typeof obj[key] === 'object') {
        const arr2 = paramsToArray(obj[key]);
        for (const [path, val] of arr2) {
          arr.push([`${key}.${path}`, val]);
        }
      }
    }
    return arr;
  };
  // 遍历
  const setOffsetTime = (data: any, offsetTime: number) => paramsToArray(data).filter(([, val]) => val instanceof Date).forEach(([path, val]) => {
    const arr = path.split('.');
    const endKey = arr.pop()!;
    let obj = data;
    for (const key of arr) {
      if (obj[offsetKey]) return;
      obj = obj[key];
    }
    obj[offsetKey] = true;
    obj[endKey] = new Date(obj[endKey].getTime() + offsetTime);
  });
  setOffsetTime(params.args, offsetTime);
  const res = await next(params);
  setOffsetTime(res, -offsetTime);
  return res;
});

It seems that this can be achieved. I am really too sleepy. There is a lot of nonsense written on it. I finally solved this damn problem. I hope this will help you, although the performance may not be very high.
// 时间偏移量
const offsetTime = 8 * 60 * 60 * 1000;
const offsetKey = Symbol('key');
prisma.$use(async (params, next) => {
  console.log('params', params.args.select);
  // params转换为[[path.path,value]]数组
  const paramsToArray = (obj: any) => {
    const arr: [string, any][] = [];
    for (const key in obj) {
      if (obj[key] instanceof Date) {
        arr.push([key, obj[key]]);
      }
      else if (typeof obj[key] === 'object') {
        const arr2 = paramsToArray(obj[key]);
        for (const [path, val] of arr2) {
          arr.push([`${key}.${path}`, val]);
        }
      }
    }
    return arr;
  };
  // 遍历
  const setOffsetTime = (data: any, offsetTime: number) => paramsToArray(data).filter(([, val]) => val instanceof Date).forEach(([path, val]) => {
    const arr = path.split('.');
    const endKey = arr.pop()!;
    let obj = data;
    for (const key of arr) {
      if (obj[offsetKey]) return;
      obj = obj[key];
    }
    obj[offsetKey] = true;
    obj[endKey] = new Date(obj[endKey].getTime() + offsetTime);
  });
  setOffsetTime(params.args, offsetTime);
  const res = await next(params);
  setOffsetTime(res, -offsetTime);
  return res;
});

I've simplified the code again, which should help performance a lot
const offsetTime = 8 * 60 * 60 * 1000;
const offsetKey = Symbol('key');
prisma.$use(async (params, next) => {
  const setOffsetTime = (obj: any, offsetTime: number) => {
    if (obj[offsetKey]) return;
    for (const key in obj) {
      if (obj[key] instanceof Date) {
        obj[key] = new Date(obj[key].getTime() + offsetTime);
      }
      else if (typeof obj[key] === 'object') {
        setOffsetTime(obj[key], offsetTime);
      }
    }
    obj[offsetKey] = true;
  };
  // 所有的查询都加上时间偏移量
  setOffsetTime(params.args, offsetTime);
  const res = await next(params);
  // 结果全都减去时间偏移量
  setOffsetTime(res, -offsetTime);
  return res;
});

I can't justify the behaviour for datetime or timestamp columns.
The server my application runs on and my database are both set to eastern time. When I insert a date with Prisma of March 14, 3:03 am, I should not see March 13, 11:03 pm in my database. Even if reading the data back through Prisma works, the data in the database is incorrect. If I have other applications accessing this data, they will read the wrong date.
I see some people are handling this be manually adding and subtracting a static offset from their dates as they read and write them. This doesn't handle daylight savings time, so my solution for now has been to use a service like this:
function fixPrismaReadDate(d: Date): Date {
  return new Date(`${d.getUTCFullYear()}-${(d.getUTCMonth() + 1).toString().padStart(2, '0')}-${d.getUTCDate().toString().padStart(2, '0')}T${d.getUTCHours().toString().padStart(2, '0')}:${d.getUTCMinutes().toString().padStart(2, '0')}:${d.getUTCSeconds().toString().padStart(2, '0')}.${d.getUTCMilliseconds().toString().padStart(2, '0')}`); // use the UTC date and time but don't include the time zone so it will be assumed to be our time zone
}

function fixPrismaWriteDate(d: Date): Date {
  return new Date(`${d.getFullYear()}-${(d.getMonth() + 1).toString().padStart(2, '0')}-${d.getDate().toString().padStart(2, '0')}T${d.getHours().toString().padStart(2, '0')}:${d.getMinutes().toString().padStart(2, '0')}:${d.getSeconds().toString().padStart(2, '0')}.${d.getMilliseconds().toString().padStart(2, '0')}Z`); // use our local date and time, but add 'Z' time zone so it will be assumed to be UTC
}

And then I have to make sure to wrap any dates that I'm writing with Prisma with fixPrismaWriteDate to trick Prisma into writing the correct date to the database and wrap any dates that I read with Prisma with fixPrismaReadDate to get the proper date back out.

Maybe our ideas can be combined to put the handling of time into the Prisma plugin.
const offsetKey = Symbol('key');

function fixPrismaReadDate(d: Date): Date {
  return new Date(`${d.getUTCFullYear()}-${(d.getUTCMonth() + 1).toString().padStart(2, '0')}-${d.getUTCDate().toString().padStart(2, '0')}T${d.getUTCHours().toString().padStart(2, '0')}:${d.getUTCMinutes().toString().padStart(2, '0')}:${d.getUTCSeconds().toString().padStart(2, '0')}.${d.getUTCMilliseconds().toString().padStart(2, '0')}`); // use the UTC date and time but don't include the time zone so it will be assumed to be our time zone
}

function fixPrismaWriteDate(d: Date): Date {
  return new Date(`${d.getFullYear()}-${(d.getMonth() + 1).toString().padStart(2, '0')}-${d.getDate().toString().padStart(2, '0')}T${d.getHours().toString().padStart(2, '0')}:${d.getMinutes().toString().padStart(2, '0')}:${d.getSeconds().toString().padStart(2, '0')}.${d.getMilliseconds().toString().padStart(2, '0')}Z`); // use our local date and time, but add 'Z' time zone so it will be assumed to be UTC
}
prisma.$use(async (params, next) => {
  const setOffsetTime = (obj: any, fn: any) => {
    if (obj === null || obj[obj]) return;
    for (const key in obj) {
      if (obj[key] instanceof Date) {
        obj[key] = fn(obj[key]);
      }
      else if (typeof obj[key] === 'object') {
        setOffsetTime(obj[key], offsetTime);
      }
    }
    obj[offsetKey] = true;
  };
  // 所有的查询都加上时间偏移量
  setOffsetTime(params.args, fixPrismaWriteDate);
  const res = await next(params);
  setOffsetTime(res, fixPrismaReadDate);
  return res;
});

Thank you for the workaround @zjprie094 ! Using the above worked for most of our test cases, but caused errors for the following Prisma model queries:

Calling findMany() with no arguments leads to a "Cannot read properties of undefined (reading 'undefined')" error due to params.args being undefined.
Calling count() leads to a "TypeError: Cannot create property 'Symbol(key)' on number" error due to res being a number.

The fix was to expand the early return conditions within the setOffsetTime function to the following:
const setOffsetTime = (obj: any, fn: any) => {
    if (obj === undefined || obj === null || obj[obj] || !isNaN(obj)) return;
    for (const key in obj) {
      if (obj[key] instanceof Date) {
        obj[key] = fn(obj[key]);
      } else if (typeof obj[key] === "object") {
        setOffsetTime(obj[key], fn);
      }
    }
    obj[offsetKey] = true;
  };

Thank you for the workaround @zjprie094 ! Using the above worked for most of our test cases, but caused errors for the following Prisma model queries:

Calling findMany() with no arguments leads to a "Cannot read properties of undefined (reading 'undefined')" error due to params.args being undefined.
Calling count() leads to a "TypeError: Cannot create property 'Symbol(key)' on number" error due to res being a number.

The fix was to expand the early return conditions within the setOffsetTime function to the following:
const setOffsetTime = (obj: any, fn: any) => {
    if (obj === undefined || obj === null || obj[obj] || !isNaN(obj)) return;
    for (const key in obj) {
      if (obj[key] instanceof Date) {
        obj[key] = fn(obj[key]);
      } else if (typeof obj[key] === "object") {
        setOffsetTime(obj[key], fn);
      }
    }
    obj[offsetKey] = true;
  };

Thanks for the reply, this is something I didn't think of because I never knew findMany could be used like this, and my usage was always findMany({}) when there were no conditions.I forgot to filter 'offsetKey'.

Hehehe, since the Prisma Client returns all DateTimes as strings in ISO 8601 format, the
then he must support string values like this being inserted as " 2023-05-19T11:43:37+08:00" or whatever.
Processing:
When inserting
"2023-05-19T11:43:37+08:00".replace(/\+\d{2}:\d{2}$/, "Z")
Remove the "+08:00" time zone difference

Same issue.
As a suggestion, and for compatibility with existing database schemas and apps, a global configuration in prisma schema in previewFeatures like useLocaltime to solve this issue, or even on each column for better control.

Three years have passed, and such a low-level bug has not been fixed? What is the reason? With such a low-level bug, how can people use Prisma?
Other orm frameworks can do it, but prisma can't? Or the team doesn't want to do it?
It's a feature?
Who needs this mentally handicapped feature?

createdAt    DateTime @default(dbgenerated("NOW()"))  @db.DateTime(0) @map("created_at")
updateAt     DateTime @default(dbgenerated("NOW() ON UPDATE NOW()")) @updatedAt @db.DateTime(0)  @map("update_at")
This way, it should be able to solve this problem！

 createdAt    DateTime @default(dbgenerated("NOW()"))  @db.DateTime(0) @map("created_at")
 updateAt     DateTime @default(dbgenerated("NOW() ON UPDATE NOW()")) @updatedAt @db.DateTime(0)  @map("update_at")

This way, it should be able to solve this problem！

@MinGlizz
I'm receiving this erro trying use your solution:
Value out of range for the type. The column updated_at contained an invalid datetime value with either day or month set to zero.
Could you please help me to solve it?

createdAt    DateTime  @default(dbgenerated("NOW()")) @map("created_at") @db.DateTime(0)
  updateAt     DateTime  @default(dbgenerated("NOW() ON UPDATE NOW()")) @updatedAt @map("update_at") @db.DateTime(0)

This does not work for postgres: Native type DateTime is not supported for postgresql connector.
Is there a clean workaround for postgres?

I'm very sad that Prisma doesn't have good timezone processing.
I used @zjprie094 's code, it works for me. But still have some problems that need attention.

It does not work for string datetime, because Primsa's datetime type is string | Date, but I don't think has a good solution without losing performance.
This comment, It does not work for me, I still get the wrong when serializing to ISO8601, so I added my timezone +08:00 in the datetime string's end.

//  use the UTC date and time but don't include the time zone so it will be assumed to be our time zone

My final code:
function fixPrismaReadDate(d: Date): Date {
  return new Date(
    `${d.getUTCFullYear()}-${(d.getUTCMonth() + 1).toString().padStart(2, '0')}-${d
      .getUTCDate()
      .toString()
      .padStart(2, '0')}T${d.getUTCHours().toString().padStart(2, '0')}:${d
      .getUTCMinutes()
      .toString()
      .padStart(2, '0')}:${d.getUTCSeconds().toString().padStart(2, '0')}.${d
      .getUTCMilliseconds()
      .toString()
      .padStart(2, '0')}+08:00`,
  );
}

function fixPrismaWriteDate(d: Date): Date {
  return new Date(
    `${d.getFullYear()}-${(d.getMonth() + 1).toString().padStart(2, '0')}-${d.getDate().toString().padStart(2, '0')}T${d
      .getHours()
      .toString()
      .padStart(2, '0')}:${d.getMinutes().toString().padStart(2, '0')}:${d.getSeconds().toString().padStart(2, '0')}.${d
      .getMilliseconds()
      .toString()
      .padStart(2, '0')}Z`,
  ); // use our local date and time, but add 'Z' time zone so it will be assumed to be UTC
}

client.$use(async (params, next) => {
  const setOffsetTime = (obj: any, fn: any) => {
    if (obj === undefined || obj === null || obj[obj] || !isNaN(obj)) return;
    for (const key in obj) {
      if (obj[key] instanceof Date) {
        obj[key] = fn(obj[key]);
      } else if (typeof obj[key] === 'object') {
        setOffsetTime(obj[key], fn);
      }
    }
    obj[dateTimeOffsetKey] = true;
  };

  setOffsetTime(params.args, fixPrismaWriteDate);
  const res = await next(params);
  setOffsetTime(res, fixPrismaReadDate);
  return res;
});

inspired by @smilecc I prepared a solution with exported hook useFixPrismaTimezone to apply on client with any timezone. It uses dayjs to simplify date manipulation.
/**
 * Prisma does not support timezones for db other than UTC
 * https://github.com/prisma/prisma/issues/5051
 *
 * This fix lies to Prisma about dates using database time
 * packed as it would be UTC time, prisma being unaware of
 * timezones passing these incorrect dates to a database that
 * uses them as dates in the db timezone
 *
 * This solution can have a negative impact on performance
 * when input or output objects have a lot of keys or nesting
 * levels, but it should be acceptable because of a similar level
 * of overhead have any serialization / deserialization process
 */

import { PrismaClient } from '@prisma/client';
import dayjs from 'dayjs';
import timezone from 'dayjs/plugin/timezone';

dayjs.extend(timezone);

function fixPrismaReadDate(databaseTimeZone: string): (d: Date) => Date {
  return (d: Date): Date => dayjs(dayjs(d).tz(databaseTimeZone).format('YYYY-MM-DDTHH:mm:ss.SSSZ')).toDate();
}

function fixPrismaWriteDate(databaseTimeZone: string): (d: Date) => Date {
  // use our local date and time, but add 'Z' time zone, so it will be assumed to be UTC
  return (d: Date): Date => dayjs(dayjs(d).tz(databaseTimeZone).format('YYYY-MM-DDTHH:mm:ss.SSS') + 'Z').toDate();
}

function applyFunctionToDates(obj: unknown, func: (date: Date) => Date) {
  if (obj instanceof Date) {
    // If obj is a Date, apply the function and update it
    return func(obj);
  } else if (typeof obj === 'object' && obj !== null) {
    // If obj is an object, recursively apply the function to its properties
    for (const key in obj) {
      if (obj.hasOwnProperty(key)) {
        // eslint-disable-next-line @typescript-eslint/ban-ts-comment
        // @ts-ignore
        obj[key] = applyFunctionToDates(obj[key], func);
      }
    }

    return obj;
  } else {
    // For any other type, return it as is
    return obj;
  }
}

export function useFixPrismaTimezone(client: PrismaClient, databaseTimezone: string = 'Europe/Warsaw') {
  client.$use(async (params, next) => {
    params.args = applyFunctionToDates(params.args, fixPrismaWriteDate(databaseTimezone));
    let res = await next(params);
    res = applyFunctionToDates(res, fixPrismaReadDate(databaseTimezone));

    return res;
  });
}

usage
useFixPrismaTimezone(client, 'Europe/Warsaw');

Why is this problem still there after four years? There is also the problem of connecting to multiple databases. Choosing Prisma was a mistake, and I'm going back to Sequlize.

 createdAt    DateTime @default(dbgenerated("NOW()"))  @db.DateTime(0) @map("created_at")
 updateAt     DateTime @default(dbgenerated("NOW() ON UPDATE NOW()")) @updatedAt @db.DateTime(0)  @map("update_at")

This way, it should be able to solve this problem！

It also worked for me with posrgres like below
  created_at   DateTime  @default(now()) @db.Timestamptz(3)
  updated_at   DateTime  @updatedAt @db.Timestamptz(3)

createdAt    DateTime  @default(dbgenerated("NOW()")) @map("created_at") @db.DateTime(0)
  updateAt     DateTime  @default(dbgenerated("NOW() ON UPDATE NOW()")) @updatedAt @map("update_at") @db.DateTime(0)

This does not work for postgres: Native type DateTime is not supported for postgresql connector.
Is there a clean workaround for postgres?

  created_at   DateTime  @default(now()) @db.Timestamptz(3)
  updated_at   DateTime  @updatedAt @db.Timestamptz(3)

Why is this problem still there after four years? I'm going back to Typeorm.

I'm not sure I understand the problem. Shouldn't you always store your data in UTC? When you display it you can transform it to any timezone you want. I don't think I've ever wanted to store my data in anything other than UTC, and Prisma not caring about what timezone my DB says it's in (it's synced with the system timezone, I don't want to constantly set it for mysql separately) is a feature to me.

some times we just store time in utc, but we need select result with some timezone, when we use other orm, we can set timezone in connect option, but prisma is fk....

I can't believe this issue has persisted until today. My account zjprie094 has already been discontinued.

The issue is still not solved!

Too many unsolved limitations. Drizzle is catching up.

Hello everyone I am also suffering from this problem! And I would like to know where do we tell the Prisma team about this problem? :0

i dont know if it helps, but this workaround using luxon save me one crucial presentation to my client:
const timestamp = DateTime.now().toUTC();
const localtimestamp = timestamp.plus({ hours: 8 }).toISO();

when i save the localtimestamp to mariadb, its still in the timezone i wanted which is GMT+8. i hope this wont causes me or my colleague any headache in the future XD

I'm not sure if this code is efficient, but I solved the timezone issue with the middleware settings to force a conversion using the code above.
// prisma.service
setMiddlewares() {
this.$use(async (params, next) => {
const result = await next(params);
  adjustDates(result, convertUTCDateToKST);

  return result;
});

}
// date.middleware
export function convertUTCDateToKST(date) {
const utcDate = new Date(date);
utcDate.setHours(utcDate.getHours() + 9);  // Add 9 hours to the UTC date to convert it to KST (Korea Standard Time)
return utcDate;
}
export function convertDateToUTC(date) {
const localDate = new Date(date);
localDate.setHours(localDate.getHours() - 9);  // Subtract 9 hours from the local date to convert it to UTC time
return localDate;
}
export function adjustDates(obj, dateConversionFunction) {
if (!obj || typeof obj !== 'object') return;  // Check if the input is not an object or is null, then exit the function
for (const key of Object.keys(obj)) {
// If the property is a Date object, apply the provided date conversion function
if (obj[key] instanceof Date) {
obj[key] = dateConversionFunction(obj[key]);
}
// If the property is an object, recursively adjust its dates
else if (typeof obj[key] === 'object') {
adjustDates(obj[key], dateConversionFunction);
}
}
}

I chose Prisma for my upcoming product , I write 70% code with prisma.  I thought , i will finish the core logic then will lookinto this timezone issue. Seeing this incapability , i am very disapointed now

I'm not sure if this code is efficient, but I solved the timezone issue with middleware settings to force a conversion. I used the people's code above
// prisma.service
  setMiddlewares() {
    this.$use(async (params, next) => {
      const result = await next(params);

      adjustDates(result, convertUTCDateToKST);

      return result;
    });
  }

// date.middleware
export function convertUTCDateToKST(date) {
 const utcDate = new Date(date);
 utcDate.setHours(utcDate.getHours() + 9);  // Add 9 hours to the UTC date to convert it to KST (Korea Standard Time)
 return utcDate;
}

export function convertDateToUTC(date) {
 const localDate = new Date(date);
 localDate.setHours(localDate.getHours() - 9);  // Subtract 9 hours from the local date to convert it to UTC time
 return localDate;
}

export function adjustDates(obj, dateConversionFunction) {
 if (!obj || typeof obj !== 'object') return;  // Check if the input is not an object or is null, then exit the function

 for (const key of Object.keys(obj)) {
   // If the property is a Date object, apply the provided date conversion function
   if (obj[key] instanceof Date) {
     obj[key] = dateConversionFunction(obj[key]);
   }
   // If the property is an object, recursively adjust its dates
   else if (typeof obj[key] === 'object') {
     adjustDates(obj[key], dateConversionFunction);
   }
 }
}

This may result in extra latency in the api

It is amazing that it's been 4 years.
@default(dbgenerated("NOW()")) seems work.
However, @updatedAt also doesn't respect timezone neither. So it is useless.

Besides, it's interesting that dbgenerated("NOW()") and @updatedAt are not even synced.

Still existing until today wtf🤣

What about writing a prisma driver adapter for mysql2 or mariadb package in order to bypass prisma driver and have better support ?
It may solve numerous issues with prisma mysql driver.
We could base on the code of the official pg adapter @prisma/adapter-pg
Is anybody tried or started to do it ?

It's unbelievable, the problem of 2020 proposed a solution in 2023, and now in 2024, the official did not solve it and fixed the solution to the problem...

I'm not sure if this is the correct code, but I solved it this way based on everyone's discussions, so I'd like to share it.

Next.js: 14.2.3
prisma: 5.14.0

I'm worried that Prisma Middleware's $use is Deprecated. https://www.prisma.io/docs/orm/prisma-client/client-extensions/middleware
app/_libs/db.ts
const prismaClientSingleton = () => {
  return new PrismaClient({
    // log: ["query", "warn", "error"],
  });
};

declare global {
  var prismaGlobal: undefined | ReturnType<typeof prismaClientSingleton>;
}

const prisma = globalThis.prismaGlobal ?? prismaClientSingleton();

prisma.$use(async (params, next) => {
  const result = await next(params);

  adjustDates(result, convertDateToUTC);

  return result;
});

export default prisma;

if (process.env.NODE_ENV !== "production") globalThis.prismaGlobal = prisma;

app/_libs/date.middleware.ts
export const convertUTCDateToJST = (date: Date) => {
  const utcDate = new Date(date);
  // Add 9 hours to the UTC date to convert it to JST
  utcDate.setHours(utcDate.getHours() + 9);
  return utcDate;
};

export const convertDateToUTC = (date: Date) => {
  const localDate = new Date(date);
  // Subtract 9 hours from the local date to convert it to UTC
  localDate.setHours(localDate.getHours() - 9);
  return localDate;
};

export const dbNow = () => {
  return convertUTCDateToJST(new Date());
};

export const adjustDates = (
  obj: { [x: string]: any },
  dateConversionFunction: { (date: Date): Date; (arg0: any): any }
) => {
  if (!obj || typeof obj !== "object") {
    return;
  }

  if (obj["isUTC"]) {
    return;
  }

  for (const key of Object.keys(obj)) {
    // If the property is a Date object, apply the provided date conversion function
    if (obj[key] instanceof Date) {
      obj[key] = dateConversionFunction(obj[key]);

      // Since it is executed several times, probably due to caching,
      // setting a flag that it has been converted to UTC.
      obj["isUTC"] = true;
    }
    // If the property is an object, recursively adjust its dates
    else if (typeof obj[key] === "object") {
      adjustDates(obj[key], dateConversionFunction);
    }
  }
};

CreateInput
const validateForm = (formData: FormData) => {
  const createInput: Prisma.***UncheckedCreateInput = {
      ***_created_at: formData.get("***CreatedAt")
      ? convertUTCDateToJST(new Date(formData.get("***CreatedAt") as string))
      : null,
    created_at: dbNow(),
    updated_at: dbNow(),
  };
  // zod
  return ***CreateInput.safeParse(createInput);
}

I am using the following environment:

NestJS
MySQL

default-time-zone = 'Asia/Tokyo'

Even when using the timestamp column, there was a discrepancy, so I addressed it as follows:
  async onModuleInit() {
    await this.$executeRaw`SET time_zone = 'UTC'`;
  }

I hope Prisma will solve this problem...

Does Prisma have a plan to fix this?? Or they just want users to accept

My gosh, please

👀

// fix with +8:00 timezone
const timezone = 8;
const offset = timezone * 60 * 60 * 1000;
// read from mysql:
new Date(new Date(value).getTime() - offset);
// write to mysql:
new Date(new Date(value).getTime() + offset).toISOString()

For MySQL
createdAt   DateTime  @default(dbgenerated("NOW()"))
updatedAt   DateTime  @default(dbgenerated("NOW() ON UPDATE NOW()"))

If you have already used the UTC timezone, you need to convert the time zone when the data is queried.
...
WHERE CONVERT_TZ(t.createdAt, 'UTC', 'Asia/Shanghai') <= '2024-08-17 16:30:00';

Or convert it in the code.
import dayjs from 'dayjs';
import utc from 'dayjs/plugin/utc';

dayjs.extend(utc);

...
WHERE t.createdAt >= '${dayjs(startDate).utc().format('YYYY-MM-DD HH:mm:ss')}'`
AND t.createdAt <= '${dayjs(endDate).utc().format('YYYY-MM-DD HH:mm:ss')}'`

If your customers are all over the world, maintaining the UTC timezone is a good choice.

For MySQL
createdAt   DateTime  @default(dbgenerated("NOW()"))
updatedAt   DateTime  @default(dbgenerated("NOW() ON UPDATE NOW()"))

dbgenerated is not working properly with mysql https://github.com/prisma/prisma/issues/11374

If your customers are all over the world, maintaining the UTC timezone is a good choice.

I disagree.
The timezone used in database representation has nothing to do with customers location. Database representation is used by developers and sys admins. So if your developers and system administrators are located in a single timezone and has all their tools (especially logs) in their local timezone, having a local timezone for database representation makes sense.
Then, if you have customers all over the world, or if dates are formatted on the client side, ISO format is a good choice for API output representation (both UTC/Z or timezone offset are OK cause it is parsed correctly by javascript Date or dayjs or any other tool).

I wasted three days trying to solve this time zone problem

An workaround is call await tx.$queryRawUnsafe("SET time_zone = '+00:00';");

The problem occurs when writing the dates. Internally, there’s a conversion to UTC, but if the database is in a different time zone, it causes issues with the data. An workaround is call:
await prisma.$queryRawUnsafe("SET time_zone = '+00:00';");

Maybe one solution is the prisma mysql driver call this always after opening the connection...

It need first to be validated as a working solution.
This query set the database server timezone to UTC (and not connection timezone to a specified timezone which is the need).
1°) This may impact other connections to database (from other apps and framework that used non UTC timezone)
2°) As far as I understand, from a cheaset about mysql timezone

It does not change any existing values in database
It changes the value read from timestamp column but not datetime ones.

So without testing I can tell you that this solution does not fully work and have side effects, likes all hacky solutions proposed above.
Here the (very common) situation:

People have an existing database with data in it
The database timezone is set to their local timezone for convenience and because THIS IS A GOOD PRACTICE*
They have an existing app implemented with another ORM or framework which supports connection timezone (All mysql drivers in all languages support connection timezone except prisma one)
They want to switch from their previous ORM to Prisma because it is sexy (and type friendly).
(But they may not migrate 100% of their app at once and may have other apps connected to the same database using a timezoned connection)

For a solution to work, it should :

Not change database timezone
Write datetimes in the database timezone (this is the definition of this type. DateTimes are Date + Time expressed related to database timezone)
Convert date from raw SQL results (which are expressed as string related to database timezone into the javascript format)

The role of a database driver is to convert SQL raw result into the client language types and vice-versa.
It get a string like this
`id`, `text`, `date`
17, 'value1', '2024-09-20 10:30:11'
32, 'value2', '2024-09-25 10:30:11'

And convert it into a javascript array:
[
  { id: 17, text: 'value1', date: Date("2024-09-20 10:30:11") },
  { id: 32, text: 'value2', date: Date("2024-09-25 10:30:11") },
]

In raw SQL, datetimes and timestamps are expressed in a YYYY-MM-DD hh:mm:ss format (without offset), related to database timezone.
So the correct code to convert a SQL Datetime (ex "2024-09-20 10:30:11") to javascript Date is to use the database timezone.
This also applies to the reverse conversion (javascript to sql)
To simplify implementation and because knowning the database timezone is a bit difficult, mysql drivers accept a standard timezone setting in order to apply this connection.
So the fix is trivial. This is basically adding an option and a few lines of code in the date converter of the driver.
Another solution would be to support the use of existing mysql drivers for nodejs (mysql, mysql2, mariadb) which are more performant, open source, and supporting timezone.

Setting database timezone to local timezone IS A GOOD PRACTICE (to my opinion)
The frontend of an app should never be able to send raw SQL query to database and never or rarely display raw sql result to endusers (especially for dates).
TLDR end users never see the raw SQL date format ("2024-09-20 10:30:11"), they always see a converted result (which have been converted at least once to Javascript or JSON)

The database timezone defines how datetime and timestamps are represented (i.e. formatted) in raw SQL result.
So this setting as nothing to do with you user location. Having users all over the world does not means you have or should set your database timezone to UTC.
The only people which have access and uses the raw SQL output are developers (when debugging or browsing database using a client) and system admins.
The need here is to be able to correlate the dates in raw SQL results and database browsing with the other dates we have (Dates used in logs, and current datetime).
Having dates in SQL in UTC while being is a different timezone and having logs with a different timezone is not convenient AND error prone.
So the good practice is to set database timezone to developers and system admins timezone. It makes sense to keep UTC timezone only if you have developers and sys admins in different timezones.

Still exist until today wtf🤣move to drizzle guys, better performance 😅 we can insert and select with our timezone custom type

Never used drizzle before. Just checked out the docs. Wouldn't default(sqlnow()) just work? With regards to respecting the timezone set in the DB?

It is amazing that it's been 4 years.
@default(dbgenerated("NOW()")) seems work. However, @updatedAt also doesn't respect timezone neither. So it is useless.

Besides, it's interesting that dbgenerated("NOW()") and @updatedAt are not even synced.

Interesting how this is handled under the hood. Because when I look at the migration file the difference is between current_timestamp and now. For dbgenerated, the command is TIMESTAMP(3) NOT NULL DEFAULT NOW() while for the datetime with default(now) it is TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP. In postgres these two commands basically do the same thing. So where could the difference be coming from?

It is amazing that it's been 4 years.
@default(dbgenerated("NOW()")) seems work. However, @updatedAt also doesn't respect timezone neither. So it is useless.

Besides, it's interesting that dbgenerated("NOW()") and @updatedAt are not even synced.

Interesting how this is handled under the hood. Because when I look at the migration file the difference is between current_timestamp and now. For dbgenerated, the command is TIMESTAMP(3) NOT NULL DEFAULT NOW() while for the datetime with default(now) it is TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP. In postgres these two commands basically do the same thing. So where could the difference be coming from?

The default in database is not used by prisma when @updatedAt is set, the date value is set from javascript, before insert / update.
Basically, when a column is define with @updatedAt, Prisma alter the column with DEFAULT CURRENT_TIMESTAMP or  DEFAULT NOW in migration, ..., but does not uses it.
But default values is not the only issue. Regular datetimes are also biaised with timezone.

+1

Still exist until today wtf🤣move to drizzle guys, better performance 😅 we can insert and select with our timezone custom type

Never used drizzle before. Just checked out the docs. Wouldn't default(sqlnow()) just work? With regards to respecting the timezone set in the DB?

Update. Tested this on drizzle. It works!

I broke down!!! Due to timezone issues, Prisma's business cannot be used in many regions. Perhaps its idea is good, but it is too idealistic, so it can only be abandoned.

authors: 10134 Aeolun DRL9 FredericLatour GGotha ICE-XIAOWU JL978 Jay-flow Jolg42 Jon-Millent Lynn-cc MinGlizz Pigrabbit Qubit-01 Scarlett13 SevInf Syntax73 TooBug WakilEfoli ZhiXiao-Lin ahathaway anaibol arielyang bentwonk brendan620 buzai chinaBerg conioX felicienfrancois felipesmendes gustawdaniel henriqueArrazao hiuny hmmhmmhm israelins85 jamesdanner jhaemin kingwrcy kiwavi lugrinder matthewmueller maxbitcoin mchao123 mfarchana move-zig mpfo0106 munepom pantharshit00 pimeys rakihub rawi96 rtf-const salyndev0 smilecc takecchi thetminko tje3d tombohub v29neil wangchihai weiliang903 winner106 wowuliao11 zhujinxuan zjprie094 zr9


text:
Timezone not returned with queryRaw for timetz data type

Bug description
When you do a raw query for some data that is typed timetz, the data you get back does not have the timezone included.
Here is the raw query against the database:
devidgmefdn94u=> select * from classes;
 id | day_of_week | class_time  | instructor | class_type | virtual
----+-------------+-------------+------------+------------+---------
 19 |           5 | 11:30:00+00 |          3 |          7 | t
 20 |           5 | 11:30:00+00 |          3 |          7 | t
 48 |           1 | 12:00:00-05 |          3 |          5 | t

Here is the code that I'm executing:
    // const data = await prisma.$queryRaw<classes[]>`select * from classes;`
    const data = await prisma.$queryRaw`select * from classes;`
    console.log(data)

The output of that code is this:
[
  {
    id: 19,
    day_of_week: 5,
    class_time: '11:30:00',
    instructor: 3,
    class_type: 7,
    virtual: true
  },
  {
    id: 20,
    day_of_week: 5,
    class_time: '11:30:00',
    instructor: 3,
    class_type: 7,
    virtual: true
  },
  {
    id: 48,
    day_of_week: 1,
    class_time: '12:00:00',
    instructor: 3,
    class_type: 5,
    virtual: true
  }
]

As you can see, the class_time here has its timezone stripped out at some point in this process.
How to reproduce
You can use the following SQL to reproduce the code I have above:
CREATE TABLE instructors (
    id serial,
    name text unique not null,
    image_url text unique not null,
    PRIMARY KEY (id)
);

CREATE TABLE class_types (
    id serial,
    name text not null,
    description text not null,
    duration integer not null,
    image_url text,
    PRIMARY KEY (id)
);

CREATE TABLE classes (
    id serial,
    day_of_week integer not null,
    class_time timetz not null,
    instructor integer not null,
    class_type integer not null,
    virtual boolean default true,
    PRIMARY KEY (id),
    FOREIGN KEY (instructor) REFERENCES instructors (id) ON DELETE cascade,
    FOREIGN KEY (class_type) REFERENCES class_types (id)
);

Expected behavior
Include the zone in the returned string e.g. 12:00:00-05 or 11:30:00+00
Prisma information

Environment & setup

OS: Mac OS
Database:

Client: Postgres 13.3
Server:  PostgreSQL 12.7 (Ubuntu 12.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit

Node.js version: v12.22.1

Prisma Version
prisma               : 2.25.0
@prisma/client       : 2.25.0
Current platform     : darwin
Query Engine         : query-engine c838e79f39885bc8e1611849b1eb28b5bb5bc922 (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli c838e79f39885bc8e1611849b1eb28b5bb5bc922 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core c838e79f39885bc8e1611849b1eb28b5bb5bc922 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt c838e79f39885bc8e1611849b1eb28b5bb5bc922 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : c838e79f39885bc8e1611849b1eb28b5bb5bc922
Studio               : 0.402.0

I can reproduce this, thanks for the detailed reproduction.

source: gharchive/issue

repo: prisma/prisma

license_type: permissive

license_source: github-api


text:
Support Prisma in Next.js middleware without using the Data Proxy

Problem
Related to #85 #9841
Next.js is working on middleware support which runs in V8 environment. Currently, it is not possible to run Prisma there.
Suggested solution
Since Prisma 3.3.0 we have Early Access support for Cloudflare Workers (which run in V8 environment too), it would be nice to extend that functionality to support Next.js middleware as well.

We discussed this one more in depth. It's unfortunately a hard problem because Next.js essentially has added another type of Javascript environment depending on the file you're in. We've handled browser vs. server environments with the package.json, but this is a new environment specific to the Next.js file layout, so no generic way to know.
For server-side rendering (SSR) it continues to make sense to use the default Node-API implementation of the Prisma Client. But the Node-API implementation wouldn't work inside the Next.js middleware, so to pull this off right now you'd need two Prisma Clients, one built for SSR, one built for Edge Functions.
Longer-term, I could see us always generating one package for each environment, that you could switch out depending on the context:
import { PrismaClient } from "@prisma/client" // used in pages/index.js
import { PrismaClient } from "@prisma/edge" // used in _middleware.js

authors: matthewmueller patryk-smc


text:
fix: Negative zero warning in dependencies

Appears in fetch-engines->node-fetch->whatwg-url->webidl-conversions
package. Ideally, we'd like to be able to update node-fetch@3, but it's
currently blocked. node-fetch@2 uses whatwg-url for polyfilling URL
object if it is not present. Since we support node 14+, we never need
this polyfill and we can patch out node-fetch->whatwg-url dependency.
Fix #14188

I guess, @SevInf that node-fetch@3 is blocked because it's an ESM-only module?

source: gharchive/pull-request


text:
toggle backlight possible?

Hiprisme60, glad i found your repository!
My Gartenwasser project (http://github.com/bgewehr/Gartenwasser) uses the LCD-i2c-keypad library from here.
I am looking for a way to switch off the blue backlight but found lcd.write(0x3F) not working as documented in web sources:
https://github.com/fehmer/adafruit-i2c-lcd/issues/8
So do you have a clue what to send for switching the blue backlight off?
Thank you!

O dear, my fault, i used the wrong library.
Taken yours and everything is allright!
Sorry for the wrong issue.
Thank you for your work and help!

license_source: bigquery


text:
Invalid elliptic curve point encoding in proof

This happens with this test
---- autogen_aggregation_verifier stdout ----
thread 'autogen_aggregation_verifier' panicked at 'called `Result::unwrap()` on an `Err` value: Transcript(Other, "Invalid elliptic curve point encoding in proof")', /app/prover/src/aggregation_circuit.rs:213:26

Hey @pinkiebell, would you like to try to patch halo2_proofs with this branch https://github.com/han0110/halo2/tree/feature/configurable-instance-query?
I think in halo2@v2022_09_10 the instance commitments are still written to transcript, so plonk-verifier gets confused by some extra commitments there.
(hopefully these patching issues will never show again after both https://github.com/privacy-scaling-explorations/halo2/pull/96 and https://github.com/privacy-scaling-explorations/plonk-verifier/pull/5 are merged...)


text:
GPU memory usage

Hi, compared to ResNet, what the GPU memory usage is for ResNeXt? Will this take more GPU memory? Thanks.

Hi, compared to ResNet, what the GPU memory usage is for ResNeXt? Will this take more GPU memory? Thanks.

@whuhxb
As the paper said, these two models have similar numbers of parameters. Hence GPU memory usage must have similar as well if it's implemented correctly.

authors: SarperYurttas whuhxb

license: mit


text:
Webhook event recieved but no triggers function

Bug Report
when creating a check_suite smee will report status 200, and in the probot app log I see the Post request is logged but it doesn't trigger the function with the context.
I tried clearing all smee servers using killall node
it use to work the only difference I'm working on a new smee server..
A clear and concise description of the behavior.
module.exports = app => {
  app.on(['check_suite.requested', 'check_run.rerequested'], check)

  async function check (context) {
    const startTime = new Date()
    console.log(context)
    // Do stuff
    const { head_branch: headBranch, head_sha: headSha } = context.payload.check_suite
    // Probot API note: context.repo() => {username: 'hiimbex', repo: 'testing-things'}
    return context.github.checks.create(context.repo({
      name: 'My app!',
      head_branch: headBranch,
      head_sha: headSha,
      status: 'completed',
      started_at: startTime,
      conclusion: 'failure',
      completed_at: new Date(),
      output: {
        title: 'Probot check!',
        summary: 'The check has passed!'
      }
    }))
  }
}

this is my .env file :
WEBHOOK_PROXY_URL=https://smee.io/JWLzGGfhdCuXbUC
APP_ID=123456
WEBHOOK_SECRET=secret
LOG_LEVEL=debug

this is the log I get from the probot app:
06:00:08.227Z  INFO probot: Listening on http://localhost:3000
06:00:08.533Z  INFO probot: Connected https://smee.io/JWLzGGfhdCuXbUC
06:00:08.586Z DEBUG github: GitHub request: GET https://api.github.com/app/installations?per_page=100 - 200 OK
  params: {
    "baseUrl": "https://api.github.com",
    "mediaType": {
      "format": "",
      "previews": []
    },
    "request": {}
  }
{ Deprecation: [@octokit/rest] Setting the "new Octokit({ auth })" option to an object without also setting the "authStrategy" option is deprecated and will be removed in v17. See (https://octokit.github.io/rest.js/#authentication)
    at authenticationPlugin (/Users/hadar/Desktop/probot/softacheck/node_modules/@octokit/rest/plugins/authentication/index.js:58:5)
    at Octokit.plugins.forEach.pluginFunction (/Users/hadar/Desktop/probot/softacheck/node_modules/@octokit/rest/lib/constructor.js:26:37)
    at Array.forEach (<anonymous>)
    at new Octokit (/Users/hadar/Desktop/probot/softacheck/node_modules/@octokit/rest/lib/constructor.js:26:11)
    at Object.GitHubAPI (/Users/hadar/Desktop/probot/softacheck/node_modules/probot/lib/github/index.js:23:19)
    at cache.wrap.ttl (/Users/hadar/Desktop/probot/softacheck/node_modules/probot/lib/application.js:232:102)
    at process._tickCallback (internal/process/next_tick.js:68:7) name: 'Deprecation' }
06:00:09.195Z DEBUG github: GitHub request: GET https://api.github.com/installation/repositories?per_page=100 - 200 OK (installation=10584118)
  params: {
    "baseUrl": "https://api.github.com",
    "mediaType": {
      "format": "",
      "previews": []
    },
    "request": {}
  }
06:00:32.385Z DEBUG probot: Webhook received
  event: {
    "event": "push",
    "id": "d2a137bc-c7f2-11ea-9dfb-48cdd544f35e",
    "installation": 10584118,
    "repository": "hadar-cpp-check/test-repo"
  }
06:00:32.386Z  INFO http: POST / 200 - 3.52 ms (id=9bf5a179-1048-4069-be32-17f77d91b708)
06:00:32.388Z DEBUG probot: Webhook received
  event: {
    "event": "push",
    "id": "d2a137bc-c7f2-11ea-9dfb-48cdd544f35e",
    "installation": 10584118,
    "repository": "hadar-cpp-check/test-repo"
  }
06:00:32.388Z  INFO http: POST / 200 - 0.67 ms (id=9bf5a179-1048-4069-be32-17f77d91b708)
06:00:32.390Z  INFO probot: POST http://localhost:3000/ - 200
06:00:32.535Z DEBUG probot: Webhook received
  event: {
    "event": "check_suite.requested",
    "id": "d2bf8000-c7f2-11ea-93b4-f0a628e9aa3e",
    "installation": 10584118,
    "repository": "hadar-cpp-check/test-repo"
  }
06:00:32.535Z  INFO http: POST / 200 - 0.88 ms (id=ef0d258f-4eaa-4bf6-9c9d-3923948578cc)
06:00:32.537Z DEBUG probot: Webhook received
  event: {
    "event": "check_suite.requested",
    "id": "d2bf8000-c7f2-11ea-93b4-f0a628e9aa3e",
    "installation": 10584118,
    "repository": "hadar-cpp-check/test-repo"
  }
06:00:32.537Z  INFO http: POST / 200 - 0.71 ms (id=ef0d258f-4eaa-4bf6-9c9d-3923948578cc)
06:00:32.538Z  INFO probot: POST http://localhost:3000/ - 200```

and this is smee screen shot:
![Screen Shot 2020-07-16 at 11 02 42 PM](https://user-images.githubusercontent.com/66395495/87753903-85262180-c7b8-11ea-8b4a-e53c45f7f94e.png)

I cannot tell what's wrong, the logs look good to me. Please share if you find out the cause of the problem

a

a

authors: gr2m hadar-cpp-check hugozanini

repo: probot/probot

license: ISC


text:
Should placing a library inside the libraries folder generate a script-src tag in index.html?

The intuition (at least for me) is that all I'd need to do to include an additional library is to place it in the libraries folder:

Or perhaps it could prompt the user...
Also, I notice this comment in index.html:
    <!-- Uncomment the lines below to include extra p5 libraries -->
    <script src="libraries/p5.dom.js" type="text/javascript"></script>
    <script src="libraries/p5.sound.js" type="text/javascript"></script>
    <script src="libraries/rita.js" type="text/javascript"></script>

however, none of the lines below are actually commented...

good points! More generally I'd like to implement a library management gui. Something that lets you add and remove third party libraries.

Similarly, when adding a new Tab, can we assume that you want to link the tab in your index.html, and auto-generate the appropriate script / link tag?

Was about to chime in and noticed @therewasaguy beat me to it!  I think it's better to assume it will be used and files can be removed manually.  Rather than have to add to index.html manually.

authors: antiboredom dhowe shiffman therewasaguy


text:
Migrate to GitHub Actions for CI

this is the last p5.js project using travis (which we now have to pay for), would love to get this on GitHub Actions too!

@therewasaguy would you mind taking a look at this and make sure it looks okay to you? thank you!


text:
very good

고맙읍니다.. 왜 이걸 이제야 발견한건지 ㅠ

감사함다!

license: WTFPL


text:
Dev: Add .nvmrc

Change-type: patch
Signed-off-by: Josh Bowling josh@monarci.com

@balena-ci I self certify!

repo: product-os/jellyfish-jellyscript


text:
Update scalafmt-core to 3.0.8

Updates org.scalameta:scalafmt-core from 2.7.5 to 3.0.8.
GitHub Release Notes - Version Diff
I'll automatically update this PR to resolve conflicts as long as you don't change it yourself.
If you'd like to skip this version, you can just close this PR. If you have any feedback, just mention me in the comments below.
Configure Scala Steward for your repository with a .scala-steward.conf file.
Have a fantastic day writing Scala!

Ignore future updates
Add this to your .scala-steward.conf file to ignore future updates of this dependency:
updates.ignore = [ { groupId = "org.scalameta", artifactId = "scalafmt-core" } ]

labels: library-update, early-semver-major, semver-spec-major

Superseded by #418.


text:
[TC-IDM-5.2] Chip-tool needs to provide a way to set delayed write request to verify the step 2.

Problem
As per Test-Plan, [TC-IDM-5.2] test step 2 expecting that we should set  "TH to Wait for 5 seconds and then send a Write Request Message to the DUT", But
Chip-tool needs to provide a way to set wait time for write request messages.
PFA screenshot below:-

Test-Plan Reference
https://github.com/CHIP-Specifications/chip-test-plans/blob/master/src/interactiondatamodel.adoc#tc-idm-5-2-timed-request-action-th-to-dut
Spec Reference:-
https://github.com/CHIP-Specifications/connectedhomeip-spec/blob/master/src/data_model/Interaction-Model.adoc#timed-request-action

Should be fixed by https://github.com/project-chip/connectedhomeip/pull/16135

closing, as #16135 is merged.

repo: project-chip/connectedhomeip


text:
[TE9] chip-tool segmentation fault when running TC-PS-2.2

Problem
When running TC-PS-2.2 in TE9, the chip-tool crashed with a segmentation fault when executing the command 'powersource subscribe-event-by-id'

ubuntu@ubuntu:~$ chip-tool powersource subscribe-event-by-id 0 120 3600 12344321 1
[1652385557.647020][34542:34542] CHIP:DL: ChipLinuxStorage::Init: Using KVS config file: /tmp/chip_kvs
[1652385557.647561][34542:34542] CHIP:DL: ChipLinuxStorage::Init: Using KVS config file: /tmp/chip_factory.ini
[1652385557.647722][34542:34542] CHIP:DL: ChipLinuxStorage::Init: Using KVS config file: /tmp/chip_config.ini
[1652385557.647830][34542:34542] CHIP:DL: ChipLinuxStorage::Init: Using KVS config file: /tmp/chip_counters.ini
[1652385557.648183][34542:34542] CHIP:DL: writing settings to file (/tmp/chip_counters.ini-iDi3au)
[1652385557.648865][34542:34542] CHIP:DL: renamed tmp file to file (/tmp/chip_counters.ini)
[1652385557.648925][34542:34542] CHIP:DL: NVS set: chip-counters/reboot-count = 114 (0x72)
[1652385557.649768][34542:34542] CHIP:DL: Got Ethernet interface: eth0
[1652385557.650471][34542:34542] CHIP:DL: Found the primary Ethernet interface:eth0
[1652385557.651172][34542:34542] CHIP:DL: Got WiFi interface: wlan0
[1652385557.651256][34542:34542] CHIP:DL: Failed to reset WiFi statistic counts
[1652385557.651344][34542:34542] CHIP:IN: UDP::Init bind&listen port=0
[1652385557.651488][34542:34542] CHIP:IN: UDP::Init bound to port=45219
[1652385557.651519][34542:34542] CHIP:IN: BLEBase::Init - setting/overriding transport
[1652385557.651543][34542:34542] CHIP:IN: TransportMgr initialized
[1652385557.651605][34542:34542] CHIP:DIS: Init fabric pairing table with server storage
[1652385557.651936][34542:34542] CHIP:IN: Loading from storage for fabric index 0x1
[1652385557.655385][34542:34542] CHIP:IN: Loading from storage for fabric index 0x2
[1652385557.656658][34542:34542] CHIP:IN: Loading from storage for fabric index 0x3
[1652385557.657874][34542:34542] CHIP:DIS: Add fabric pairing table delegate
[1652385557.664710][34542:34542] CHIP:ZCL: Using ZAP configuration...
[1652385557.668967][34542:34542] CHIP:DL: Avahi client registered
[1652385557.670211][34542:34542] CHIP:CTL: System State Initialized...
[1652385557.670299][34542:34542] CHIP:CTL: Setting attestation nonce to random value
[1652385557.670388][34542:34542] CHIP:CTL: Setting CSR nonce to random value
[1652385557.670515][34542:34542] CHIP:CTL: Setting attestation nonce to random value
[1652385557.670565][34542:34542] CHIP:CTL: Setting CSR nonce to random value
[1652385557.671490][34542:34542] CHIP:CTL: Generating NOC
[1652385557.672461][34542:34542] CHIP:DIS: Verifying the received credentials
[1652385557.674905][34542:34542] CHIP:DIS: Added new fabric at index: 0x1, Initialized: 1
[1652385557.674956][34542:34542] CHIP:DIS: Assigned compressed fabric ID: 0x01E738535DD7208F, node ID: 0x000000000001B669
[1652385557.674989][34542:34542] CHIP:CTL: Joined the fabric at index 1. Compressed fabric ID is: 0x01E738535DD7208F
[1652385557.675098][34542:34542] CHIP:CTL: Setting attestation nonce to random value
[1652385557.675180][34542:34542] CHIP:CTL: Setting CSR nonce to random value
[1652385557.676304][34542:34542] CHIP:CTL: Generating NOC
[1652385557.677522][34542:34542] CHIP:DIS: Verifying the received credentials
[1652385557.680101][34542:34542] CHIP:DIS: Added new fabric at index: 0x2, Initialized: 1
[1652385557.680155][34542:34542] CHIP:DIS: Assigned compressed fabric ID: 0x4D808C102B78AC41, node ID: 0x000000000001B669
[1652385557.680190][34542:34542] CHIP:CTL: Joined the fabric at index 2. Compressed fabric ID is: 0x4D808C102B78AC41
[1652385557.680293][34542:34542] CHIP:CTL: Setting attestation nonce to random value
[1652385557.680375][34542:34542] CHIP:CTL: Setting CSR nonce to random value
[1652385557.681503][34542:34542] CHIP:CTL: Generating NOC
[1652385557.682917][34542:34542] CHIP:DIS: Verifying the received credentials
[1652385557.684699][34542:34542] CHIP:DIS: Added new fabric at index: 0x3, Initialized: 1
[1652385557.684736][34542:34542] CHIP:DIS: Assigned compressed fabric ID: 0xA5A256CC44153F13, node ID: 0x000000000001B669
[1652385557.684760][34542:34542] CHIP:CTL: Joined the fabric at index 3. Compressed fabric ID is: 0xA5A256CC44153F13
[1652385557.764727][34542:34547] CHIP:DL: CHIP task running
[1652385557.764958][34542:34547] CHIP:DL: HandlePlatformSpecificBLEEvent 32787
[1652385557.764992][34542:34547] CHIP:TOO: Sending command to node 0xbc5c01
[1652385557.765027][34542:34547] CHIP:CSM: FindOrEstablishSession: PeerId = 01E738535DD7208F:0000000000BC5C01
[1652385557.765054][34542:34547] CHIP:CSM: FindOrEstablishSession: No existing OperationalDeviceProxy instance found
[1652385557.765097][34542:34547] CHIP:DIS: Resolving 01E738535DD7208F:0000000000BC5C01 ...
[1652385557.766989][34542:34547] CHIP:DL: Avahi resolve found
[1652385557.767071][34542:34547] CHIP:DIS: Node ID resolved for 01E738535DD7208F:0000000000BC5C01
[1652385557.767104][34542:34547] CHIP:DIS:     Addr 0: [2601:1c0:4701:53b0:c6ac:59ff:fe9c:6454]:5540
[1652385557.768006][34542:34547] CHIP:DIS: Lookup clearing interface for non LL address
[1652385557.768043][34542:34547] CHIP:DIS: UDP:[2601:1c0:4701:53b0:c6ac:59ff:fe9c:6454%eth0]:5540: new best score: 7
[1652385557.768067][34542:34547] CHIP:DIS: Checking node lookup status after 3 ms
[1652385557.768089][34542:34547] CHIP:DIS: Keeping DNSSD lookup active
[1652385557.965480][34542:34547] CHIP:DIS: Checking node lookup status after 201 ms
[1652385557.965599][34542:34547] CHIP:CTL: Updating device address to UDP:[2601:1c0:4701:53b0:c6ac:59ff:fe9c:6454]:5540 while in state 1
[1652385557.965656][34542:34547] CHIP:CTL: OperationalDeviceProxy[01E738535DD7208F:0000000000BC5C01]: State change 1 --> 2
[1652385557.966085][34542:34547] CHIP:SC: Initiating session on local FabricIndex 1 from 0x000000000001B669 -> 0x0000000000BC5C01
[1652385557.968220][34542:34547] CHIP:SC: Including MRP parameters
[1652385557.968989][34542:34547] CHIP:IN: Prepared unauthenticated message 0xaaaad7bced08 to 0x0000000000000000 (0)  of type 0x30 and protocolId (0, 0) on exchange 23458i with MessageCounter:2334500426.
[1652385557.969095][34542:34547] CHIP:IN: Sending unauthenticated msg 0xaaaad7bced08 with MessageCounter:2334500426 to 0x0000000000000000 at monotonic time: 000000000447263E msec
[1652385557.969518][34542:34547] CHIP:SC: Sent Sigma1 msg
[1652385557.969589][34542:34547] CHIP:CTL: OperationalDeviceProxy[01E738535DD7208F:0000000000BC5C01]: State change 2 --> 3
[1652385557.969654][34542:34547] CHIP:DIS: Discovery does not require any more timeouts
[1652385558.130409][34542:34547] CHIP:EM: Received message of type 0x33 with protocolId (0, 0) and MessageCounter:1619303474 on exchange 23458i
[1652385558.130470][34542:34547] CHIP:EM: Found matching exchange: 23458i, Delegate: 0xffff6c001180
[1652385558.130517][34542:34547] CHIP:EM: Rxd Ack; Removing MessageCounter:2334500426 from Retrans Table on exchange 23458i
[1652385558.130541][34542:34547] CHIP:EM: Removed CHIP MessageCounter:2334500426 from RetransTable on exchange 23458i
[1652385558.130580][34542:34547] CHIP:SC: Received Sigma2Resume msg
[1652385558.130677][34542:34547] CHIP:SC: Found MRP parameters in the message
[1652385558.130726][34542:34547] CHIP:SC: Peer assigned session session ID 64530
[1652385558.131898][34542:34547] CHIP:SC: Unable to delete session resumption link for node 0000000000BC5C01: ../../examples/chip-tool/config/PersistentStorage.cpp:140: CHIP Error 0x000000A0: Value not found in the persisted storage
[1652385558.153292][34542:34547] CHIP:SC: Sending status report. Protocol code 0, exchange 23458
[1652385558.153361][34542:34547] CHIP:EM: Piggybacking Ack for MessageCounter:1619303474 on exchange: 23458i
[1652385558.153408][34542:34547] CHIP:IN: Prepared unauthenticated message 0xaaaad7bced08 to 0x0000000000000000 (0)  of type 0x40 and protocolId (0, 0) on exchange 23458i with MessageCounter:2334500427.
[1652385558.153454][34542:34547] CHIP:IN: Sending unauthenticated msg 0xaaaad7bced08 with MessageCounter:2334500427 to 0x0000000000000000 at monotonic time: 00000000044726F7 msec
[1652385558.153754][34542:34547] CHIP:IN: New secure session created for device <0000000000BC5C01, 1>, LSID:3617 PSID:64530!
[1652385558.153793][34542:34547] CHIP:CTL: OperationalDeviceProxy[01E738535DD7208F:0000000000BC5C01]: State change 3 --> 4
[1652385558.153848][34542:34547] CHIP:TOO: Sending SubscribeEvent to:
[1652385558.153873][34542:34547] CHIP:TOO:      cluster 0x0000_002F, event: 0x0000_0000, endpoint 1
[1652385558.154022][34542:34547] CHIP:IN: Prepared secure message 0xaaaad7bced28 to 0x0000000000BC5C01 (1)  of type 0x3 and protocolId (0, 1) on exchange 23459i with MessageCounter:12296355.
[1652385558.154061][34542:34547] CHIP:IN: Sending encrypted msg 0xaaaad7bced28 with MessageCounter:12296355 to 0x0000000000BC5C01 (1) at monotonic time: 00000000044726F7 msec
[1652385558.154199][34542:34547] CHIP:DMG: MoveToState ReadClient[0xffff6c009040]: Moving to [AwaitingIn]
[1652385558.241571][34542:34547] CHIP:EM: Received message of type 0x10 with protocolId (0, 0) and MessageCounter:1619303475 on exchange 23458i
[1652385558.241670][34542:34547] CHIP:EM: Found matching exchange: 23458i, Delegate: (nil)
[1652385558.241774][34542:34547] CHIP:EM: Rxd Ack; Removing MessageCounter:2334500427 from Retrans Table on exchange 23458i
[1652385558.241834][34542:34547] CHIP:EM: Removed CHIP MessageCounter:2334500427 from RetransTable on exchange 23458i
[1652385558.328143][34542:34547] CHIP:EM: Received message of type 0x5 with protocolId (0, 1) and MessageCounter:1544731 on exchange 23459i
[1652385558.328222][34542:34547] CHIP:EM: Found matching exchange: 23459i, Delegate: 0xffff6c009040
[1652385558.328369][34542:34547] CHIP:EM: Rxd Ack; Removing MessageCounter:12296355 from Retrans Table on exchange 23459i
[1652385558.328410][34542:34547] CHIP:EM: Removed CHIP MessageCounter:12296355 from RetransTable on exchange 23459i
[1652385558.328498][34542:34547] CHIP:DMG: ReportDataMessage =
[1652385558.328544][34542:34547] CHIP:DMG: {
[1652385558.328611][34542:34547] CHIP:DMG:      SubscriptionId = 0x43ef536f35ea477b,
[1652385558.328654][34542:34547] CHIP:DMG:      InteractionModelRevision = 1
[1652385558.328693][34542:34547] CHIP:DMG: }
[1652385558.328771][34542:34547] CHIP:DMG: MoveToState ReadClient[0xffff6c009040]: Moving to [AwaitingSu]
[1652385558.328866][34542:34547] CHIP:EM: Piggybacking Ack for MessageCounter:1544731 on exchange: 23459i
[1652385558.328963][34542:34547] CHIP:IN: Prepared secure message 0xaaaad7bced08 to 0x0000000000BC5C01 (1)  of type 0x1 and protocolId (0, 1) on exchange 23459i with MessageCounter:12296356.
[1652385558.329041][34542:34547] CHIP:IN: Sending encrypted msg 0xaaaad7bced08 with MessageCounter:12296356 to 0x0000000000BC5C01 (1) at monotonic time: 00000000044727A6 msec
[1652385558.419550][34542:34547] CHIP:EM: Received message of type 0x4 with protocolId (0, 1) and MessageCounter:1544732 on exchange 23459i
[1652385558.419609][34542:34547] CHIP:EM: Found matching exchange: 23459i, Delegate: 0xffff6c009040
[1652385558.419656][34542:34547] CHIP:EM: Rxd Ack; Removing MessageCounter:12296356 from Retrans Table on exchange 23459i
[1652385558.419680][34542:34547] CHIP:EM: Removed CHIP MessageCounter:12296356 from RetransTable on exchange 23459i
[1652385558.419737][34542:34547] CHIP:DMG: SubscribeResponseMessage =
[1652385558.419763][34542:34547] CHIP:DMG: {
[1652385558.419787][34542:34547] CHIP:DMG:      SubscriptionId = 0x43ef536f35ea477b,
[1652385558.419813][34542:34547] CHIP:DMG:      MinIntervalFloorSeconds = 0x78,
[1652385558.419857][34542:34547] CHIP:DMG:      MaxIntervalCeilingSeconds = 0xe10,
[1652385558.419882][34542:34547] CHIP:DMG:      InteractionModelRevision = 1
[1652385558.419905][34542:34547] CHIP:DMG: }
[1652385558.419944][34542:34547] CHIP:DMG: Subscription established with SubscriptionID = 0x43EF536F35EA477B MinInterval = 120s MaxInterval = 3600s Peer = 01:0000000000BC5C01
[1652385558.419973][34542:34547] CHIP:DMG: MoveToState ReadClient[0xffff6c009040]: Moving to [Subscripti]
[1652385558.420012][34542:34547] CHIP:DMG: Refresh LivenessCheckTime for 3620000 milliseconds with SubscriptionId = 0x43EF536F35EA477B Peer = 01:0000000000BC5C01
[1652385558.420050][34542:34547] CHIP:EM: Sending Standalone Ack for MessageCounter:1544732 on exchange 23459i
[1652385558.420117][34542:34547] CHIP:IN: Prepared secure message 0xffff81125968 to 0x0000000000BC5C01 (1)  of type 0x10 and protocolId (0, 0) on exchange 23459i with MessageCounter:12296357.
[1652385558.420161][34542:34547] CHIP:IN: Sending encrypted msg 0xffff81125968 with MessageCounter:12296357 to 0x0000000000BC5C01 (1) at monotonic time: 0000000004472801 msec
[1652385558.420291][34542:34547] CHIP:EM: Flushed pending ack for MessageCounter:1544732 on exchange 23459i
[1652385587.765216][34542:34542] CHIP:CTL: Shutting down the commissioner
[1652385587.765305][34542:34542] CHIP:CTL: Shutting down the controller
[1652385587.765363][34542:34542] CHIP:CTL: Shutting down the commissioner
[1652385587.765412][34542:34542] CHIP:CTL: Shutting down the controller
[1652385587.765472][34542:34542] CHIP:IN: Expiring all connections for fabric 1!!
[1652385587.765560][34542:34542] CHIP:CTL: Shutting down the commissioner
[1652385587.765613][34542:34542] CHIP:CTL: Shutting down the controller
[1652385587.765658][34542:34542] CHIP:IN: Expiring all connections for fabric 2!!
[1652385587.765727][34542:34542] CHIP:CTL: Shutting down the commissioner
[1652385587.765775][34542:34542] CHIP:CTL: Shutting down the controller
[1652385587.765818][34542:34542] CHIP:IN: Expiring all connections for fabric 3!!
[1652385587.765878][34542:34542] CHIP:CTL: Shutting down the System State, this will teardown the CHIP Stack
[1652385587.767634][34542:34542] CHIP:DMG: IM WH moving to [Uninitialized]
[1652385587.767703][34542:34542] CHIP:DMG: IM WH moving to [Uninitialized]
[1652385587.767746][34542:34542] CHIP:DMG: IM WH moving to [Uninitialized]
[1652385587.767788][34542:34542] CHIP:DMG: IM WH moving to [Uninitialized]
[1652385587.767833][34542:34542] CHIP:DMG: All ReadHandler-s are clean, clear GlobalDirtySet
[1652385587.767959][34542:34542] CHIP:BLE: BleConnectionDelegate::CancelConnection is not implemented.
[1652385587.768403][34542:34542] CHIP:DL: writing settings to file (/tmp/chip_counters.ini-h0uiVs)
[1652385587.769574][34542:34542] CHIP:DL: renamed tmp file to file (/tmp/chip_counters.ini)
[1652385587.769697][34542:34542] CHIP:DL: NVS set: chip-counters/total-operational-hours = 0 (0x0)
[1652385587.769752][34542:34542] CHIP:DL: Inet Layer shutdown
[1652385587.769797][34542:34542] CHIP:DL: BLE shutdown
[1652385587.769843][34542:34542] CHIP:DL: System Layer shutdown
[1652385587.770065][34542:34542] CHIP:TOO: Run command failure: ../../examples/chip-tool/commands/common/CHIPCommand.cpp:392: CHIP Error 0x00000032: Timeout
Segmentation fault (core dumped)

Proposed Solution
Need investigation of the root cause of the segmentation fault.

For event subscriptions, try running chiptool in interactive mode:
chip-tool interactive start
chip-tool powersource subscribe-event-by-id 0 120 3600 12344321 1

Segfault is likely unclear shutdown due to timeout

@ifyall Is this reproducible on ToT?  I just tried running that command and I am not seeing a segfault...

@cjandhyala You added the "cert blocker" label, but is this reproducible?

assigning this to @cjandhyala - need ToT reproduction steps (since Boris tried and it did not reproduce) or closing.
@ifyall could you also add info regarding setup:

what platforms was the command run on
how often does it reproduce
if you are able to re-run it quickly enough, can you test on ToT on your environment?

We also now have a doc: https://github.com/project-chip/connectedhomeip/blob/master/docs/BUG_REPORT.md . It generally asks for platform info and also asks for logs to be provided as attachments (and only curated subsets being inline) to avoid making the issue description too large.

I do not see the Segmentation fault on the ToT.
@ifyall, I guess we can close this with a "can not reproduce" or "it just went away".

authors: andy31415 billwatersiii bzbarsky-apple ifyall isiu-apple