Setting "Complex Default Value" for a Variable inside of DATE_TRUNC function

[DCamps]

Hello,

Running Postrgres. I'm trying to use the generate_series function to create a column of dates as such:

SELECT
        generate_series(date_trunc('month', now() - interval '100 years'), date_trunc('month', now()), interval '1 month') AS date

Which generates the following result:

However, I'm trying to make it such that the "start_date" inside of the query is filterable as such:

SELECT
    generate_series(date_trunc('month', {{start_date}}), date_trunc('month', now()), interval '1 month') AS date

If I create a date filter and set a value to it it works:

However, if I remove the value from the date filter it returns an error saying the value is required:

Is it possible to add a complex default value for this variable inside of the first date_trunc function nested inside of the generate_series function? Similar to how you would do it in a where clause:

WHERE 
    start_date = [[{{start_date}} --]]('2000-01-01'::date)

Or alternatively, making the variable optional inside of the date_trunc function?

Any suggestions or ideas are greatly appreciated, thanks!

[DCamps]

I also tried this for inline comment syntax but that didn't work:

generate_series(date_trunc('month', [[{{start_date}} /*]]('2000-01-01'::date)*/), date_trunc('month', now()), interval '1 month') AS date

[flamber]

Hi @DCamps
You probably don't need Complex Default Value, but just Optional Clauses with coalesce() trickery:

SELECT
    generate_series(date_trunc('month', coalesce([[{{start_date}}::date,]] '2000-01-01'::date)), date_trunc('month', now()), interval '1 month') AS date

You can find other interesting uses of "Optional Clauses" in the forum: https://discourse.metabase.com/search?q="optional%20clauses"

[DCamps]

Thanks for your swift response @flamber, this works beautifully!