How to get all records, where the date value in the record is older then an hour with Prisma DB and-CodePudding

This is what I am doing right now:

const addHours = function (date: Date, hours: number) {
  date.setTime(date.getTime()   (hours * 60 * 60 * 1000))
  return date;
}
let expiredReservation = await prisma.collectionIndexes.findMany({
    where: {
        AND: [
            {
                collectionId: dbCollection.id
            },
            {
                submitedTx: null
            }
        ]
    },
    orderBy: {
        reservedAt: 'asc'
    }
})
expiredReservation = expiredReservation.filter(r => addHours(r.reservedAt, 1).getTime() < new Date().getTime())

These are my models:

model CollectionIndexes {
  id            Int        @id @unique @default(autoincrement())
  submitedTx    String?

  collection    Collection @relation(fields: [collectionId], references: [id])
  collectionId  Int

  reservedIndex Int
  reservedAt    DateTime   @default(now())

}

model Collection {
  id                Int                 @id @unique @default(autoincrement())
  collectionName    String              @unique
  collectionLimit   Int?
  CollectionIndexes CollectionIndexes[]
}

I would much rather just get the correct records from prisma db, without doing filter after. If it's possible, how to write it?

CodePudding user response：

Could you try something like below

const subtractHour = (date, hour) => {
  date.setHours(date.getHours() - hour);
  return date;
}

let expiredReservation = await prisma.collectionIndexes.findMany({
    where: {
        AND: [
            {
                collectionId: dbCollection.id
            },
            {
                submitedTx: null
            },
            {
                reservedAt: {
                  lte: subtractHour(new Date(), 1)
                }
            }
        ]
    },
    orderBy: {
        reservedAt: 'asc'
    }
})