Inconsistent column data: Unexpected conversion failure from String to datetime. Reason: $trailing input

[rajdtta]

Bug description

I updated Prisma from v5.6 to v5.7 (to test out the experimental join feature). When the feature was enabled, my application started erroring with the following message:

Invalid `prisma.userProfile.findUniqueOrThrow()` invocation:

Inconsistent column data: Unexpected conversion failure from String(2023-12-06T17:43:39.515686+00:00) to DateTime. Reason: $trailing input
    at async UserDBO.getUserByID (./app/api/user/source.ts:67:16)
    at async UserService.getSessionUser (./app/api/user/service.ts:58:16)
    at async DashboardPage (dashboard/page.tsx:52:25)
digest: "3975094782"

How to reproduce

All I did was upgrade from Prisma v5.6 to v5.7. If I can figure out how to replicate the issue consistently from a sandbox, I'll follow up with an instruction set.

Expected behavior

I would expect these queries to execute w/o any issues given that they worked completely fine in the past.

Prisma information

model UserProfile {
  userId           String             @id @db.Uuid
  avatar_url       String?
  companyId        String             @db.Uuid
  createdAt        DateTime           @default(now())
  updatedAt        DateTime           @updatedAt
  companyRole      COMPANY_ROLE       @default(USER)
  role             USER_ROLE          @default(USER)
  firstName        String
  lastName         String
  phone            String
  dashboards       Dashboard[]
  DashboardPresets DashboardPresets[]
  company          Company            @relation(fields: [companyId], references: [id], onDelete: Cascade)
  user             users              @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@schema("platform_db")
}

model Company {
  id               String                 @id @default(uuid()) @db.Uuid
  name             String
  createdAt        DateTime               @default(now())
  updatedAt        DateTime               @updatedAt
  deletedAt        DateTime?
  ownerId          String                 @unique @db.Uuid
  stripeCustomerId String?
  stripeTestId     String?
  owner            users                  @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  subscriptions    CompanySubscriptions[]
  DashboardPresets DashboardPresets[]
  User             UserProfile[]

  @@schema("platform_db")
}


model users { // Supabase's user table
  instance_id                 String?       @db.Uuid
  id                          String        @id @db.Uuid
  aud                         String?       @db.VarChar(255)
  role                        String?       @db.VarChar(255)
  email                       String?       @db.VarChar(255)
  encrypted_password          String?       @db.VarChar(255)
  email_confirmed_at          DateTime?     @db.Timestamptz(6)
  invited_at                  DateTime?     @db.Timestamptz(6)
  confirmation_token          String?       @db.VarChar(255)
  confirmation_sent_at        DateTime?     @db.Timestamptz(6)
  recovery_token              String?       @db.VarChar(255)
  recovery_sent_at            DateTime?     @db.Timestamptz(6)
  email_change_token_new      String?       @db.VarChar(255)
  email_change                String?       @db.VarChar(255)
  email_change_sent_at        DateTime?     @db.Timestamptz(6)
  last_sign_in_at             DateTime?     @db.Timestamptz(6)
  raw_app_meta_data           Json?
  raw_user_meta_data          Json?
  is_super_admin              Boolean?
  created_at                  DateTime?     @db.Timestamptz(6)
  updated_at                  DateTime?     @db.Timestamptz(6)
  phone                       String?       @unique
  phone_confirmed_at          DateTime?     @db.Timestamptz(6)
  phone_change                String?       @default("")
  phone_change_token          String?       @default("") @db.VarChar(255)
  phone_change_sent_at        DateTime?     @db.Timestamptz(6)
  confirmed_at                DateTime?     @default(dbgenerated("LEAST(email_confirmed_at, phone_confirmed_at)")) @db.Timestamptz(6)
  email_change_token_current  String?       @default("") @db.VarChar(255)
  email_change_confirm_status Int?          @default(0) @db.SmallInt
  banned_until                DateTime?     @db.Timestamptz(6)
  reauthentication_token      String?       @default("") @db.VarChar(255)
  reauthentication_sent_at    DateTime?     @db.Timestamptz(6)
  is_sso_user                 Boolean       @default(false)
  deleted_at                  DateTime?     @db.Timestamptz(6)
  identities                  identities[]
  mfa_factors                 mfa_factors[]
  sessions                    sessions[]
  Company                     Company?
  UserProfile                 UserProfile?

  @@index([instance_id])
  @@schema("auth")
}

await prisma.userProfile.findUniqueOrThrow({
      where: {
        userId: userID,
      },
      select: {
        userId: true,
        avatar_url: true,
        firstName: true,
        lastName: true,
        phone: true,
        role: true,
        companyRole: true,
        company: {
          select: {
            name: true,
            id: true,
            ownerId: true,
          },
        },
        user: {
          select: {
            email: true,
            last_sign_in_at: true,
            confirmed_at: true,
            created_at: true,
          },
        },
      },
    });

