View the original community article here

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' ))))))​

​