BigQueryでTIMESTAMPのカラムに対し日付を指定する際に、自分が今まで誤って行った方法をまとめてみる。
課題設定
startTime というTIMESTAMP型のカラムがあるテーブルを対象として、
startTime
----------
2016-01-01 01:00:00 UTC
2013-04-21 11:33:01 UTC
2019-09-27 10:30:20 UTC
…startTimeが2016年7月1日のレコードのみを取り出したいと想定する。
SELECT startTime, …
FROM dataset.table
WHERE [ ? ] // 2016-07-01のデータのみ取り出したい場合に、どう書くのが適切なのか（話を単純にするため、UTCからの変換は考慮に入れる必要がないと仮定する）
ものすごく単純な課題にもかかわらず、色々踏み抜いてきたので以下一つずつ書いていく。
失敗例1：
日付である 2016-07-01 を指定するぞ、という意識だけで以下のクエリをまず書いた。
WHERE startTime = date(2016, 7, 1)※ BigQueryのDATE型での特定の日時の書き方は以下を参照：
BigQueryリファレンス: Date functions
対象のstartTimeがTIMESTAMP型だという認識がそもそも持てていない時の失敗。（ No matching signature for operator = for argument types: TIMESTAMP, DATE. と怒られる）
失敗例2：
比較対象の型に合わせないといけないのだった、DATEからTIMESTAMPに変えよう…となって、次に以下のようなクエリを書いた。
WHERE startTime = timestamp('2016-07-01')※ BigQueryでのTIMESTAMP型での特定の日時の書き方は以下を参照：
BigQueryリファレンス: Timestamp functions
これは実行できてしまうのだけど、意図した形では動かない。
timestamp('2016-07-01') は 2016-07-01 00:00:00 UTCのことなので、00時ちょうどのデータしか合致しない。結果が1行も返ってこないか、想定よりとても少ない件数が返ってくることになる（count などで気づければよいのだけど、処理途中の部分だったりすると気づけないことがある）。
失敗例3：
では時刻まで含めた形で指定すれば良いのでは、となって以下のクエリを書いた。
WHERE startTime BETWEEN timestamp('2016-07-01 00:00:00') AND timestamp('2016-07-01 23:59:59')2016/07/01 のレコードの大半がこれで取得できるけど、正確ではない（終了側の指定の仕方が適切ではない）。
TIMESTAMP の説明として以下の記載がある。
A TIMESTAMP object represents an absolute point in time, independent of any time zone or convention such as Daylight Savings Time with microsecond precision.
BigQueryリファレンス: Data-types
TIMESTAMPはマイクロ秒の精度であることがわかる。範囲としては 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 UTCとある。
そのため、記載したクエリだと2016-07-01の23時59分59.5秒のデータなどが範囲に含まれないことになってしまう。
失敗例4：
ではTIMESTAMPの方をDATE型に変換してしまうか、となって以下のクエリを書いた。
WHERE date(startTime) = date(2016, 07, 01)※ DATE(timestamp_expression)という形式でTIMESTAMPをDATEに変換できる BigQueryリファレンス: Date funcitons
これでも2016/07/01のレコードを取得できる。ただ、これがパーティション分割テーブルだったりすると、全てスキャンされることになりパフォーマンスが下がる。
※ パーティション分割テーブルのスキャン範囲についてはこのあたりに記載がある
クエリでスキャンされるパーティションを制限するには、フィルタで定数式を使用します。クエリフィルタで動的式を使用すると、BigQuery はすべてのパーティションをスキャンする必要があります。
BigQueryリファレンス: パーティション分割テーブルに対するクエリ
成功例：
数々の失敗を経て以下の形式にたどり着いた。
WHERE
startTime >= timestamp('2016-07-01 00:00:00')
AND startTime < timestamp('2016-07-02 00:00:00')これだと、意図した範囲を指定できているし、定数式にもなっている。
（この条件を満たす他の書き方もあるのかもしれないけど…）
1つのネタでよくこれだけ失敗できたなという感があるけど、色々回り道したことで理解が深まったのでよかったと思うことにする。