Environment & setup

• OS: macOS 14.1
• Database: PostgreSQL (using Prisma Accelerate for pooling)
• Node.js version: v21.2.0

Prisma Version

prisma                  : 5.7.0
@prisma/client          : 5.7.0
Computed binaryTarget   : darwin-arm64
Operating System        : darwin
Architecture            : arm64
Node.js                 : v21.2.0
Query Engine (Node-API) : libquery-engine 79fb5193cf0a8fdbef536e4b4a159cad677ab1b9 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli 79fb5193cf0a8fdbef536e4b4a159cad677ab1b9 (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.7.0-41.79fb5193cf0a8fdbef536e4b4a159cad677ab1b9
Default Engines Hash    : 79fb5193cf0a8fdbef536e4b4a159cad677ab1b9
Studio                  : 0.495.0
Preview Features        : fullTextSearch, multiSchema, postgresqlExtensions

[Weakky]

Hey @rajdtta,

Thanks a lot for the report 🙏! Could you confirm whether this error also happens when you are not using Prisma Accelerate?

[jkepps]

I am also seeing a very similar error, but only when i enable the relationJoins preview and I am not using Accelerate

Inconsistent column data: Unexpected conversion failure from String(1972-02-01) to DateTime. Reason: $input contains invalid characters

[Weakky]

Hey @jkepps,

Thanks for chiming in. Would you mind sharing part of your datamodel? I can reproduce your bug, but I'm assuming you're using a native type (with the field Datetime @db.<SomeNativeType> notation and I'd like to know which one you are using.

To be clear, only the datetime fields of your datamodel are relevant.

Thanks!

[jkepps]

@Weakky yeah definitely. i'm pretty sure this is the relevant model, and the field at issue is date

model Checkin {
  id                    Int       @id @default(autoincrement())
  createdAt             DateTime  @default(now()) @db.Timestamp(6)
  updatedAt             DateTime  @default(now()) @updatedAt @db.Timestamp(6)
  date                  DateTime  @db.Date
  routineId             Int
  memberId              Int
  autocheckin           Boolean   @default(false)
  notPresentWarningSeen Boolean   @default(false)
  checkInAt             DateTime? @db.Timestamp(6)
  checkOutAt            DateTime? @db.Timestamp(6)
  timeWarningSeen       Boolean   @default(false)

  routine         Routine          @relation(fields: [routineId], references: [id], onDelete: Cascade)
  member          Member           @relation(fields: [memberId], references: [id], onDelete: Cascade)
  checkinLocation CheckinLocation?

  @@unique([date, routineId], name: "checkinDateRoutine", map: "checkinDateRoutine")
  @@index([memberId])
  @@index([routineId])
  @@index([date], map: "IDX_ec5713aa719bae2482befb992b")
  @@map("checkins")
}

[jkepps]

there are also these two related data models. i noticed that the contracts load fine on their own, but if i try to access a contract through a treatment provider i get the unexpected conversion error on the date fields

model Contract {
  id                    Int           @id @default(autoincrement())
  name                  String        @unique
  startingBalance       Int
  durationOfRewards     Int?
  durationOfMessaging   Int?
  createdAt             DateTime      @default(now()) @db.Timestamp(6)
  updatedAt             DateTime      @default(now()) @updatedAt @db.Timestamp(6)
  planYearEarningsLimit Int?
  planYearStartsAt      DateTime?     @db.Date
  requiresReferral      Boolean       @default(false)
  endsAt                DateTime?     @db.Timestamp(6)
  rewardsPlanId         Int
  tangoBrandKeys        String[]
  test                  Boolean       @default(false)
  type                  ContractType?
  eligibleMemberCount   Int?
  enrollmentStartsAt    DateTime?     @db.Date
  enrollmentEndsAt      DateTime?     @db.Date

  memberBenefits        MemberBenefits[]
  healthPlans           HealthPlan[]
  rewardsPlan           RewardsPlan             @relation(fields: [rewardsPlanId], references: [id])
  rewardsCodes          RewardsCode[]
  treatmentProviders    TreatmentProvider[]
  eligibilityFiles      EligibilityFile[]
  MemberBenefitsArchive MemberBenefitsArchive[]

  @@index([rewardsPlanId])
  @@map("contracts")
}


model TreatmentProvider {
  id         Int      @id @default(autoincrement())
  name       String   @unique(map: "treatment_providers.name_unique") @db.VarChar
  contractId Int
  createdAt  DateTime @default(now()) @db.Timestamptz(6)
  updatedAt  DateTime @default(now()) @updatedAt @db.Timestamptz(6)

  members      Member[]      @relation
  contract     Contract      @relation(fields: [contractId], references: [id], onDelete: Cascade)
  rewardsCodes RewardsCode[]

  @@map("treatment_providers")
}

[Zenoo]

Same issue here, I'm guessing it happens when using a @db.Timestamptz() in my case

[semoal]

Same here:

→ 131 const task = await prisma.task.findFirstOrThrow(
Inconsistent column data: Unexpected conversion failure from String(2023-12-07T09:18:30.111+00:00) to DateTime. Reason: $trailing input
 ❯ si.handleRequestError ../../packages/prisma-client/client/runtime/library.js:125:6817
 ❯ si.handleAndLogRequestError ../../packages/prisma-client/client/runtime/library.js:125:6151
 ❯ si.request ../../packages/prisma-client/client/runtime/library.js:125:5859
 ❯ l ../../packages/prisma-client/client/runtime/library.js:130:10025
 ❯ ../../packages/prisma-client/client/runtime/library.js:34:550
 ❯ src/controllers/v1/tasks/labels.controller.test.ts:131:20
    129|         userId: user.id,
    130|       });
    131|       const task = await prisma.task.findFirstOrThrow({
       |                    ^
    132|         where: { id: tasks[0].id },
    133|         include: {

⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
Serialized Error: { code: 'P2023', clientVersion: '5.7.0', meta: { modelName: 'Task', message: 'Unexpected conversion failure from String(2023-12-07T09:18:30.111+00:00) to DateTime. Reason: $trailing input' }, batchRequestIdx: undefined }
⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯[2/2]⎯

 Test Files  1 failed (1)
      Tests  2 failed | 4 passed (6)
   Start at  09:18:29
   Duration  932ms (transform 176ms, setup 0ms, collect 558ms, tests 220ms, environment 0ms, prepare 52ms)

Our task model:

model Task {
  ...
  createdAt           DateTime             @default(now()) @db.Timestamptz(3)
  updatedAt           DateTime             @updatedAt @db.Timestamptz(3)
  availableAt         DateTime?            @db.Timestamptz(3)
  availableUntil      DateTime?            @db.Timestamptz(3)
  ...
}

[janpio]

@Zenoo @semoal I assume also only when relationJoins preview feature enabled?

[Zenoo]

@janpio Yes.

[blaueeiner]

Can confirm this issue as well when relationJoins preview feature is enabled.

[capaj]

removing the feature flag indeed fixes the issue.

[rajdtta]

Hey @rajdtta,

Thanks a lot for the report 🙏! Could you confirm whether this error also happens when you are not using Prisma Accelerate?

Apologies for the late reply, but can confirm that the error occurs regardless of Accelerate being used. As the others have figured out by now, it seems to be the usage of @db.Timestamptz() that causes the error to occur (when relationJoins is enabled).

[jkepps]

in my case it is the @db.Date (in addition to @db.Timestamptz() stated above) that's causing an issue

[janpio]

Thanks for the confirmations, super helpful. This definitely is the biggest and most popular thing we missed. Working on investigation and fix.

@rajdtta Thanks for updating your message above as well, we were scared it leaked out of the preview feature - but we know that relationJoins is required to trigger this problem.

[rajdtta]

@rajdtta Thanks for updating your message above as well, we were scared it leaked out of the preview feature - but we know that relationJoins is required to trigger this problem.

For sure, sorry for the mix-up w/ the initial report. I had forgotten to run prisma generate after removing the flag during my initial testing 😅

[Weakky]

Hey folks,

So this is an oversight on my end for not handling native types. Most of them are going to fail atm: DateTime native types (Timestamp, Timestamptz, Time, TimeTz, etc..), but Decimals too (such as Money) and possibly others (String, Bytes, Float native types etc). I'm currently working on a fix. It's not clear yet whether we will issue a patch or not, since this is a preview feature and it doesn't affect the "stable" product (as updated by @rajdtta).

Either way, be sure it'll be available latest for the next release. If we release a patch, I'll make sure to let you know here. Meanwhile, I advise you to not use the preview feature just yet if you use native types. Don't get discouraged though, your reports are very useful for us to get joins on part with the old query-based mechanism.

Thank you all 🙏

[Weakky]

Hey, as promised, we are releasing a 5.7.1 patch for this bug. Make sure to try it out as soon as it's out to confirm it's fixed!

Thanks for the report again 🙏

[yovanoc]

Still appears with the neon serverless adapter

[janpio]

Can you please open a new issue and optimally provide a reproduction @yovanoc? It Probably has a different root cause, but we will certainly want to fix that. Thanks.