SELECT DATE_TRUNC({{time_granularity}}, created_at) , COUNT(DISTINCT id) FROM transactions GROUP BY 1
So I’m trying to make a SQL query that allows the end-user to change time granularity grouping (by days, weeks, months, etc.). Problem: the bar chart can intelligently adjust column-width for day-grouping, month-grouping, quarter-grouping - but when it’s week-grouping, the bar chart thinks it’s daily data.
Can this be a new feature? If yes, how easily built? Also, current work-around?
Edit: I do know that Metabase has intelligent grouping in the “Ask a question” workflow, my problem is more with the SQL query (the query above is simplified to isolate the problem) Edit 2: I’m using Metabase 0.33.7.3, with Redshift
Hi @vl307
Which version of Metabase?
And which database type are you querying?
What are you inputting as filter, when trying to do the week-grouping?
When you make a Custom question and group by created_at by Week, and convert it to Native, what does query look like?
Example; MySQL looks like str_to_date(concat(yearweek(`CREATED_AT`), ' Sunday'), '%X%V %W') and H2 looks like parsedatetime(formatdatetime("CREATED_AT", 'YYYYww'), 'YYYYww')
I’ve tried using ‘w’, ‘week’, and ‘weeks’ - but they have the same results.
Custom-to-native SQL query is (date_trunc('week', CAST((CAST("transactions"."created_at" AS timestamp) + INTERVAL '1 day') AS timestamp)) - INTERVAL '1 day') AS "created_at"
@vl307
Okay, I don’t have any Redshift to test with, but what does the query look like, if you try to do a custom-to-native with Month?
What is the database column type of created_at?
And what’s the Field Type? Admin > Data Model > (database) > (table) > (column)
@flamber
Custom-to-native with Month looks something like this date_trunc('month', CAST("transactions"."created_at" AS timestamp)) AS "created_at", count(distinct "transactions"."id") AS "count"
created_at is stored as datetime.
Within Metabase, created_at is modeled as Creation timestamp.
@vl307 I think I misunderstood the request to begin with.
Metabase doesn’t know what date interval you’re showing, when using SQL, so it does not know the difference between a single date, week, month or year. The returned value is just a date.
The visualization has some logic that tries to figure out what type of data it’s receiving - it’s not perfect, and sometimes a little too aggressive or in this case, not detecting it at all.
When I try to reproduce with Sample Dataset, then it displays weeks as dates and quarters as months, so it’s not specific to weeks only.
You can open a feature request here: https://github.com/metabase/metabase/issues/new/choose
Perhaps the workaround is to show the week number instead? Or some other manipulation. SELECT CASE WHEN {{time_granularity}}='week' THEN to_char(created_at, 'IYYY-IW') ELSE DATE_TRUNC({{time_granularity}}, created_at) END, COUNT(DISTINCT id) FROM transactions GROUP BY 1