Can't group by hour in time field


#1

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?


#2

@lucas.lima

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.


#3

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:


#4

@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 (í) ?


#5

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.


#6

@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.


#7

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.


#8

@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.


#9

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.