I'm using Prisma with a MySQL database. This is my code:

  const thisYear = Number("2023")
  const newYear = thisYear + 1

  const startDate = format(new Date(thisYear, 6, 1), 'yyyy-MM-dd HH:mm:ss')
  const endDate = format(new Date(newYear, 5, 30), 'yyyy-MM-dd HH:mm:ss')
  console.log("startDate, endDate: ", startDate, endDate)

  const bucketTransactions = await prisma.bucket_transaction.groupBy({
       by: ['bucket_id'],
       _sum: {
           amount: true,
       },
       where: {
           date_added: {
               gte: startDate,
               lte: endDate,
           },
           amount: {
               gt: 0,
           },
           donation_id: {
               gt: 0,
           },
       },
   });

I get this error:

Invalid value for argument `gte`: input contains invalid characters. Expected ISO-8601 DateTime.

This is the value of startDate and endDate in the console log:

startDate, endDate:  2023-07-01 00:00:00 2024-06-30 00:00:00

Do you know what's wrong with my code?

I ran this directly into the SQL terminal and it worked:

select * from bucket_transaction WHERE date_added BETWEEN "2023-07-01 00:00:00" and "2024-06-30 00:00:00" AND amount > 0 AND donation_id > 0;

Why doesn't it work with Prisma?

I'm using Prisma with a MySQL database. This is my code:

  const thisYear = Number("2023")
  const newYear = thisYear + 1

  const startDate = format(new Date(thisYear, 6, 1), 'yyyy-MM-dd HH:mm:ss')
  const endDate = format(new Date(newYear, 5, 30), 'yyyy-MM-dd HH:mm:ss')
  console.log("startDate, endDate: ", startDate, endDate)

  const bucketTransactions = await prisma.bucket_transaction.groupBy({
       by: ['bucket_id'],
       _sum: {
           amount: true,
       },
       where: {
           date_added: {
               gte: startDate,
               lte: endDate,
           },
           amount: {
               gt: 0,
           },
           donation_id: {
               gt: 0,
           },
       },
   });

I get this error:

Invalid value for argument `gte`: input contains invalid characters. Expected ISO-8601 DateTime.

This is the value of startDate and endDate in the console log:

startDate, endDate:  2023-07-01 00:00:00 2024-06-30 00:00:00

Do you know what's wrong with my code?

I ran this directly into the SQL terminal and it worked:

select * from bucket_transaction WHERE date_added BETWEEN "2023-07-01 00:00:00" and "2024-06-30 00:00:00" AND amount > 0 AND donation_id > 0;

Why doesn't it work with Prisma?

• javascript
• sql
• prisma

Share Improve this question Follow edited Aug 1, 2023 at 19:04 Shadow 34.3k1010 gold badges6565 silver badges7575 bronze badges asked Aug 1, 2023 at 16:17 Page COWPage COW 76533 gold badges1818 silver badges4141 bronze badges 1

• Doesn't ISO-8601 require a T in the timestamp nowadays? Like '2023-07-01T00:00:00'. – jarlh Commented Aug 1, 2023 at 17:53

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 3

You can change

const startDate = format(new Date(thisYear, 6, 1), 'yyyy-MM-dd HH:mm:ss')

to

const startDate = new Date(thisYear, 6, 1)

or

const startDate = new Date(thisYear, 6, 1).toISOString()

Prisma work with the date directly or with the .toISOString()

I had to change the startDate and the endDate into a new Date object like this:

       where: {
           date_added: {
               gte: new Date(startDate),
               lte: new Date(endDate),
           }
       },