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

Bug reports
1

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:

image
image1442×564 22.6 KB

Please help
thanks

2

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
3

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

4

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

Try:
{{period}}::INTERVAL

That forces the varchar to be cast to an interval.

2 Likes
5

Thanks @AndrewMBaines
It worked like charm :+1:

Thanks for your time and effort :smiley: