Grouping by date types instead of strings

[jwp]

For queries that group by a date, keeping the column as a date is much faster than converting it to a string. I wouldn’t be surprised if this is true for all databases; it is definitely true for Snowflake. Here’s an example:

select date_trunc('month',d), count(1) from foo group by 1
vs
select to_char(date_trunc('month',d),'YYYY-MM'), count(1) from foo group by 1

Looker generates the second version when selecting a month timeframe from a dimension_group for d. But the first version runs about 20x faster!

Ideally Looker would generate sql like the first query, but in lieu of that, what are people’s favorite workarounds?