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
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:
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.
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.
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.
So create a new issue and supply with the relevant information from here.