Hi there, guys! My issue is as simple as my title. I can’t group by hour when using MySQL time field. My metabase version is 0.31.0. Any thought on that?
I cannot completely reproduce this on 0.31.2, since I’m on SQL Server 2008, where format
function doesn’t exists, so I cannot use the Group By Hour
, but I can use Group By Hour of Day
Are you sure the type is set correctly in Settings > Admin > Data Model > (database) > (table) > (column)? I’ve set mine to Creation Time
and my database column is TIME
.
Maybe my post wasn't clear enough. When I said group by hour, I was meaning the grouping by hour in metabase. Check this:
No matter what column tyupe I choose in metabase admin panel, it tries to group by entries by day - it doesn't show any option to group by any other level.
When using datetime MySQL field, and setting column to Creation Datetime, or anything similar to that on metabase, it will display all these options (incluiding hour, minute, second) to group by:
@lucas.lima
So when MySQL column is TIME
and Metabase column is Creation Time
, then it does not work.
But when MySQL column is DATETIME
and Metabase column is Creation DateTime
, then it works.
Correct? Are you using 0.31.2 now?
Do you see anything in the browser console or Metabase log?
Have you tried using a column name that doesn’t contain accents (í
) ?
Yes, you are correct. In a more generic description, it wil not work with time, and it will always work with datetime.
Tried with 0.31.2 in my local machine, for no good; no entries on metabase log or browser console (it works, just not properly).
Tried to use a column without any special characters as well, no difference found. Seems to me that Metabase doesn’t really love Time fields in MySQL.
@lucas.lima
Can you check your browser console and look at the actual database query being created by Metabase - here’s how to get the query
Please post it - sanitize where needed.
This is the query generated (one can suppose, but that table contains one row for each second of the day - literally, all possible time values):
SELECT date(dm_time.time) AS time FROM dm_time GROUP BY date(dm_time.time) ORDER BY date(dm_time.time) ASC LIMIT 2000
It is literally parsing my time column as date (and grouping it as ":day", as you can see in the print). Although it is defined as a Time column in Metabase.
When it comes to a datetime column, metabase does STR_TO_DATE(DATE_FORMAT(datetime_column, '%Y-%m-%d %H'), '%Y-%m-%d %H')
instead of what was done on the query above.
@lucas.lima
I cannot find any bugs related to TIME - only the one about CAST of DATE columns, which messes with the indexing. This is slightly related, but not really.
https://github.com/metabase/metabase/issues/4043
So create a new issue and supply with the relevant information from here.
Oh, I know that issue quite well, I’m already following that one from some time, quite useful.
Thanks for your support, @flamber. You’ve been very helpful. Gonna do that and upload it here.