I'm running across a funny issue using variables specifically inside the DATEADD function when querying redshift. A minimal example:
SELECT DATEADD({{period}}, 1, created_at) AS event_date
FROM my_table
LIMIT 1
Running this query using Metabase v0.35.3 against my Redshift cluster with {{period}} set up as text field assigned the value 'month' yields the error:
[Amazon] (500310) Invalid operation: syntax error at or near "$1" Position: 135;
However, for other date functions, it works fine, e.g.:
SELECT DATE_TRUNC({{period}}, created_at) AS event_date
FROM my_table
LIMIT 1
Hi @dhaas
Perhaps Redshift is more strict in that function compared to the other. Metabase is type casting the parameters to prevent injections.
Try this instead:
SELECT
CASE
WHEN {{period}}='day' THEN DATEADD('day', 1, created_at)
WHEN {{period}}='week' THEN DATEADD('week', 1, created_at)
WHEN {{period}}='month' THEN DATEADD('month', 1, created_at)
END AS event_date
FROM my_table
LIMIT 1
Sorry for the year-late response! Just returned to this work, and for posterity, the workaround you posted above using the CASE statement works great. My issues with it were due to other bugs in my query.