概要
BigQueryで日時を扱う時に混乱しやすいTIMESTAMPとDATETIMEについて、公式リファレンスの該当箇所や使用時の挙動も示しながら整理します。
TIMESTAMP型およびDATETIME型の定義
TIMESTAMP型
TIMESTAMP型のリファレンス
A timestamp value represents an absolute point in time, independent of any time zone or convention such as daylight saving time (DST), with microsecond precision.
A timestamp is typically represented internally as the number of elapsed microseconds since a fixed initial point in time.
Note that a timestamp itself doesn't have a time zone; it represents the same instant in time globally. However, the display of a timestamp for human readability usually includes a Gregorian date, a time, and a time zone, in an implementation-dependent format. For example, the displayed values "2020-01-01 00:00:00 UTC", "2019-12-31 19:00:00 America/New_York", and "2020-01-01 05:30:00 Asia/Kolkata" all represent the same instant in time and therefore represent the same timestamp value.
TIMESTAMP型は、タイムゾーンや夏時間に依存しない絶対的な時点を表すと定義されています。そのため、TIMESTAMP型の日時は全てUTCとして表され、後述するTIMESTAMP関数にタイムゾーンを含む日時を入力しても同じ瞬間を表すUTCの日時が返されます。
DATETIME型
DATETIME型のリファレンス
A datetime value represents a Gregorian date and a time, as they might be displayed on a watch, independent of time zone. It includes the year, month, day, hour, minute, second, and subsecond. To represent an absolute point in time, use a timestamp.
DATETIME型はグレゴリオ暦の日付と時刻を表し、タイムゾーンに関係しない時計に表示される日時を表すと定義されています。つまり、この型はあくまで年月日時分秒の値を保存しているだけで、それがどのタイムゾーンの日時を表しているかはユーザーが意識して使う必要があります。
TIMESTAMP関数およびDATETIME関数の使用時の挙動
TIMESTAMP関数
TIMESTAMP関数のリファレンス
TIMESTAMP関数は、タイムスタンプを表す文字列などを引数としてTIMESTAMP型の値を返します。TIMESTAMP関数の使用例を以下に示します。
SELECT
TIMESTAMP('2025-04-20 03:00:00'), -- 2025-04-20 03:00:00 UTC
TIMESTAMP('2025-04-20 03:00:00', 'Asia/Tokyo'), -- 2025-04-19 18:00:00 UTC
TIMESTAMP('2025-04-20 03:00:00 Asia/Tokyo'), -- 2025-04-19 18:00:00 UTC
TIMESTAMP('2025-04-20 03:00:00+09'), -- 2025-04-19 18:00:00 UTC
TIMESTAMP関数の戻り値では、全てUTCの日時が返されていることが確認できます。
1つ目では、引数にタイムゾーンの情報が含まれておらず、入力した日時がそのままUTCの日時として返されています。
2～4つ目は全て同じ意味ですが、Asia/Tokyoの日時 2025-04-20 03:00:00 と同じ瞬間のUTCの日時 2025-04-19 18:00:00 が返されていることになります。
DATETIME関数
DATETIME関数のリファレンス
DATETIME関数は、年月日時分秒の値やタイムスタンプ型のオブジェクトを引数としてDATETIME型の値を返します。DATETIME関数の使用例を以下に示します。
SELECT
DATETIME(2025, 4, 20, 3, 0, 0), -- 2025-04-20T03:00:00
DATETIME(TIMESTAMP('2025-04-20 03:00:00')), -- 2025-04-20T03:00:00
DATETIME(TIMESTAMP('2025-04-20 03:00:00'), 'Asia/Tokyo'), -- 2025-04-20T12:00:00
DATETIME(TIMESTAMP('2025-04-20 03:00:00', 'Asia/Tokyo')), -- 2025-04-19T18:00:00
DATETIME関数の戻り値では、タイムゾーンの情報を含まない単なる値として日時が返されていることが確認できます。
1つ目では、引数にタイムゾーンの情報はなく、戻り値はユーザーが決めた任意のタイムゾーンの日時として解釈できます。
2つ目についても、引数に明示的なタイムゾーンの情報はありませんが、前述の通りTIMESTAMP型はUTCの日時を表すので、それがキャストされた戻り値もタイムゾーンはUTCとして解釈するのが適切です。
3つ目では、第一引数で指定したUTCの日時 2025-04-20 03:00:00 と同じ瞬間のAsia/Tokyoの日時として 2025-04-20T12:00:00 が返されていることになります。そのため、戻り値のタイムゾーンはAsia/Tokyoとして解釈するのが適切です。
4つ目では、コードは3つ目と酷似しており混乱しますが、Asia/Tokyoの日時 2025-04-20 03:00:00 と同じ瞬間のUTCの日時 2025-04-19 18:00:00 がTIMESTAMP型で返され、それがそのままDATETIME型にキャストされて 2025-04-19T18:00:00 が返されています。そのため、戻り値のタイムゾーンはUTCとして解釈するのが適切です。
TIMESTAMP型およびDATETIME型の比較時の挙動
DATETIME型はDATETIME型同士で比較できるほか、DATE型との比較も可能です。DATE型はその日の0時0分0秒として評価されます。これらの比較を行う際は、それぞれの値のタイムゾーンをユーザーが意識して、整合したタイムゾーンで演算を行う必要があります。
SELECT
DATETIME(2025, 4, 20, 0, 0, 0) <= DATETIME(2025, 4, 20, 3, 0, 0), -- true
DATETIME(2025, 4, 20, 0, 0, 0) = DATE(2025, 4, 20), -- true (DATEは0時0分0秒として評価される)
DATETIME(2025, 4, 20, 3, 0, 0) <= DATE(2025, 4, 20), -- false (DATEは0時0分0秒として評価される)
TIMESTAMP型はTIMESTAMP型同士で比較できますが、タイムゾーンの情報を持たないDATETIME型やDATE型との比較はできません。
SELECT
TIMESTAMP('2025-04-20 00:00:00') <= TIMESTAMP('2025-04-20 03:00:00'), -- true
-- TIMESTAMP('2025-04-20 00:00:00') <= DATETIME(2025, 4, 20, 3, 0, 0), これはエラー
-- TIMESTAMP('2025-04-20 00:00:00') = DATE(2025, 4, 20), これはエラー