こんにちは！

みなさん、こんにちは。
遅くなりましたが、インテリジェンスAdventCalender 12/10担当のきりぽんといいます。
普段はdots.というサービスの開発をしています。

概要

今回は、TDのクエリを書いていて時間の扱いがどうにも苦手でなので、まとめてみました。
時間を扱う独自関数については
Supported Hive UDFs (User Defined Functions) | Treasure Data
を見れば一応書いてあるのですが、よく使う独自関数を後で自分で見直しやすいようにまとめなおしています。
MySQLでも時々データ取得をするので、MySQLの書き方もメモります。
自分なりに調べてやってる感じなので認識や使い方が間違ってたらごめんなさい(´・ω・｀)

TD_TIME_RANGE()

時間の範囲を指定する関数。

boolean TD_TIME_RANGE(int/long unix_timestamp,
                      int/long/string start_time,
                      int/long/string end_time
                      [, string default_timezone = 'UTC'])

select * from users where TD_TIME_RANGE(created_at, '2016-01-01', '2017-01-01', 'JST')

みたいな感じで使います。

• 第1引数
  • チェックしたい日時
  • timestampを指定
  • 指定したいカラムがtimestampになってない時は後で出てくるTD_TIME_PARSEを使う
• 第２引数
  • 開始日時
  • yyyy-MM-ddみたいに日付だけの指定もできるし、yyyy-MM-dd HH:mm:ssみたいに時間までの指定もできます
• 第３引数
  • 終了日時
  • 開始日時 <= date < 終了日時で終了日時は含まれないようなので注意
  • 指定できるフォーマットは開始日時と同じ
• 第4引数
  • タイムゾーン
  • デフォルトは標準時間'UTC'
  • 基本日本時間なので'JST'を指定
  • たぶんこれ指定しないとUTCのその時間の範囲としてチェックされるっぽいので注意

開始、終了を指定しない時は第二、第三引数をNULLとすればOK！

MySQLだとたぶん↓

column_name BETWEEN start_time AND end_time

なんですが、 BETWEEN start_time AND end_time は
start_time <= date <= end_time なので

select * from users where TD_TIME_RANGE(created_at, '2016-01-01', '2017-01-01', 'JST')

と同じ日付を指定したい場合は

BETWEEN '2016-01-01' AND '2016-12-31'

になる

TD_TIME_PARSE

文字列になっている日付をtimestampに変換する関数。

long TD_TIME_PARSE(string time
                   [, string default_timezone = 'UTC'])

select * from users where TD_TIME_RANGE(TD_TIME_PARSE(birthday, 'JST'), '1988-04-02', '1989-04-02', 'JST')

birthdayカラムの型が'yyyy-MM-dd'というstringのフォーマットと考えた場合、こんな感じで使います

• 第１引数
  • 文字列になっている日付
• 第２引数
  • タイムゾーン
  • デフォルトは標準時間'UTC'
  • 基本日本時間なので'JST'を指定
  • これをしていしないとUTCのtimestampが返ってくるから頭がこんがらがる

MySQLだとこれ

UNIX_TIMESTAMP(time)

TD_TIME_FORMAT

timestampを文字列に変換する時にフォーマット指定して変換できる関数。

string TD_TIME_FORMAT(long unix_timestamp,
                      string format
                      [, string timezone = 'UTC'])

select TD_TIME_FORMAT(created_at, 'yyyy-MM-dd'), count(id) from users group by TD_TIME_FORMAT(created_at, 'yyyy-MM-dd')

みたいな感じで、日次の集計などでよく使います。私は。

• 第１引数
  • timestampを指定
• 第２引数
  • 指定したいフォーマットの文字列？
    • 'yyyy-MM-dd'とか
    • 私はPHPerなので私は初め混乱しましたこれがこうなる
      • "Y-m-d H:i:s" => "yyyy-MM-dd HH:mm:ss"
• 第３引数
  • タイムゾーン
  • デフォルトは標準時間'UTC'
  • 基本日本時間なので'JST'を指定
  • これをしていしないとUTCの日次が返ってくるから気をつけて

頭がこんがらがる

タイムゾーンで私はなぜかハマって、データ取得するたびに「あれ、取得範囲違う？？？？」と泣いてたのですが・・
普段扱ってるサーバーはJSTに時間が合ってるので、タイムゾーンとか意識してなかったんですね。
TDを使う時はUTC時間のサーバーに向けて問い合わせてるので基準を'JST'に正すためにタイムゾーンを指定する頭を切り替えるのが大事。

今日は一旦ここまで

今日は一旦ここまでです。
もうちょっと時間のある時に他もまとめます。