Which string format for weeks should be used to recognize as timeseries

Hello there!

Some context: I'm using a MySQL database.

I'm trying to set up a questions where one of the variables of my question defines the timescale on which the graph is reported: Weekly vs Monthly.

This is an example of a basic setup i'm looking for:

SELECT DATE_FORMAT(created, "%Y-W%v") as Period, count(*)
FROM mytable
GROUP BY Period

The problem here is that the ISO format "2024-W05" is not being recognized as a time series. I could do something like created - interval (weekday) created to get the first day of the week, but this no longer allows me to use a snippet to pass to the DATE_FORMAT string, so I would have to write this logic for every single query all over again.

Is there any other format i could get to to make metabase understand that i'd like it to be treated as a time scale?

Thanks!

What about something like

SELECT 
    CASE 
        WHEN {{time_scale}} = 'weekly' THEN DATE_FORMAT(DATE_SUB(created, INTERVAL WEEKDAY(created) DAY), '%Y-%m-%d')
        WHEN {{time_scale}}= 'monthly' THEN DATE_FORMAT(created, '%Y-%m')
    END as Period, 
    COUNT(*)
FROM 
    mytable
GROUP BY 
    Period
ORDER BY 
    Period;