2

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?

lamont
  • 428
  • 1
  • 7
  • 16

1 Answers1

4

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)
  • I'm aware of most of those alternatives already, what I really want to do is parameterize the whole expression, like I seem to be able to do when slonik isn't involved. That's not possible? – lamont Jul 26 '21 at 22:11
  • /u/a_horse_with_no_name answer is correct, but Slonik now has `sql.interval` helper. https://github.com/gajus/slonik#user-content-slonik-query-building-sql-interval – Gajus Aug 19 '22 at 17:29