Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Broken sql generator when parsing dates

  • 7 December 2017
  • 1 reply
  • 62 views
S

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

L
Rank
Userlevel 5
Badge

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.

Reply


Terms of Service