I have a dashboard with 3 SQL questions on Postgres which take a time range like:
WHERE {{created_at}}
The variable is a required Date Range Field filter and I need a default value relative to today - like LAST TWO calendar months. I tried to achieve that with the
[[ WHERE {{created_at}} --]] date_trunc(date(now()), month)
But I since this is a time range it doesn’t work. Is there a functionality I’m missing? Or a workaround? I’d be happy to select start and end date for the time range, but that would be applied using BETWEEN on the same column, so it won’t work for me to set the dates on the dashboard…
Hi @nikolac
Is the date range always relative to today? Then you could change it to simple Date instead of Field Filter and make the query like this:
WHERE "created_at" BETWEEN date_trunc('month', coalesce([[{{created_at}}::date,]] current_date - '1 month'::interval)) AND current_date
There seems to be an issue with “complex default value” (that’s the --) because it puts 1=1 when there’s no value instead of omitting the optional clause.
So something like this currently (0.32.8) doesn’t work:
WHERE [[{{created_at}} --]] "created_at" BETWEEN date_trunc('month', current_date - '1 month'::interval) AND current_date
I could make this work for my case - I had to change the dashboard so that it has a {{start_date}} Date and {{end_date}} Date instead of a Field Filter time range. Thanks for the syntax using coalesce([[{{created_at}}::date, ]]