Create a postgresql field filter to change date between day/week/month/year

Hi there,
I wonder if there's a way to create a field filter that can change grouping freely between day/week/month/year in a single figure without duplicating the saved questions.

Most databases uses single-quote ' for string literals, and double-quote " for identifiers.
You'll need to create a lookup table if you want to use Field Filters.

Hi @flamber,
I wasn't clear of how to build the lookup table, do you mean build time dimension and date dimension tables?
and how should I use it to group by the different dateparts?

@ANYANNY02 The errors you are getting is because of bad quoting - " instead of using '.
You are basically asking for this: - upvote by clicking :+1: on the first post
If you want dropdowns currently, then you'll need to create a table, which contains the values you need.
Have a look here: How to create a SQL filter variable to change date grouping between month/week/day

@flamber Yes, I have read the second post, should I try zhangguiyu's solution and turn the expression to postgresql? or what I am asking is how should the lookup table looks? Thank you.

@ANYANNY02 Metabase will only scan tables/views for values to show in dropdowns, so if you want a dropdown, then you have to create a table/view that Metabase can scan.
I would recommend that you simple just tell people to input a filter value. It will make everything a lot more simple for you.

@ANYANNY02 Hey, I had a similar need and this is what worked for me.
I put a filter in the date_trunc({{granularity}}, date), made it required and set a default value to 'day' or whatever you need and the user can write in hour, day, week, month, etc.

1 Like

Thanks @calvin, I solved the issue by creating a view table,
then use the date_trunc((select “dateformat” from view_datecodes where {{datecode}}), “createdat”) , by this way, you will be able to have a dropdown widget.

1 Like