Parameterized Interval in Where Clause

I'm trying to write a query that uses an interval as a parameter. The SQL looks like this and works fine:

where now() - interval '1 month' < table.date

where now() - interval '1 month' < table.date

When I try to write it using drizzle, it also works fine if a hard code the interval:

lt(
    sql<Date>`now() - interval '1 month'`,
    sql<Date>`table.date`
)

lt(
    sql<Date>`now() - interval '1 month'`,
    sql<Date>`table.date`
)

However, it does not work if I try to parameterize the interval like this:

lt(
    sql<Date>`now() - interval '${interval}'`,
    sql<Date>`table.date`
)

lt(
    sql<Date>`now() - interval '${interval}'`,
    sql<Date>`table.date`
)

or this:

sql<boolean>`now() - interval '${interval}' < table.date`,

sql<boolean>`now() - interval '${interval}' < table.date`,

I get this error

Error: could not determine data type of parameter $3

I tried this

lt(
    sql<Date>`now() - ${interval}`,
    sql<Date>`table.date`
)

lt(
    sql<Date>`now() - ${interval}`,
    sql<Date>`table.date`
)

and got this error

operator does not exist: interval < timestamp with time zone

Since the raw SQL works, I must be missing something in the ORM usage. Any suggestions? I'm running Postgres 16 on neon using drizzle 0.30.7.

3 Replies

Hi dude, did you manage to get this working?

san4dOP•2mo ago

Nope. I had to use the raw SQL.