Pivot table with weekday and time

Hi. I am trying to design a question which shows the creation time pattern of the records by categorizing them with weekday and time. I have a timestamp column in my table. What should be the best way to create the view? I am expecting something like this:

Hi @llg3888
You would have to do it in SQL currently if you don't actually have data points for each day and hour, so you'll either have to use a lookup table or generate it on-the-fly, and just use the regular Table visualization with pivoted enabled.

Each data point contain a timestamp in my table. I tried to split it into date and time but it turns out that the time cannot be used in the Group By feature of Metabase. Should I do the grouping in raw SQL instead?

@llg3888 Like I said, you'll need to do it in SQL currently. There's several limitations you hit in GUI.

You should duplicate column with date. Then you can summarize by "2 dates"


@inzKarwowski It won't work as expected currently:
https://github.com/metabase/metabase/issues/4726 - upvote by clicking :+1: on the first post

This will work in every situation.
Here you have the question

And result

1 Like

Thanks! This is awesome! I can even use the conditional formatting to create a heatmap now :slight_smile:

1 Like