見出し画像

【TreasureData】日時関数

パーソルビジネスプロセスデザイン BX事業本部（旧パーソルプロセス＆テクノロジー SMKT事業部）

About

  • Treasure Data には便利な日時関数が登録されています
    公式ドキュメントをサンプルコード付きで、解説します

  • 挙動やユースケースをイメージしやすくなるようにしてみました

1.TD_TIME_RANGE

  • 第 1 引数のUNIXTIMEが、特定の範囲内におさまっているかを true/false で返却します

    • start_time <= time < end_time

    • start_time に NULL をいれると、 0 と解釈します

    • end_time に NULL をいれると、 無限 と解釈します

  • WHERE句で使用するケースが多いです

構文

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

使用例

SELECT ... FROM ...
WHERE TD_TIME_RANGE(time
                , '2022-04-01'
                , '2022-04-02'
                , 'JST'
)

  • 上記のクエリは以下の条件に当てはまるデータを返します

  • 2022/04/01 00:00:00 <= time < 2022/04/02 00:00:00（日本時間）

between との違い

  • BETWEEN は終端を含みますが、TD_TIME_RANGE は end_time を含みません

WITH _times AS (
  SELECT
    time_str
    , TD_TIME_PARSE(time_str, 'JST') AS time
  FROM
    (
      VALUES
        '2020-06-23'
        , '2020-06-24'
        , '2020-06-25'
    ) AS t(time_str)
)
SELECT
  time_str
  , time BETWEEN TD_TIME_PARSE('2020-06-23', 'JST') AND TD_TIME_PARSE('2020-06-25', 'JST') AS ver_between
  , TD_TIME_RANGE(time, '2020-06-23', '2020-06-25', 'JST') AS ver_td_time_range
FROM
  _times

  • 上記のクエリは以下を返却します


2.TD_SCHEDULED_TIME

  • スケジュールクエリによって、ジョブが実行されたときの正確な時刻を返します

    • そのクエリが実行されたときのUNIXTIMEを返す関数

    • バッチクエリとして実行する場合、始めに指定した時刻が適用され、PrestoのNOW関数とは異なります

    • アドホックに実行する場合、基本的にはNOW()と同じであり、「いつ実行したと扱うか」を実行時にポップアップで指定します


3.TD_INTERVAL

  • 代用：TD_DATE_TRUNC ＋ TD_TIME_RANGE ＋ TD_TIME_ADD

  • 複雑な日付操作を必要とする相対的な時間範囲を計算するために使用されます （TD_TIME_RANGEは絶対時間範囲に使用されます）

  • TD_SCHEDULED_TIME で指定した基準日をまず TRUNC して日（週，月）の始まりの時間に整えた上で、 interval_string で指定した長さ範囲を定めます

  • WHERE句で使用するケースが多いです

  • とても便利でありがたい関数です(個人的主観)

構文

boolean TD_INTERVAL(int/long time
                    , string interval_string
                    [, string default_timezone = 'UTC']
)

使用例

※基準日 = 2016-05-22 01:00:00 日本時間

SELECT ... FROM ...
WHERE
  TD_INTERVAL(time
              , '-1d'
              , 'JST'
)

  • 上記のクエリは以下の条件に当てはまるデータを返します

  • 2016/05/21 00:00:00 <= time < 2016/05/22 00:00:00（日本時間）

とっても便利なオフセット機能

  • interval_string にstring形式の「duration / offset」を記載

  • オフセット： '-Ld/-Nd', '-Lw/-Nw', '-LM/-NM'を使用できる（L, Nは任意の整数）


4.TD_INTERVAL_RANGE

  • TD_INTERVAL_RANGEを使用して、TD_INTERVALの時間範囲を確認できます

構文

ARRAY[string start_time, string end_time] TD_INTERVAL_RANGE(string interval_string,
                                                            [, string default_timezone = 'UTC']
)

使用例

※session time = 日本時間2022年4月12日12時34分

SELECT
  TD_INTERVAL_RANGE('1d/2022-06-24',   'JST') AS col1
  , TD_INTERVAL_RANGE('-1d/2022-06-24', 'JST') AS col2
  , TD_INTERVAL_RANGE('-1d',            'JST') AS col3

  • 上記のクエリは以下を返却します


