BigQuery query to specify timezone and search with WHERE clause

Tadashi Shigeoka ·  Tue, January 9, 2018

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

BigQuery | ビッグクエリ

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