BigQuery DATETIMEとTIMESTAMPの違い

BigQueryにある、DATETIMEとTIMESTAMPオブジェクトですが、
同じ日時を扱うものではありますが、明確に違いがあります。

その為、DATEIMEとTIMESTAMPを直接比較することもできません。
どちらかに変換を書けてから、比較する必要があります。

この記事では日時型であるDATETIMEオブジェクトと
TIMESTAMPオブジェクトの違いについて解説していきます。

タイムゾーン

結論からいうと、
TIMESTAMPオブジェクトにはタイムゾーンが定義されていて、
どの地域（タイムゾーン）の何時何分と絶対的な情報になっています。
具体的にはUTCを基準とした日時の管理をしているわけです。

一方、DATETIMEオブジェクトは、
単純な日時であり、そこにはタイムゾーンの情報はありません。
何時何分といってはいるけど、それが日本（JST）なのか、ニューヨーク（EST or ESD）なのかまでは明言していないという事です。

DATETIME

BigQueryのドキュメントにはこのように定義されています。

DATETIME オブジェクトは、タイムゾーンに依存せずにカレンダーや時計に表示される日時を表します。
これには年、月、日、時、分、秒、サブ秒が含まれます。絶対的な時刻を表すには、タイムスタンプを使用します。

BigQueryでは、DATETIME('2021-05-29 19:00:00')のように表現するとDATEIMEとして扱われます。
'2021-05-29 19:00:00'だけで書いても、暗黙的にDATETIMEにキャストされて処理される事もあります。

SELECT 
DATETIME('2021-05-29 19:00:00') AS datetime_1,
;

DATETIMEオブジェクトをそのまま出力すると、Tという文字が間に入った日時表記になります。
この日時は、どの地域（タイムゾーン）の時間という情報まではありません。
単純に、何時であるといっているだけにすぎません。

TIMESTAMP

BigQueryのドキュメントにはこのように定義されています。

TIMESTAMP オブジェクトは、タイムゾーンや夏時間などの慣習に関係なく、マイクロ秒精度の絶対的な時刻を表します。

TIMESTAMPオブジェクトを扱う場合は、
TIMESTAMP('2021-05-29 19:00:00') のような書き方となります。
この場合、指定した日時はUTCで指定したものとして扱われます。

上記は、TIMESTAMP('2021-05-29 19:00:00', 'UTC') と同じで、UTC指定を省略したものとして扱われます。

UTC時間なので、JST時間でいうと2021-05-30 04:00:00に等しい日時になります。

JSTのタイムゾーンで指定したい場合は、第２引数に指定のタイムゾーンを記述します。
TIMESTAMP('2021-05-29 19:00:00', 'Asia/Tokyo')

下記はTIMESTAMPオブジェクトを使った例です。

SELECT 
TIMESTAMP('2021-05-29 19:00:00')                     AS timestamp_1, -- TIMESTAMP UTCとして扱われる
TIMESTAMP('2021-05-29 19:00:00', 'UTC')              AS timestamp_2, -- ↑と同じ
TIMESTAMP('2021-05-29 19:00:00', 'Asia/Tokyo')       AS timestamp_3, -- 日本時間 "JST"とは書けないので注意
TIMESTAMP('2021-05-29 19:00:00', 'America/New_York') AS timestamp_4, -- ニュヨーク時間 "EST"とは書けないので注意
;

入力値は、TIMESTAMPオブジェクトとして、UTCで管理されているのがわかります。
それによって絶対的な日時を表現しています。

まとめ

DATETIMEオブジェクトは、
タイムゾーン情報はなくて、ただの日時(何処の地域の何時とは言ってない)

TIMESTAMPオブジェクトは
タイムゾーン情報も保持してる(実際にはUTCとして管理されてる)

Javaでいうところの、LocalDateTimeとZonedDateTimeの違いみたいなものだと理解することができます。
（Zonedが、タイムゾーン情報も持ってる方）

また、両者を比較する時は、
どちらかのオブジェクトに型合わせして比較する必要があります。

• DATETIME -> TIMESTAMP へ変換する場合
  TIMESTAMP(<DATETIME>, 'Asia/Tokyo')

• TIMESTAMP -> DATEIME へ変換する場合
  DATETIME(<TIMESTAMP>, 'Asia/Tokyo')