BigQueryにおいて日時を表すデータ型はいくつかあるが、混乱しがちだ。
今回は私個人の備忘録を含めて、日時を表すデータ型について整理したいと思う。
日時を表すデータ型一覧
BigQueryにおいて日時を表すデータ型は下記４つある。
- TIMESTAMP型：タイムスタンプ型
- DATE型：日付型
- DATETIME型：日時型
- TIME型：時間型
特に赤字のTIMESTAMP型とDATE型がよく使われるので、この２つは明確に理解しておきたい。
それぞれの特徴を整理した表が下記だ。
日時を表すデータ型を深く理解するためにはタイムゾーンについて把握しておく必要がある。
タイムゾーンとは？
タイムゾーンとは共通の標準時を使う地域や区分のことだ。
※標準時とはある国家や地域が共通で使う時刻。
世界の基準となる時刻のことを協定世界時（UTC）と呼び、各地域のタイムゾーンの時刻はUTCとの時差で定義されている。
例えば、日本においてはUTC（協定世界時）に９時間を足した「日本標準時」（JST）が使われている。
タイムゾーンは、カレンダーや時計に表示される日時（時計を見て確認できる時間）からタイムスタンプ（絶対時間）へ、またはその逆に変換されるときに使用される。
そのため、BigQueryにおいて日時データを取り扱う時はこのタイムゾーンを考慮する必要があるのだ。
TIMESTAMP型
TIMESTAMP型とは、絶対的な時刻（UTC）を表すデータ型だ。
TIMESTAMP型を使うと、例えば、2022-12-01-09:00:00 のように表示されるが、2022年12月1日9時00分00秒という意味だ。
TIMESTAMP型のタイムゾーンはUTC（協定世界時）で表されるのが基本だが、他のタイムゾーン（日本のJSTなど）を指定して変換することも可能だ。
前述したとおり、UTCと日本の標準時であるJSTは9時間ずれがあるので、下記は同じ時刻を指している。
実務においては、このTIMESTAMP型からDATE型に変換することが多いが、DATE型はタイムゾーンの情報を持たない。
そのため、TIMESTAMP型からDATE型に変換する場合は「どのタイムゾーンの日付なのか？」を指定してあげる必要があるので、要注意だ。
BigQueryのデフォルトのタイムゾーンは UTCなので、データのアップロードをする際は注意が必要だ。
JST（日本の標準時）の日時データをそのままTIMESTAMP 型でアップロードするとタイムゾーンがUTCになってしまうからだ。
そのため、日時データをTIMESTAMP 型でアップロードする場合はロード前にタイムゾーンの情報を付与するか、後述するDATETIME型に変換しておく必要があるのは覚えておきたい。
DATE型
DATE型は日付に関する情報を持っているデータ型だ。
例えば、2022年12月1日9時00分00秒はDATE型で表示すると2022-12-01 という形で日付のみが表示される。
実務において時間データまでは必要なく、日付データがあれば目的を果たせる、というデータは多いので、DATE型がよく使われる。
ただ前述したとおり、DATE型はタイムゾーンの情報を持たないので、TIMESTAMP型からDATE関数等で変換する時はタイムゾーンを指定してあげる必要がある。
DATETIME型
DATETIME型は日付と時間に関する情報を持っているデータ型だ。
例えば、2022年12月1日9時00分00秒はDATETIME型で表示すると2022-12-01 という形で日付のみが表示される。
前述したTIMESTAMP型とDATETIME型はどちらも日付も時刻も表しているので
「同じじゃないの？」
と疑問を持つ人も多いと思う。
結論から言うと、TIMESTAMP型とDATETIME型の違いはタイムゾーンの情報を持つかどうかだ。
TIMESTAMP型とDATETIME型でそれぞれ2022年12月1日9時00分00秒を表してみる。
1 2 | SELECT TIMESTAMP("2022-12-01 09:00:00") AS timestamp ,DATETIME("2022-12-01 09:00:00") AS datetime; |
上記クエリを実行した結果が下記だ。
TIMESTAMP 型とDATETIME型で表記が微妙に違っているのがお分かりいただけると思う。
TIMESTAMP型はタイムゾーンが定義されていて、前述したUTC（世界標準時）を基準とした日時の管理がされている。
タイムゾーンを指定すれば、日本の標準時（JST）に変えることも出来るし、他の国のタイムゾーンに変えることも出来る。
一方でDATETIME型はタイムゾーンの情報を持たない。
例えば、2022-12-01-09:00:00 という値は2022年12月1日9時という意味だが、どのタイムゾーンの時間なのかはDATETIME型では表していないということだ。
そのため、JST（日本の標準時）の2022年12月1日9時かもしれないし、UTC（協定世界標準時）の2022年12月1日9時かもしれないが、DATETIME型ではその情報は持っていないということだ。
TIME型
TIME型は時間に関する情報を持つデータ型だ。
特定の日付とは関係なく、時刻のみを表す。
1 | SELECT TIME("2022-12-01 09:00:00") AS time ; |
上記クエリを実行してTIME型のデータを表すと下記のように時刻のみが表される。
このTIME型データもTIMESTAMP型と違い、タイムゾーンの情報は持たない。
もし絶対的な時刻を表したい場合はTIMESTAMP型を使う必要がある。
まとめ
今回はBigQueryにおける日時を表す4つのデータ型を解説した。
それぞれのデータ型の特徴を把握して、うまく使いこなせるようにしていきたい。