5.TD_TIME_ADD

  • 第一引数に指定されたUNIXTIMEまたは時間文字列に対して， duration 分の時間の加減算を行います

  • 時間文字列に対しても加減算がでます

  • 'year' と 'month' はサポートされていないので注意が必要です

構文

long TD_TIME_ADD(int/long/string time
                 , string duration
                 [, string default_timezone = 'UTC']
)

使用例

SELECT ... FROM ...
WHERE TD_TIME_RANGE(time
                    , '2013-01-01'
                    , TD_TIME_ADD('2013-01-01', '1d')
)

  • 上記のクエリは以下の条件に当てはまるデータを返します

  • 2013-01-01 00:00:00 UTC <= time < 2013-01-02 00:00:00 UTC

  • 下記の例で'JST'を使用する場合、'JST'を2回記載しないと結果に差異が出るので注意が必要です

SELECT ... FROM ...
WHERE TD_TIME_RANGE(time 
                    , '2013-01-01'
                    , TD_TIME_ADD('2013-01-01', '1d', 'JST')
                    , 'JST'
)


6.TD_DATE_TRUNC

  • 指定された時間をあらゆる時間単位で「切り捨て」をしてくれます

構文

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

使用例

※time = '2018-11-23 11:11:00' の UNIXTIME

SELECT
  TD_DATE_TRUNC('week',  time) 
FROM ...

  • 上記のクエリは以下のデータを返します

  • 2018-11-19 00:00:00


7.TD_TIME_PARSE

  • 日付文字列を、UNIXTIMEに変換します

構文

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


8.TD_TIME_FORMAT

  • UNIXTIMEを指定された形式のstringへ変換します

  • 利便性のため、TD_TIME_FORMATの代わりにTD_TIME_STRINGを使用することをお勧めします

構文

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

使用例

※time = '2018-11-23 11:11:00' の unixtime

SELECT TD_TIME_FORMAT(time
                      , 'yyyy-MM-dd HH:mm:ss z'
) 
FROM ...

  • 上記のクエリは以下のデータを返します

  • 2018-11-23 11:11:00 UTC


9.TD_TIME_STRING

  • UNIXTIMEをstringに変換します。

  • 利便性のため、TD_TIME_FORMATの代わりにTD_TIME_STRINGを使用することをお勧めします

  • Prestoでは動きますが、Hiveでは動きません

構文

string TD_TIME_STRING(time
                      , '(interval string)'
                      , time zone?
)

※time = '1995-06-24 12:34:56+0900' の unixtime


10.DATE_FORMAT

  • 日付 / タイムスタンプ / ストリングを、日付形式 fmt によって指定された形式のストリング値に変換します

  • この関数はMySQLの日付時刻フォーマットを使用するので注意が必要です

構文

string DATE_FORMAT(date/ timestamp/string ts
                   , string fmt
)

使用例

SELECT DATE_FORMAT(timestamp '2021-05-13 16:23:54.123', '%Y-%m-%d %W %H:%i:%s')
--> 2021-05-13 Thursday 16:23:54
SELECT DATE_FORMAT(timestamp '2021-05-01 16:23:54.123', '%y-%c-%e %l:%i:%s %p')
--> 21-5-1 4:23:54 PM

  • なお、Hiveの場合は下記となります

SELECT DATE_FORMAT('2021-05-13 16:23:54.123', 'yyyy-MM-dd EEEE HH:mm:ss')
--> 2021-05-13 Thursday 16:23:54
SELECT DATE_FORMAT('2021-05-01 16:23:54.123', 'yy-M-d h:m:s a' )
--> 21-5-1 4:23:54 PM

運用であったほんとの話

  • Presto実行の運用でMySQLの日付時刻フォーマットに気が付かず、テストがしっかりできていないと、下記現象が起こります

    • 時刻の時間部分(HH)は h が12時間表記となるため、 H で24時間表記とする

    • 時刻の分部分(mm)は m が月表記となるため、 i で分表記とする

    • 日付の年部分(YYYY)は Y が暦週の基準年で年末日付にずれが生じることがあるため、 y で年表記とする
      (同じ週に翌年の1/1を含んでいる場合のみずれが発生する)


