SQL query using INTERVAL won't take variable

(problem has been resolved, see answer below)

I'm trying to run a query that uses the INTERVAL syntax with the value from a select component used as a variable in the query, but get different errors depending on my implementation.

The error seems to stem from the fact that INTERVAL format is picky about how you give it the format and the templating language is quoting my string values slightly off.

I initially used this query:

  DATE("publishedAt") AS date,
  COUNT(*)::INTEGER AS count
  DATE("publishedAt") > NOW() - INTERVAL {{new_videos_days.value}}
  date ASC;

My select dropdown assigns a value of 7 days (a string):

This results in the query using "7 days" (note the double quotes):

...and the following error:

"syntax error at or near "$1""

This is a problem since this generated SQL that contains DATE("publishedAt") > NOW() - INTERVAL "31 day" (with double quotes), which is not valid SQL. I need it to be DATE("publishedAt") > NOW() - INTERVAL '31 day' (single quotes).

If I try an alternate way to inject the value...

  DATE("publishedAt") > NOW() - INTERVAL '{{new_videos_days.value}} days'

...and use a drop-down value of just 7, which makes it list it as an number...

...but I get a new error...

"bind message supplies 1 parameters, but prepared statement "" requires 0"

...despite it using just 7 without any quotes (presumably used as an integer):

Am I quoting the variable wrong? Is there some kind of explicit casting I could do to solve this issue?

Is there a way to get the full SQL that a query tried to use BTW, rather than the 'SQL Prepared Statement' format shown above?

I'm using PostgreSQL, but not sure if this is specific to that DB type. A similar problem seems to have been noted before, but the proposed solution doesn't seem to address the issue of the SQL that needs to consume the select component's value.

FWIW, even using DATE("publishedAt") > NOW() - {{new_videos_days.value}} and a value of 'INTERVAL 7 days' with or without the single quotes doesn't work. That seems to strip out the single quotes and the resulting DATE("publishedAt") > NOW() - INTERVAL 31 days isn't valid.

Answering my own question, the tricks seems to be to caste it with ::INTERVAL in the query:

DATE("publishedAt") > NOW() - {{new_videos_days.value}}::INTERVAL

...and then use a value of 7 days e.g..

1 Like