I’ll introduce sample code for BigQuery queries that specify timezone and search with WHERE clause.

Prerequisite: Standard SQL

I’m using Standard SQL, not Legacy SQL.

Query to filter by specifying timezone

Here’s sample code for a BigQuery query that filters by specifying timezone.

It filters the date column for a one-week period from 1/1 to 1/8 in PT (PST/PDT).

SELECT
  *
FROM
  `data.201801`
WHERE
  date >= TIMESTAMP('2018-01-01 00:00:00', 'America/Los_Angeles')
AND
  date < TIMESTAMP('2018-01-08 00:00:00', 'America/Los_Angeles')

That’s all from the Gemba where I wanted to filter by specifying timezone in BigQuery.

References

• BigQueryのStandard SQLの便利な機能とLegacy SQLからの変更点をいくつか紹介 - 唯物是真 @Scaled_Wurm