11.Tips

  • 基本関数が理解できたところで、運用プロジェクトでよく使っていたSQLをTips形式で3つ紹介致します

11-1.日次でAppendされているテーブルから〇日～〇日に追加されたデータ何件あるか

  • 日付のみ変更して実行するようなPrestoクエリを保存して運用しました

Sample

WITH var AS (
  SELECT
  '2022-03-03' AS var1 --> FROM_DATE
  , '2022-03-04' AS var2 --> TO_DATE
)
, result AS (
  SELECT
    TD_TIME_FORMAT((time), 'yyyy-MM-dd HH:mm:ss', 'JST') AS formatted_time
    , COUNT(id) AS id
    , COUNT(DISTINCT id) AS uniq_id
  FROM test_tbl
  WHERE TD_TIME_RANGE(time, (SELECT var1 FROM var), (SELECT var2 FROM var), 'JST')
  GROUP BY TD_TIME_FORMAT((time), 'yyyy-MM-dd HH:mm:ss', 'JST')
)

  • 〇日～〇日の部分を最初に書いてあるので都度修正します(TO_DATEは含まれません)

  • 結果が欲しいテーブルをUNIONしておくと便利です

  • このクエリ、Hiveで実行すると、「Only 1 SubQuery expression is supported.」とのエラーが出ます
    (Hiveでのサブクエリ実行は1個までですと…)

11-2.今日からn日分前までのデータ

  • 昨日の05時から当日の05時までのデータをHiveで実行して取得したい

  • 判断する日付が入っているカラムは文字列型カラムです

  • TD_INTERVAL(time, '-24h/2022-04-10 05:00:00')ならすぐなのですが、文字列カラムであること、また、5時が起点になること

  • -1dと指定すると日付で丸められるため、基準時間が00:00:00になってしまうので注意が必要です

Sample

SELECT ... FROM ...
WHERE
  TD_INTERVAL(TD_TIME_PARSE(mk_updatedat, 'JST'), (SELECT var1 FROM var), 'JST')

  • Prestoで実行出来るなら11-1と同じ方法でサブクエリを宣言して、上記の方法もありです

  • TD_INTERVALはUNIXTIMEに対して、日時レンジを指定するものとなるので、TD_TIME_PARSEをしている時点で少々時間がかります

SELECT ... FROM ...
WHERE
  mk_updatedat >= CONCAT(TD_TIME_FORMAT(TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d'), 'yyyy-MM-dd', 'JST'), ' 05:00:00')
  AND mk_updatedat < CONCAT(TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM-dd', 'JST'), ' 05:00:00')

  • Hiveでの実行は直接的な辞書順比較が一番早いようです

  • 可読性は低いです

11-3.先々月の売上データ

  • よくある、アルアルネタです

  • 判断する日付が入っているカラムは文字列型カラムです

  • Hiveで実行して取得したい

  • TD_INTERVAL関数のオフセット機能を使用して、前々月データのみ取得

Sample

TD_INTERVAL(unix_timestamp(sales_date)
            , '-1M/-1M'
            , 'JST'
)

  • No.3で説明済みの関数です

  • 神様みたいな関数です(個人的主観)


12.おまけ

PrestoとHiveでのunixtimestampの取得方法

  • Presto

TD_TIME_FORMAT(TD_DATE_TRUNC('hour', CAST(TO_UNIXTIME(now()) AS BIGINT), 'JST')
               , 'yyyy-MM-dd HH:mm:ss'
               , 'JST'
)

  • Hive

TD_TIME_FORMAT(TD_DATE_TRUNC('hour', unix_timestamp(), 'JST')
               , 'yyyy-MM-dd HH:mm:ss'
               , 'JST'
)


13. まとめ

今回は、Treasure Data の日時関数をまとめてみました。 毎日触っていると、色々な気付きがあります。 また、別の視点でもご紹介できればと思います。 最後までお読みいただきありがとうございました！

最後に

SMKT事業部では、データエンジニアを募集しています。是非こちらもご覧ください。
是非こちらもご覧ください。

▽その他募集職種こちらから▽

●●●

✉ サービスに関するお問い合わせ ✉
パーソルプロセス＆テクノロジー SMKT事業部
smkt_markegr_note@persol-pt.co.jp

●●●