Datetime types in Looker and conversions into strings

  • 17 November 2017
  • 1 reply

Userlevel 1

When using the time dimension group in Looker, this causes poor performance when converted into SQL Server T-SQL, as the CONVERT(varchar(x), my_date) statement results in cardinality estimation issues.

Instead, if these were done using the native datatypes, the same can be achieved without having to convert the data.


DECLARE @my_date datetime = GETDATE()

SELECT CONVERT(date, @my_date) -- convert the datetime into a date for equality comparions
SELECT CONVERT(date, DATEADD(month, DATEDIFF(month, 0, @my_date),0)) -- month comparisons
SELECT CONVERT(date,DATEADD(quarter,DATEDIFF(quarter,0,@my_date),0)) -- quarter comparisons
SELECT CONVERT(date,DATEADD(year,DATEDIFF(year,0,@my_date),0)) -- year comparisons

This can result in significant query performance uplift as SQL Server statistics can be fully utilised; is this something that could be implemented?

1 reply

Hey Matthew,

Thanks for your question. By default dimension groups also include a ‘raw’ timeframe that will stop Looker from converting the date to a varchar. Here’s an article that goes into more detail on how to use the raw timeframe.