Day fields is aggregated by minute by default

Get Help
1

Hi,
I am using Metabase 0.44 with BQ database and a table with a column called 'Day' with type 'DATE'. Whenever I try to aggregate by this column using query builder, the query builder chooses minute aggregation by default - which neither makes much sense, nor actually works with BQ.

While I can modify binning by hand, it means that every user, every time they have a question involving this question, have to remember to do it.

Is there any way to specify default time grain for a column?
Even better, would it make sense to use day aggregation by default for columns of type 'DATE'?

The closes prior question I could find was Unsupported temporal bucketing: You can't bucket a :type/Date Field by :minute - but it only say about manually changing binning, not the two questions I have.

Thanks in advance!

2

Hi @vladimir.prus
The default granularity is based on the fingerprint of the field, but if there weren't any data initially (or just a single row) in the database, then the fingerprint is now good.
There's currently no easy way to clear fingerprinting:
https://github.com/metabase/metabase/issues/18074 - upvote by clicking :+1: on the first post

3

@flamber, thanks for the answer! I've tried to delete the database entirely, and start over, and the default binning is still a minute. I also tried the manual approach from the issue, followed by resync, and it did not help either. I wonder if that's because I have a lot of data, and if Metabase samples 1000 rows, they all might come with the same day field? In the metabase_field I see this

{"global":{"distinct-count":1,"nil%":0.0},"type":{"type/DateTime":{"earliest":"2022-01-18T00:00:00Z","latest":"2022-01-18T00:00:00Z"}}}

and the data most clearly has way more distinct dates.

4

@vladimir.prus Not sure why it is being detected as type/DateTime instead of type/Date, when you say it's a plain date column, since that could definitely be part of the problem, together with the weird distinct count. You can try to modify it manually, so latest is some months into the future (ex. today).

We have other issues about not providing options that aren't valid for field:
https://github.com/metabase/metabase/issues/21269 (more history https://github.com/metabase/metabase/pull/20902)

5

Now that you mention it, I looked at the entire row, and it's quite strange
| 239237 | 2022-08-10 13:12:48 | 2022-08-10 13:51:15 | day | type/Date | NULL | | NULL | | 5 | 15747 | NULL | Day | normal | NULL | 2022-08-10 13:51:15 | NULL | NULL | {"global":{"distinct-count":1,"nil%":0.0},"type":{"type/DateTime":{"earliest":"2022-01-18T00:00:00Z","latest":"2022-01-18T00:00:00Z"}}} | 5 | DATE | NULL | NULL | 5 | 0 | type/Date | NULL | NULL |

The type is clearly Date, but scan results are using "DateTime". After running
update metabase_field set fingerprint='{"global":{"distinct-count":300,"nil%":0.0},"type":{"type/Date":{"earliest":"2022-01-1T00:00:00Z","latest":"2022-08-18T00:00:00Z"}}}' where id = 239237;
UI offers date aggregation by default.

Thanks!