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
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?