DT
Drizzle Team•2w ago
Uncle

Date and lte

I'm adding Drizzle to my Sveltekit / Prisma stack and I can't figure out how to query by lte() date. I used the prisma extention to convert my prisma schema to drizzle and I got this for my invoices 
export const Invoice = pgTable('invoice', {
    invoiceNum: serial('invoiceNum').notNull().primaryKey(),
    customerId: text('customerId').notNull(),
    employeeId: text('employeeId'),
    leaseId: text('leaseId'),
    invoiceAmount: doublePrecision('invoice_amount').notNull(),
    invoiceCreated: timestamp('invoiceCreated', { precision: 3 }).notNull().defaultNow(),
    invoiceDue: timestamp('invoice_due', { precision: 3 }).notNull(),
    invoiceNotes: text('invoiceNotes'),
    deposit: boolean('deposit').notNull(),
    amountPaid: doublePrecision('amount_paid').notNull()
}, (Invoice) => ({
    'invoice_customer_fkey': foreignKey({
        name: 'invoice_customer_fkey',
        columns: [Invoice.customerId],
        foreignColumns: [User.id]
    })
        .onDelete('cascade')
        .onUpdate('cascade'),
    'invoice_employee_fkey': foreignKey({
        name: 'invoice_employee_fkey',
        columns: [Invoice.employeeId],
        foreignColumns: [User.id]
    })
        .onDelete('cascade')
        .onUpdate('cascade'),
    'invoice_lease_fkey': foreignKey({
        name: 'invoice_lease_fkey',
        columns: [Invoice.leaseId],
        foreignColumns: [Lease.leaseId]
    })
        .onDelete('cascade')
        .onUpdate('cascade')
}));
export const Invoice = pgTable('invoice', {
    invoiceNum: serial('invoiceNum').notNull().primaryKey(),
    customerId: text('customerId').notNull(),
    employeeId: text('employeeId'),
    leaseId: text('leaseId'),
    invoiceAmount: doublePrecision('invoice_amount').notNull(),
    invoiceCreated: timestamp('invoiceCreated', { precision: 3 }).notNull().defaultNow(),
    invoiceDue: timestamp('invoice_due', { precision: 3 }).notNull(),
    invoiceNotes: text('invoiceNotes'),
    deposit: boolean('deposit').notNull(),
    amountPaid: doublePrecision('amount_paid').notNull()
}, (Invoice) => ({
    'invoice_customer_fkey': foreignKey({
        name: 'invoice_customer_fkey',
        columns: [Invoice.customerId],
        foreignColumns: [User.id]
    })
        .onDelete('cascade')
        .onUpdate('cascade'),
    'invoice_employee_fkey': foreignKey({
        name: 'invoice_employee_fkey',
        columns: [Invoice.employeeId],
        foreignColumns: [User.id]
    })
        .onDelete('cascade')
        .onUpdate('cascade'),
    'invoice_lease_fkey': foreignKey({
        name: 'invoice_lease_fkey',
        columns: [Invoice.leaseId],
        foreignColumns: [Lease.leaseId]
    })
        .onDelete('cascade')
        .onUpdate('cascade')
}));
and I would like to querry like this: 
   const invoices = prisma.$drizzle.select()
      .from(invoice)
      .where(and(lt(invoice.amountPaid, invoice.invoiceAmount), lte(invoice.invoiceDue, new Date())))
      .orderBy(invoice.invoiceNum);
   const invoices = prisma.$drizzle.select()
      .from(invoice)
      .where(and(lt(invoice.amountPaid, invoice.invoiceAmount), lte(invoice.invoiceDue, new Date())))
      .orderBy(invoice.invoiceNum);
which doesn't give me any typescript errors but does give me this error 'ERROR: operator does not exist: timestamp without time zone <= text\n' how do I properly search by date?
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?