Difference in the way duckdb sql is using parameters in Notebook and Framework

[gcoch]

I have this notebook that calculates GDD (Growing Degree Days) for temperatures read by a sensor. The date filters work fine in the notebook.

I have used the same code in a Data App, but I cannot get the data from the Date Inputs (i..e the range) to be read by the sql statement.

Here are two different ways I've tried:

SELECT *
FROM sensorReadings
WHERE CAST(timestamp AS DATE) BETWEEN '${fechas[1]}' AND '${fechas[2]}';

and

SELECT *
FROM (
    SELECT CAST(timestamp AS DATE) AS day, *
    FROM sensorReadings
) subquery
WHERE day BETWEEN '${fechas[1]}' AND '${fechas[2]}';

In both cases, I get an error message, however when I hard code the dates it works fine.

TIA

[mbostock]

I don’t think you want the quotes around the interpolated values. The SQL code block or template literal will take care of escaping for you.

[gcoch]

I've tried without the quotes and the issue persists. I thought it was the parquet format so I changed the timestamp from datetime to epoch (in parquet). I modified the sql slightly in the notebook and it works fine there again, but tried it Framework with the same code and it still doesn't work.

[mbostock]

Let me put it this way: it’s never correct to put quotes around interpolated values. So you need to do something like this:

SELECT *
FROM sensorReadings
WHERE CAST(timestamp AS DATE) BETWEEN ${fechas[1]} AND ${fechas[2]}

If that doesn’t work, there’s some other problem, but I can’t debug it because I can’t see the rest of the code and I don’t know what errors you are seeing.

[gcoch]

Sorry for the lack of context and thanks for replying.

I found the issue. In Framework ${fechas[1]} must be cast, in order for the query to work, otherwise it throws the error. Binder Error: Cannot mix values of type DATE and VARCHAR in BETWEEN clause - an explicit cast is required

SELECT CAST(timestamp AS DATE) AS day, *
  FROM sensorReadings
   WHERE bloque = ${fechas[0].name}
   AND CAST(timestamp AS DATE) BETWEEN CAST(${fechas[1]} AS DATE) AND CAST(${fechas[2]} AS DATE)
   ORDER BY timestamp DESC;

In a notebook, however this code works:

`SELECT CAST(timestamp AS DATE) AS day,
  t AS temperature, 
  h AS humidity, bloque
  FROM sensorReadings
WHERE bloque = ${fechas[0].name}
AND day BETWEEN ${fechas[1]} AND ${fechas[2]} -- Filter by date range
ORDER BY day DESC;`

I have attached the .md file just in case.
gdd1.md

[mbostock]

You probably want fechas[1] and fechas[2] to be Date instances. Then you shouldn’t need the CAST.