Trying to calculate last 30 days from a date using variable in SQL

Hi I am trying to do a LAST N Period Calucation from a defined date in SQL query interface

Here is my query

SELECT field_one FROM table_one WHEREtime_field >= {{specified_date}} - INTERVAL {{N period}}

here the preview query is generating the correct sql for the values:
specified_date = 2023-12-30 (date type)
N Period = 30 days (TEXT type)

preview of query looks like :
SELECT field_one FROM table_one WHERE time_field >= DATE '2023-12-30' - INTERVAL '30 days'

This query is working on postgres but metabase is not able to run it. it gives the following error:

Please help

That's very strange. I created something similar and found the same results.
What's oddest is that if you use the 'Query preview', it show SQL that will run.

1 Like

Exactly the SQL runs when copied from 'Query Preview'
Don't know what to do here :frowning:

I have tried using concat_ws() to generate the period string by having 2 variables , such that '30' comes from number type variable and 'days' comes from text type variable, still no success.

Also Which category should I add this issue into, so that it gets looked into by some one from metabase

Got it!
The problem is Postgres being awkward. It never does an implicit cast.


That forces the varchar to be cast to an interval.


Thanks @AndrewMBaines
It worked like charm :+1:

Thanks for your time and effort :smiley: