Broken sql generator when parsing dates

  • 7 December 2017
  • 1 reply

Hi there, I’m having a challenge with the date converter. I have a pretty simple table that has a dimension group for dates. I selected date and filtered it to be within the last 90 days.

My DB stores dates in utc and the connection is set to convert them to PST. This is what the SQL generator gave us for the where clause -

WHERE ((((date ) >= ((CONVERT_TIMEZONE(‘US/Pacific’, ‘UTC’, DATEADD(day,-89, DATE_TRUNC(‘day’,CONVERT_TIMEZONE(‘UTC’, ‘US/Pacific’, GETDATE())) )))) AND (date ) < ((CONVERT_TIMEZONE(‘US/Pacific’, ‘UTC’, DATEADD(day,90, DATEADD(day,-89, DATE_TRUNC(‘day’,CONVERT_TIMEZONE(‘UTC’, ‘US/Pacific’, GETDATE())) ) )))))))

What’s up with that? It converts getdate into PST and then unconverts it - instead of converting it to PST and converting date to PST. There is no value set for convert_tz in the lookml for this. Thanks!

1 reply

Userlevel 5

Hey @sebastian! We do timezone conversion like this because it preserves indexes on the underlying date column where possible.

You asked why we don’t convert the getdate to PST and also convert the underlying column to PST. This is because it would make any indexes on the underlaying column unusable in most databases, since you would be doing a transformation on that column. Instead, we convert the getdate to PST, do any sort of transformations we need (in this case, truncating to the day, so the days are grouped by PST date boundaries, and subtracting 89 days). And then, we convert this all back to UTC to compare directly to the underlying column.