Currently, I am working on a webpage using Next.js to manage inventory tracking. To connect to my MySQL database, I rely on Prisma. One of the key features I want is the ability to create a new row in the database whenever there's a change in the inventory. This new entry should include the date and time when the change occurred. However, the issue I'm facing is that the generated date follows UTC+00:00, but what I actually need is for it to be based on UTC-06:00.
Here is the schema model representation in Prisma:
model inventory_movements {
moveNumber Int @id @default(autoincrement())
name String @db.VarChar(128)
date DateTime? @default(now()) @db.DateTime(0)
amount Decimal @db.Decimal(11, 2)
comment String? @db.VarChar(128)
}
My current approach involves using Moment.js to generate the date and then adjusting the timezone as required:
import moment from 'moment';
import 'moment-timezone';
prisma.inventory.create({
data: {
name: "product_name",
amount: 1,
comment: "comment on the change",
date: moment().tz('America/Monterrey').format()
}
});
The date output I get is ''2022-12-11T00:21:29-06:00'', whereas the database stores a different value ''2022-12-11 06:21:28''.
I attempted an alternative method where the database handles the date generation automatically with a default value set as NOW(), which worked correctly:
Therefore, I removed the date field from the Prisma command like so:
import moment from 'moment';
import 'moment-timezone';
prisma.inventory_movements.create({
data: {
name: "product_name",
amount: 1,
comment: "comment on the change",
}
});
Unfortunately, this resulted in an error:
error - unhandledRejection: PrismaClientValidationError:
Invalid `prisma.inventory_movements.create()` invocation:
{
data: {
name: 'product_name',
amount: '1',
comment: 'comment on the change',
+ date: DateTime
}
}
Note: Lines with + are required