Q1. 日本時間で今朝4時をTIMESTAMP型で

Question⌗

日本時間の今朝4時(AM.04:00)をTIMESTAMP型で表現する．

SQLを書いていると，時間を条件指定して抽出することがあります．日付+時間が事前に分かっていれば指定することが簡単ですが，今日のX時や一週間前のY時というものを表現するのにベストな方法は何かなと考えてみました．

Answer⌗

クリックで表示切替

A1. DATEとTIMEからDATETIME → TIMESTAMPコンストラクタ(タイムゾーン指定)⌗

SELECT
  TIMESTAMP(
    DATETIME(CURRENT_DATE('Asia/Tokyo'), TIME(4, 0, 0)),
    'Asia/Tokyo'
  )

解説⌗

BigQueryのTIMESTAMPコンストラクタには[STRING・DATE・DATETIME]を渡すことができます．そのため，次に見るのはDATETIMEコンストラクタ．

DATETIMEリファレンスを眺めていると2. DATETIME(date_expression[, time_expression])のtime_expressionで時間を渡せることが分かります．ここで初めてTIME型を使ったのですが，TIME(4, 0, 0)で4時を表現することができます．

A1としてはDATETIMEをDATE(日本時間の今日)+TIME(4時)から生成し，それをタイムゾーン(‘Asia/Tokyo’)指定でTIMESTAMPを作ることでした．ここでタイムゾーンを指定しないとDATETIMEからTIMESTAMPに変換する時にデフォルトタイムゾーンが指定されてしまうので，UTCでBigQueryを使っていたらバグります．

A2. DATETIMEからタイムゾーンSTRING → TIMESTAMPコンストラクタ⌗

SELECT
  TIMESTAMP(
    FORMAT_DATETIME("%F 04:00:00+09", CURRENT_DATETIME('Asia/Tokyo'))
  )

解説⌗

A2ではSTRINGからTIMESTAMPを作ります．
こちらもTIMESTAMPコンストラクタを読んでいると．

string_expression[, timezone]: STRING 表現を TIMESTAMP データ型に変換します。string_expressionにはタイムスタンプのリテラルを含める必要があります。string_expressionのタイムスタンプリテラルにタイムゾーンが含まれている場合は、明示的なtimezone引数を含めないでください。

つまりはSTRINGの時間フォーマットで渡すことができ，そのSTRINGにタイムゾーン情報を埋め込めば良いということです．ということはDATETIMEをタイムゾーン指定のSTRINGで出力し，それをTIMESTAMPで読み込めば完成．

FORMAT_DATETIMEでDATETIME(日本時間)を%F 04:00:00+09の形式(%Fは%Y-%m-%dと同義)でタイムゾーン含めたSTRINGに変換し，これをTIMESTAMPコンストラクタに入れました．

感想⌗

思ったよりは難しかったです．

好みはA1.