Text variable in DATEADD function on Redshift

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

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

Is this a bug or am I just being silly somehow?

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:

  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

You are asking for this - upvote by clicking :+1: on the first post of each issue:
https://github.com/metabase/metabase/issues/9875 and https://github.com/metabase/metabase/issues/6583

Thank you! That workaround also led to strange errors for some reason, but I found another approach that worked.

Also thanks for the issue links, gave both a :+1: as they’d definitely solve a common use case for us.

@dhaas Please post the errors you saw and your alternative approach, so others can learn from that.

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.