Using Variables in Interval

Get Help
1

Hey all,

I want to do something along the lines of:

table."registrationCreatedAt" >= now() - interval 1 {{period}}

where the variable can be day, week or month (to compare week-on-week changes or other period changes). The error I get is:

ERROR: syntax error at or near "$1" Position: 905

How can I overcome this? Thanks in advance

2

It is not possible to use placeholders for the period as the period itself is not a varchar parameter but a keyword. A workaround can be to use a case expression in your condition, something like this:

table."registrationCreatedAt" >= now() - 
  case 
     when {{ period }} = 'day' then interval 1 day
     when {{ period }} = 'week' then interval 1 week
     when {{ period }} = 'month' then interval 1 month
     when {{ period }} = 'year' then interval 1 year
     else null
   end
3

This is great! Thank you :slight_smile: