BigQueryのSQLいろいろ (3) 日付・時刻型

BigQueryのSQLについて、ドキュメントを読んだり実験したりしながら挙動を解き明かしていこうと思います。第3回は日付・時刻型についてです。

今回扱う型

TIMESTAMP
DATE
TIME
DATETIME
INTERVAL
RANGE

TIMESTAMP型

TIMESTAMP型は、地球上で共有される同時性における特定の瞬間をあらわします。ただし、計算の都合上うるう秒は除外されているため、UTCやTAIとの秒単位での対応関係は曖昧です。

TIMESTAMPが表現できる範囲は、遡及グレゴリオ暦の紀元後1年の頭 (ちょうどを含む) から10000年の頭 (ちょうどを含まない) までの9999年間です。閏日の扱いの差により、ユリウス暦基準では1年1月3日からしか扱えません。

TIMESTAMPの精度はマイクロ秒です。

うるう秒は除外されているため、TIMESTAMP型において1日は正確に86400秒です。これはUNIXタイムスタンプの計算方法と同じです。そのため、TIMESTAMP型が表せる範囲は正確に315537897600000000マイクロ秒の範囲です。

TIMESTAMPを表記する際は、RFC3339形式のオフセットつき日付時刻で書かれることが多いです。しかし、実際にTIMESTAMPが保持しているのは瞬間であり、タイムゾーンやオフセットの情報はパース時に破棄されています。

SELECT
  -- true
  TIMESTAMP '2025-01-02T12:34:56+09:00' =
  TIMESTAMP '2025-01-02T03:34:56Z'

うるう秒が除外されているため、RFC3339形式のパース時に正のうるう秒は通常の秒に変換されます。

SELECT
  -- true
  TIMESTAMP '2017-01-01T08:59:60+09:00' =
  TIMESTAMP '2017-01-01T09:00:00+09:00'

DATETIME型

DATETIME型は、名前の通り、日付と時刻の組み合わせを表します。意味的には、時刻を瞬間として解釈するのに必要な情報(地点)が指定されていないため、あくまでもオフセットに対しての相対的な時間概念を表すものとして解釈されます。

日本の9時とアメリカ西海岸(PST)の9時があったとき、DATETIME型はどちらも同じ「9時」という概念としてとらえます。
日本の9時とアメリカ西海岸(PST)の9時があったとき、TIMESTAMP型では前者を「UTCの0時」、後者を「UTCの17時」という概念としてとらえます。

ただし、意味的な扱いが異なるだけで、扱える情報の量はTIMESTAMP型とぴったり一致します。UTCを指定してDATETIMEとTIMESTAMPを相互変換するとき、1対1対応します。

実際、以下のように、DATETIMEでも閏秒の情報は捨てられます。

SELECT
  -- true
  DATETIME '2016-12-31T23:59:60' =
  DATETIME '2017-01-01T00:00:00'

DATE型

DATE型は日付を表します。DATETIMEの日付部分と一致します。

なお、日付もまたタイムゾーンに依存した概念であることに注意が必要です。日本の7時において、アメリカではまだ前の日です。

TIME型

TIME型は1日の間の時刻を表します。DATETIMEの時刻部分と一致します。

TIMEは巡回する

TIMEは巡回的な型です。つまり、1秒ずつ足していくといつかは元の場所に戻ってきます。この性質に対して、BigQueryは以下のような実装になっています。

うるう秒はパース時に通常の秒に変換されますが、このとき巡回が発生することがあります。 (23:59:60 → 00:00:00)
TIMEには演算子による加減算が実装されていません。
TIME_ADD, TIME_SUB は巡回的に実装されています。
TIME_DIFF は非巡回的です。1日の間での前後関係に基づいて計算されます。
比較演算子 (<, <=, >, >=, BETWEEN) は非巡回的です。1日の間での前後関係に基づいて計算されます。
ORDER BY は非巡回的です。1日の間での前後関係に基づいて計算されます。

INTERVAL型

INTERVAL型は2つの日付時刻の間の差を表すデータです。しかしその実態はやや複雑です。

INTERVAL の構成要素

INTERVAL は以下の3つの要素からなります。

年月数 (月単位)。-120000ヶ月から120000ヶ月まで。 (=10000年分)
日数 (日単位)。-3660000日から3660000日まで。(=およそ10000年分)
時間 (ナノ秒単位)。-316224000000000000000ナノ秒から316224000000000000000ナノ秒まで。 (=およそ10000年分)
- ドキュメント上はマイクロ秒と書いてあるが、実際にはナノ秒の記述を受け付けるためこのように記載しています。ただし、TIMESTAMPがマイクロ秒までしか扱わないため、実用上はナノ秒が出てくることはないと考えられます。

それぞれの要素は独立であり、3次元のベクトルのように振舞います。大きさの制限もそれぞれの要素で独立に適用されます。

INTERVAL の表示

表示上は、INTERVALの3要素はさらに個別の桁に分解されます。3要素ごとに符号は独立です。

年月数は、12ヶ月分を1年として "[符号]年数-月数" の形で表示されます。
日数はそのまま表示されます。
時間は "[符号]時間:分:秒.ナノ秒" の形で表示されます。

いずれかの要素が0でも全ての要素を文字列化します。できた文字列は空白で連結します。

そのため、「1年」や「1時間」などの単位はあくまで表示上のものであり、それぞれ「12ヶ月」や「3600000000000ナノ秒」と同じものとして扱われます。

INTERVAL 同士の算術演算

INTERVAL はベクトルのように振る舞うため、加減算やスカラー倍の計算を行うことができます。

SELECT
  -- 0-11 22 0:0:0.000000033
  INTERVAL '0-10 20 0:0:0.000000030' YEAR TO SECOND +
  INTERVAL '0-1 2 0:0:0.000000003' YEAR TO SECOND,
  -- 0-2 4 0:0:0.000000006'
  INTERVAL '0-1 2 0:0:0.000000003' YEAR TO SECOND * 2

INTERVAL の比較

INTERVALの同値性 (=, !=, IN, NOT IN, GROUP BY など) や大小関係 (<, <=, >, >=, BETWEEN, ORDER BY など) を比較するときの挙動はかなり特殊です。こうしたときは以下のように線形の量に射影して比較を行います。

1ヶ月は2592000000000000ナノ秒 (= 30日) とみなす。
- したがって1年は360日。
1日は86400000000000ナノ秒 (=24時間) とみなす。

そのため、内容が異なっていても、比較で同じ結果になるINTERVAL値が存在します。

SELECT INTERVAL 1 MONTH = INTERVAL 30 DAY -- true

しかし、これらは同じように振る舞うわけではありません。

SELECT
  -- 1
  EXTRACT(MONTH FROM INTERVAL 1 MONTH),
  -- 0
  EXTRACT(MONTH FROM INTERVAL 30 DAY)

INTERVAL の要素操作

INTERVALの要素を取り出すには EXTRACT を使います。これはINTERVALの表示のときに使われるアルゴリズムと概ね同様で、以下のように動作します。

YEAR は月数を12で割った商で、-833 〜 833 の値をとります。符号は月数と一致します。
MONTH は月数を12で割った余りで、-11 〜 11 の値をとります。符号は月数と一致します。
DAY は日数で、-3660000 〜 3660000 の値をとります。
HOUR は時間を3600000000000ナノ秒で割った商で、 -87840000 〜 87840000 の値をとります。符号は時間と一致します。
MINUTE は時間を 60000000000ナノ秒で割った商を60で割った余りで、 -59 〜 59 の値をとります。符号は時間と一致します。
SECOND は時間を 1000000000ナノ秒で割った商を60で割った余りで、-59 〜 59 の値をとります。符号は時間と一致します。
MILLISECOND は時間を 1000000ナノ秒で割った商を1000で割った余りで、 -999 〜 999 の値をとります。符号は時間と一致します。
MICROSECOND は時間を 1000ナノ秒で割った商を1000000で割った余りで、 -999999 〜 999999 の値をとります。符号は時間と一致します。MILLISECOND の情報を含む点に注意が必要です。

逆に、INTERVALを要素から構成するには MAKE_INTERVAL を使います。このとき、year や month など項別の引数で境界検査を行うのではなく、月数/日数/時間のそれぞれの要素まで計算してから境界検査を行うようです。

また、 JUSTIFY_DAYS, JUSTIFY_HOURS, JUSTIFY_INTERVAL はINTERVAL値の正規化に使えます。これはINTERVAL値の比較と同じく、1ヶ月=30日、1日=24時間の単位でくり上げるものです。このとき上位の要素と下位の要素の符号は揃えられます。

SELECT
  -- 0-0 11 12:0:0
  JUSTIFY_INTERVAL(INTERVAL '0-0 10 36:0:0' YEAR TO SECOND),
  -- 0-0 11 12:0:0
  JUSTIFY_INTERVAL(INTERVAL '0-0 13 -36:0:0' YEAR TO SECOND),
  -- 0-0 -11 -12:0:0
  JUSTIFY_INTERVAL(INTERVAL '0-0 -10 -36:0:0' YEAR TO SECOND),
  -- 0-0 -11 -12:0:0
  JUSTIFY_INTERVAL(INTERVAL '0-0 -13 36:0:0' YEAR TO SECOND)

日付・時刻の差分

日付・時刻の差分は以下のように動作します。

TIMESTAMPの差分は、時間要素のみで算出されます。
DATETIMEの差分は、日数と時間に分けて算出されます。月数は使いません。日数要素と時間要素の符号は一致します。
DATEの差分は、日数で算出されます。
TIMEの差分は、時間要素のみで算出されます。

SELECT
  -- 0-0 0 9529:1:1
  TIMESTAMP '2002-02-02T02:02:02Z' - TIMESTAMP '2001-01-01T01:01:01Z',
  -- 0-0 397 1:1:1
  DATETIME '2002-02-02T02:02:02' - DATETIME '2001-01-01T01:01:01',
  -- 0-0 396 22:58:59
  DATETIME '2002-02-02T01:01:01' - DATETIME '2001-01-01T02:02:02',
  -- 0-0 -396 -22:58:59
  DATETIME '2001-01-01T02:02:02' - DATETIME '2002-02-02T01:01:01',
  -- 0-0 -397 -1:1:1
  DATETIME '2001-01-01T01:01:01' - DATETIME '2002-02-02T02:02:02',
  -- 0-0 397 0:0:0
  DATE '2002-02-02' - DATE '2001-01-01',
  -- 0-0 0 1:1:1
  TIME '02:02:02' - TIME '01:01:01'

日付・時刻のINTERVAL操作

TIMESTAMPに対するINTERVAL操作は、月数要素がない場合にのみ行えます。1日=24時間として時間を加減算します。

DATETIMEに対するINTERVAL操作は月数要素をサポートしますが、この場合には月によって日数が異なることが問題になります。このような場合は以下の手順で処理します。

月数要素の加減処理を行う。
月の日数が足りないときは、日付成分をいじって月内に収める。このとき時間成分は変更しない。
日数要素・時間要素の加減処理を行う。

SELECT
  DATETIME '2025-01-31T00:00:00' + INTERVAL 1 MONTH,
  DATETIME '2024-02-29T00:00:00' + INTERVAL 1 YEAR,
  DATETIME '2025-01-31T00:00:00' + INTERVAL '0-1 -1 0:0:0' YEAR TO SECOND,
  DATETIME '2025-01-31T00:00:00' + INTERVAL '0-1 1 0:0:0' YEAR TO SECOND,
  DATETIME '2025-01-30T00:00:00' + INTERVAL '0-1 -1 0:0:0' YEAR TO SECOND,
  DATETIME '2025-01-30T00:00:00' + INTERVAL '0-1 1 0:0:0' YEAR TO SECOND

DATEに対するINTERVAL操作は、DATETIMEに変換してから実行されます。

TIMEとINTERVALを足す操作はありません。

TIMESTAMP_ADD などの関数は一般のINTERVALを受け取る演算ではありませんが、基本的にはここに挙げたものと同様に動作すると考えられます。

RANGE 型

RANGE 型は半開区間をあらわす型で、端点として2つの値をとります。

RANGE はジェネリック型ですが、以下の3種類のインスタンス化のみをサポートします。

RANGE<TIMESTAMP>
RANGE<DATETIME>
RANGE<DATE>

一般論としては、区間データ型はを整数などの他のデータ型に対しても使えることが多いです。BigQueryではこの一般論が成り立たず、RANGE型は日付・時刻関連の処理にのみ利用することができます。

RANGE型で表せるもの

RANGE型は、下端が閉(端点を含む)、上端が開(端点を含まない)であるような非空な区間のみを扱えます。空区間は扱えません。

SELECT RANGE<DATE> '[2025-01-01, 2025-01-01)' -- エラー (空区間)

いっぽう、非有界な区間を扱うことはできます。これは表記上は UNBOUNDED と記載され、端点の値としては NULL が用いられます。

SELECT
  RANGE<DATE> '[2025-01-01, 2025-01-02)', -- 有界
  RANGE<DATE> '[2025-01-01, UNBOUNDED)', -- 上に非有界
  RANGE<DATE> '[UNBOUNDED, 2025-01-02)', -- 下に非有界
  RANGE<DATE> '[UNBOUNDED, UNBOUNDED)' -- 全体集合

RANGE と INTERVAL

RANGE は端点の情報を持っているため、同じ長さでも位置によって異なる区間を表します。いっぽう、 INTERVAL は長さの情報のみを持ちます。

なお、数学的には「区間 (interval)」と呼ぶ場合、ここでいう RANGE 相当のものを指します。用語の混乱に注意が必要です。

RANGE の演算

RANGEの構成には RANGE<T> リテラルまたは RANGE<T> 関数を使い、分解には RANGE_START / RANGE_END を使います。

代数的な演算としては以下が提供されています。

RANGE_CONTAINS は集合の包含判定を行います。この関数は、要素の所属判定にも利用できます。
RANGE_OVERLAPS は集合の交叉判定を行います。
RANGE_INTERSECT は集合の交叉演算を行います。ただし、結果が空集合になるときエラーになります。
区間の合併の閉包も原理的には存在しえますが、現行のBigQueryには対応する関数はありません。

区間から配列を生成する方法として以下も提供されています。

GENERATE_RANGE_ARRAY
RANGE_SESSIONIZE

RANGE の比較

RANGE の同値性 (=, !=, IN, NOT IN, GROUP BY など) は、端点の同値性で表されます。非有界の場合は端点が NULL (=UNBOUNDED) になる可能性がありますが、この場合でも同一なRANGE同士は等しいです。

RANGE の大小関係 (<, <=, >, >=, ORDER BY, BETWEEN など) は、始点と終点の辞書順で表されます。このとき始点のUNBOUNDEDは最小値、終点のUNBOUNDEDは最大値として扱われます。

SELECT * FROM UNNEST([
  -- この順にソートされる
  RANGE<DATE> '[UNBOUNDED, 2025-01-01)',
  RANGE<DATE> '[UNBOUNDED, 2025-01-02)',
  RANGE<DATE> '[UNBOUNDED, 2025-01-03)',
  RANGE<DATE> '[UNBOUNDED, 2025-01-04)',
  RANGE<DATE> '[UNBOUNDED, UNBOUNDED)',
  RANGE<DATE> '[2025-01-01, 2025-01-02)',
  RANGE<DATE> '[2025-01-01, 2025-01-03)',
  RANGE<DATE> '[2025-01-01, 2025-01-04)',
  RANGE<DATE> '[2025-01-01, UNBOUNDED)',
  RANGE<DATE> '[2025-01-02, 2025-01-03)',
  RANGE<DATE> '[2025-01-02, 2025-01-04)',
  RANGE<DATE> '[2025-01-02, UNBOUNDED)',
  RANGE<DATE> '[2025-01-03, 2025-01-04)',
  RANGE<DATE> '[2025-01-03, UNBOUNDED)',
  RANGE<DATE> '[2025-01-04, UNBOUNDED)'
]) AS x
ORDER BY x DESC