How to pass a parameterized INTERVAL in a slonik/postgres query?

user_803297 0
Viewed 203

This query seems to be legal when I run it in Datagrip with a parameter value of '14 days' SELECT * FROM users WHERE users.updated_at < (CURRENT_DATE - INTERVAL $1)

But trying to do something similar in slonik as below does not:

const interval='14 days'
// ...
const {rows} = await pool.query<any>(sql`
SELECT * FROM users WHERE users.updated_at < (CURRENT_DATE - INTERVAL ${interval}))
`)

Seems to yield the same query: "SELECT * FROM users WHERE updated_at < CURRENT_TIME - INTERVAL $1)" but the pool's query method throws a syntax error near "$1" error when I try to execute it.

I've tried a couple of variations including escaping the input const interval="'14 days'" and adding parens for the INTERVAL function (CURRENT_DATE - INTERVAL(${interval})) with the same error results.

Is it not possible to parameterize a slonik query this way or am I doing something stupid?

slonik node-postgres postgresql
1 Answers

You can subtract an integer representing the number of days from CURRENT_DATE because that is a date value not a timestamp

SELECT * FROM users WHERE updated_at < CURRENT_DATE - $1

Then pass $1 as an integer

Another option is to multiply an interval of a specific length with the parameter:

SELECT * FROM users WHERE updated_at < CURRENT_DATE - (interval '1 day' * $1)

or use the make_interval() function:

SELECT * FROM users WHERE updated_at < CURRENT_DATE - make_interval(days => $1)
user_3303150
Related