Create date trunc by day, week, month using text filter

I want to create create dynamic date aggregation (day, week, month)
SELECT DATE_TRUNC(instagram_business.user_insights.date, {{DATE}})

However, it showed A valid date part name is required

And I found this web https://github.com/metabase/metabase/issues/6583
so I change to be group by and it still doesn't work
What should I do?

date should show by week but it shows by day

Hi @Jaeng
You are grouping by "date". You need to only group by your trunc.
Try replacing the variable with a hardcoded value until it works exactly like how you intended.

If I remove 'date', it still need to group by date. I have no idea what to do.
Can you elaborate more about hardcode value? Thank you

@Jaeng Replace {{date}} with 'week' and run the query on your database. If the query doesn't run there, then it would be impossible for Metabase to run it.
From the looks of it, you'll need to add the case-clause to the select-clause too.