Charts not detecting weekly-time-grouping when used in conjunction with SQL-query variable

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, 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’m using Metabase, with Redshift.

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"

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)

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:

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