効果的なtimeカラムの使い方

2021年1月18日

UNIX時間について

UNIX時間は、「1970年1月1日午前0時0分0秒（UTC）」からの経過秒数で表現する時間の表現方法です。UTC(Coordinated Universal Time)は世界協定時刻のことです。以前はGMT（グリニッジ標準時）が利用されておりましたが、1960年代からより精度の高いUTCが使用されるようになりました。以下の例は、UTCの時間とUNIX時間との比較です。

表1

UNIX時間のままではぱっと見た際に日付が分からないため、関数によってYYYY/MM/DDなどの書式でフォーマットを変えることが出来ます。変更方法は次の章で説明をしますが、クエリーを書く時間もないという際はutimeというChrome拡張機能が便利です。右上のウィンドウにUNIX時間を貼り付けるだけで、日時を返してくれます。

timeの加工について

TD_TIME_FORMAT

Treasure Data CDPでは、クエリーエンジンとしてPrestoとHiveの２つをサポートしており、使用できる関数もPrestoとHiveそれぞれ使用可能ですが、それ以外にTreasure Data CDP独自の関数が設定されています。

その中でよく使用する関数について記載します。
TD_TIME_FORMATは、第1引数のunix_timestampを第2引数の指定のフォーマットで、第3引数のtimezoneに計算をして表示してくれる関数で恐らく一番良く使う関数です。日付を表示する際のフォーマットは、年、月、日、時間、分、曜日などを指定することが出来るため、以下に一覧を記載します。MMやmmなど大文字と小文字で内容が変わってくるものもあるため注意が必要です。

表2

関数フォーマット

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

EX.

以下の例は、前章の表１にあるUNIX時間「1577836800」を`yyyy-MM-dd HH:mm:ss`のフォーマットで、日本時間で表示する内容です。前章の表１ではUTC時間なので0時ですが、今回はJST指定で日本時間のためプラス９時間となり、時間が9時となっております。また、月を表示する場合は大文字のM2つ（MM）で、分を表示する場合は小文字のm2つ（mm）を使用します。

SELECT
  TD_TIME_FORMAT(1577836800, 'yyyy-MM-dd HH:mm:ss', 'JST')
  →  2020-01-01 09:00:00

TD_TIME_FORMAT

yyyy/MM/dd形式の日付からUNIX時間への変換をする際に使用します。

使用タイミング

yyyy/MM/dd形式の日付をtimeに格納したい場合
TD_TIME_RANGEなどでUNIX時間が指定されている関数を使用する場合など

関数フォーマット

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

EX.

SELECT
  TD_TIME_PARSE('2020-01-01 09:00:00', 'JST')
  →  1577836800

Workflowでのtimeについて

Workflowでは、クエリでは使用できない機能としてJavascriptの関数を使用した計算が可能です。また、${変数名}のシンタックスで変数を利用する事が出来ます。 Treasure Data CDPに事前に用意されている変数は以下です。

表3

EX.

例えば、1日に1回処理するWorkflowを作成した際に、同じ日に2回処理されてしまうと困る場合があるので、2回実行された場合は同日に処理されたデータを削除して実行を行う方法があります。その場合、session_dateを使用しての以下のような記載が可能です。最初のタスク(+delete)で、session_dateで絞ったデータ(当日実行したデータ)を削除し、その後のタスク(+insert)でtimeに処理をした日付を入れております。

_export:
# データ取得元のDB名を入力
  td:
    database: db_name

+main_task:
  +delete:
    td>:
    query: |
      DELETE
      FROM
        summary_table
      WHERE
        time = ${moment(session_date).unix()}

  +insert:
    td>:
    query: |
      SELECT 
        ${moment(session_date).unix()} AS time
        , id
        , name
      FROM
        base_table
    insert_into: summary_table

今回はtimeについての基本的な部分について書きました。次回は内容を変え、新しくサービス展開を行っているTreasure Insightsについて書く予定です。