Custom column as date

Use case: i want to create a pivot table with 2x group by date.
(Day / hour)

This is not possible at the moment. I thought i could maybe do a work around by adding a custom column which is just a 'copy' of the date field.

If I look at the results in a table, then i can see that column correctly added, but I can't use it in a group by, because it is not seen as a 'date' column.
Is it possible to specifiy the 'custom' column as a date column?

Hi @cobalt
You are looking for this essentially:
https://github.com/metabase/metabase/issues/4726 - upvote by clicking :+1: on the first post
And https://github.com/metabase/metabase/issues/11371

Thanks for the links.
The 1ste one i already liked.

Too bad there is no workaround in Metabase for it at the moment then :slight_smile:

@cobalt Create a database view with an extra column, that's what I have been doing for a couple of years.
It might be possible to do something with Models in 0.42.

Thanks for the suggestion. If it was for personal use, i would definitely already have done this.
But it is for all our clients where we install metabase, and i don't want to work with all different kind of views instead of the main tables.

Let's see what i can do with Models as soon there is a new version out without the current bugs in RC1 :slight_smile:

np, at least i was able to test what i want, and can confirm that it doesn't work as well.
(I wanted to group by 2x on date field)

So i created a model with just the main table, join to the original table, and as soon as i group by the date field, the other date field is 'gone'.

@cobalt It would be a million times easier if you use Sample Dataset and provide screenshots, then it's much easier to follow along.

I'll try to explain with sameple database.

Order table, the CREATED_AT field.
I want a pivot table on that field.

I want to group by week of day in columns and group by hour of day in rows.

So i create a model based on the orders table. Then i create a new question based on the model and i join the orders table.

Then i want to summarize the total records and group by day and hours.
And i don't have the date field 2 times.

As long as i don't use the field in my group by, i can find it in the joined table to group by on. But as soon as i select it either on the model table or the original table, the other field will be gone.

@cobalt So do it with SQL instead, and don't do a join, just add the extra column to the select clause.

I'm unable to do it like i want it in SQL.
My sql is pretty basic :slight_smile:

But that has nothing to do anymore with metabase offcourse :slight_smile:

@cobalt select *, my_date_column as my_date_column2 from my_table
Example from Sample Database:
select *, CREATED_AT as CREATED_AT2 from PRODUCTS - save question, convert to Model and then I connected the metadata just to make it look nice.

Thank you so mutch for pointing me in that direction!

Too bad i can't go deeper into these numbers when i click on a number to see those records:

But to be honest that is not such a big deal :slight_smile: That's another topic

@cobalt
Logs are everything - without those, I don't know - Admin > Troubleshooting > Logs.
And always include "Diagnostic Info" from Admin > Troubleshooting.

It would also be extremely helpful if you could run in English, since while I can read Dutch, it's just a lot easier to read English - and you will find many more results, when you search for English errors.

But you are essentially seeing this issue:
https://github.com/metabase/metabase/issues/18011

I'll keep it in mind next time. But don't think i have to post it this time as the issue you mentiond perfectly describes it.