prisma: Error: Provided String, expected DateTime or DateTimeFieldUpdateOperationsInput

Thanks for jumping on this @pantharshit00. Just to be clear, in my example above I was already using an ISO 8601 DateTime format - though allow me to expand a bit more around the issue.

I’ve been running a few different tests since my last message and here is what I’ve found:

• Using .toISOString() is working as expected.
• Using new Date() is working as expected.
• Using the ISO 8601 DateTime format returned by the date-fns/formatISO method isn’t working.
• Using the ISO 8601 DateTime format returned by the AWSDateTime scalar type isn’t working.

Here’s a sandbox showing the issue using date-fns/formatISO (open terminal > yarn run test): https://codesandbox.io/s/prismadatetimedatefns-l7i2u?file=/index.ts

For my use case, I’m using Prisma inside a Lambda function, in combination with AWS AppSync. Therefore, my DateTime inputs are coming through as AWSDateTime scalar type (ISO 8601). In that context, you will understand that using .toISOString() or new Date() isn’t easily applicable. Ideally, Prisma would accept the ISO 8601 returned by AWS AppSync.

Happy to be proven wrong, but I feel like Prisma isn’t supporting the ISO 8601 format entirely. But only in the exact shape returned by .toISOString()?

Am also facing this problem, the workaround that worked for me is to convert the string to date with new Date(...) or you just convert your value to .toISOString()

Should be simple to deal with native Date objects without having to deal with DateTime object and timezone constraints. Most of the ORMs support passing just a dd-mm-yyyy or yyyy-mm-dd for that, so no issues with timezones.

This issue is opened for over 3 years, so we need to understand that Prisma team might be busy with other high-priority tasks - is not easy to maintain an amazing open source library for this - so massive thanks.

For whoever is looking for alternatives - we had to mess with timezones (TZ env variable, or convert the dd-mm-yyyy to date for specific timezones and we had problems), so we have migrated all queries that deal with that to use https://github.com/drizzle-team/drizzle-orm - it has a fantastic type support and developer experience and it’s useful to deal with cases where prisma doesn’t support native stuff.

Sorry for the late reply here.

@navicstein

If you want to filter via dates, you will need to use the date native type. By default DateTime maps to timestamp(3) in the database which has millisecond precision in match and storing. If you just want to filter, use the Date native type like so(native types are in preview):

model Event {
  id              String   @id @default(uuid())
  date            DateTime @db.Date
  isOccuringFirst Boolean
  startTime       String
  endTime         String
}

Now regarding your issue @maoosi. I can confirm that as a bug in our JS validator for dates.

Here is our date handling code: https://github.com/prisma/prisma-engines/blob/9a901beeaa4d52b23483c02dc9803aadb56f0e28/libs/prisma-value/src/lib.rs#L208 . This looks alright but the JS code doesn’t seem to have a proper validator. I think our regular expression is wrong here: https://github.com/prisma/prisma/blob/557aaaddae6c826c0b01fa282d0a28df2fa1011d/src/packages/client/src/runtime/utils/common.ts#L195

Reproduction:

import { PrismaClient } from "@prisma/client";
import { formatRFC3339 } from "date-fns";

async function main() {
  const prisma = new PrismaClient();
  const _dates = await prisma.event.findMany({
    where: {
      date: formatRFC3339(new Date()),
    },
  });

  console.log(_dates);

  prisma.$disconnect();
}

main();

@maoosi @pantharshit00 Am not sure that Prisma’s date-time thingy is working as expected, let’s assume I have some data like this

// db data
[
  {
    id: 'a4a0b0aa-a62a-4c47-9ebc-324c59e9d40d',
    date: 2020-12-23T12:45:00.000Z,
    isOccuringFirst: true,
    startTime: '1:45 PM',
    endTime: '3:45 PM',
    eventId: 'd66b5060-2f21-4feb-a6f2-ce8660110346'
  },
  {
    id: '841b1847-85a3-42ae-bad5-ba05daf8e638',
    date: 2021-01-01T03:53:00.000Z,
    isOccuringFirst: true,
    startTime: '4:53 AM',
    endTime: '5:53 AM',
    eventId: 'ec7bae12-22aa-4d45-969b-e11a4899fa9e'
  },
  {
    id: '6d089ee5-43f2-4bf0-88cd-f8e8cdbfea34',
    date: 2020-12-16T10:54:00.000Z,
    isOccuringFirst: true,
    startTime: '5:54 AM',
    endTime: '11:54 AM',
    eventId: '8ed719b6-5096-4aa1-8656-b902d46687de'
  },
  {
    id: '10f28e20-7e19-45b3-8c9c-d0fdb6f64e66',
    date: 2020-12-04T07:01:03.559Z,
    isOccuringFirst: true,
    startTime: '1:00 PM',
    endTime: '4:00 PM',
    eventId: '3375cdb0-6197-4e34-8ddf-a0c2316c5c12'
  },
  {
    id: '3f3c48b6-ab50-412a-802a-6e7f30943614',
    date: 2020-12-04T07:10:02.627Z,
    isOccuringFirst: true,
    startTime: '10:24 AM',
    endTime: '6:24 AM',
    eventId: 'a5fc4601-10d1-4cd8-98db-2c969cce5127'
  }
]

Then my query is

// new Date().toISOString() =>  2020-12-04T07:07:30.514Z
  const _dates = await eventDate.findMany({
    where: {
      date: new Date().toISOString(),
    },
  })

_date should at least match the last two objects in the above result since is still in the range of 2020-12-04 but instead is empty [] but… if I omit .toISOString(), the entire results will be fetched which is not desired, changing the Prisma type from DateTime to String and using the query

...
    where: {
      date: {
        contains: new Date().toISOString(),
      },
...

seems to solve this problem, but why’s Prisma looking up DateTime in strict mode? at least

Thanks a lot for reporting 🙏 This issue is fixed in the latest dev version of @prisma/cli. You can try it out with npm i -g @prisma/cli@dev.

In case it’s not fixed for you - please let us know and we’ll reopen this issue!

Hey @maoosi

This is intended. Prisma will only accept DateTime in ISO 8061 format(https://en.wikipedia.org/wiki/ISO_8601).

If you want use another format, you can should parse it and convert it into a javascript Date object as suggested @navicstein. We will then internally call toISOString() on that Date object and pass it to the database.

Awesome, thanks!