Knowledge Drop
Last tested: Jan 23, 2018
This question comes up from time to time and the answer is that the pattern is expected at the moment. For example, both code blocks below have Looker generated SQL for a date field. The SELECT statement has a convert timezone directly on the date field, but in the WHERE statement the convert timezone is on the date from the filter (i.e. is on the day 1-21-2018)
A way to think of what is going on is as follows:
The user has asked, essentially, “in my timezone (LA / PST) what events happened on 2018-01-21"? In the database timezone (UTC), that means “What events happened between 2018-01-21 08:00 - 2018-01-22 08:00? This is the reasoning for the WHERE statement.
And then, Lookers converts in the SELECT because we don’t want to show the UTC time, instead we want to show the user the user specific query timezone (LA / PST). This is the reasoning for the SELECT statement.
MYSQL
SELECT
DATE(CONVERT_TZ(order_items.returned_at ,'UTC','America/Los_Angeles')) AS `order_items.returned_date`
FROM db.order_items AS order_items
WHERE
(((order_items.returned_at ) >= ((CONVERT_TZ(TIMESTAMP('2018-01-21'),'America/Los_Angeles','UTC'))) AND (order_items.returned_at ) < ((CONVERT_TZ(DATE_ADD(TIMESTAMP('2018-01-21'),INTERVAL 1 day),'America/Los_Angeles','UTC')))))
GROUP BY 1
ORDER BY DATE(CONVERT_TZ(order_items.returned_at ,'UTC','America/Los_Angeles')) DESC
LIMIT 500
Redshift
SELECT
DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', opportunityfieldhistory.createddate )) AS "opportunityfieldhistory.createddate_date"
FROM opportunityfieldhistory AS opportunityfieldhistory
WHERE
(((opportunityfieldhistory.createddate ) >= ((CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', TIMESTAMP '2018-01-21'))) AND (opportunityfieldhistory.createddate ) < ((CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', DATEADD(day,1, TIMESTAMP '2018-01-21' ))))))
LinkedIn
Twitter
