個人的によく使うTreasure Dataの時間系の関数をまとめてみた

はじめに

今回は、個人的によく使うTreasure Dataの時間系の関数をまとめてみました。結構覚えられなくて苦労してので、参考になれば嬉しいです。

今回紹介する関数は、PrestoでもHiveでも使える関数です。詳細に関しては、公式のドキュメントを確認してみてください。

個人的によく使う関数

TD_TIME_PARSE

• 文字列の時刻をunix timestampに変更する関数
• タイムゾーンのデフォルト値はUTC
  • 他の関数も同様

使い方

-- 戻り値はunix timestamp
TD_TIME_PARSE(時間(string) [, タイムゾーン(string)])

具体例

SELECT TD_TIME_PARSE('2022-08-24', 'JST')

TD_TIME_FORMAT

• unix timestampを、指定したフォーマットの時間（string）に変換する関数
  • TD_TIME_PARSEと逆の役割
• フォーマットの例はドキュメントに記載されている
  • ドキュメント
• 自分はあまり使ったことがないが、Treasure Dataではこの関数と同じ役割を持つ関数のTD_TIME_STRINGを使うことが推奨されているようだ
  • ドキュメント

使い方

-- 戻り値は時間（string）
TD_TIME_FORMAT(時間(unix_timestamp), フォーマット(文字列) [, タイムゾーン(string)])

具体例

SELECT TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM-dd HH:mm:ss', 'JST') -- クエリの実行したタイミングの時間が文字列で返ってくる
SELECT TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'yyyy-MM', 'JST') -- クエリの実行したタイミングの時間が文字列で返ってくる

TD_SCHEDULED_TIME

• Treasure Dataのスケジュール機能で登録されている時間を返す関数
• スケジュール機能を使用していない場合はクエリの実行したタイミングの時間を返す

使い方

-- 戻り値はunix timestamp
TD_SCHEDULED_TIME()

具体例

SELECT TD_TIME_PARSE(TD_SCHEDULED_TIME(), 'JST') -- クエリの実行したタイミングの時間が文字列で返ってくる

TD_TIME_RANGE

• 絶対的な期間を指定することができる関数
• 終了時刻を2022-08-24と記載した場合、集計される期間は2022-08-23 23:59:59になるので注意
• 開始時刻もしくは終了時刻をNULLとすることもできて、その場合期間の上限もしくは下限がなくなる

使い方

-- 戻り値はboolean
TD_TIME_RANGE(時間(unix_timestamp), 開始時刻(string), 終了時刻(string) [, タイムゾーン(string)])

具体例

...部分は省略している

SELECT ... WHERE TD_TIME_RANGE(unix_timestamp, '2022-08-23 JST') -- 指定される期間は[2022-08-23 00:00:00, 2022-08-24 00:00:00]            
SELECT ... WHERE TD_TIME_RANGE(unix_timestamp, '2022-08-23', '2013-08-24','JST') -- 指定される期間は[2022-08-23 00:00:00, 2022-08-24 00:00:00]
SELECT ... WHERE TD_TIME_RANGE(unix_timestamp, NULL, '2022-08-24', 'JST') -- 指定される期間は[指定なし, 2022-08-24 00:00:00]
SELECT ... WHERE TD_TIME_RANGE(unix_timestamp, '2022-08-23', NULL, 'JST') -- 指定される期間は[2022-08-23 00:00:00, 指定なし] 

TD_INTERVAL

• 引数の時間から相対的な期間を算出するための関数
• 相対的な期間の指定の仕方は、Treasure Dataのドキュメントを参考にするとわかりやすい

使い方

-- 戻り値はboolean
TD_INTERVAL(時間(unix_timestamp), 期間(string), [, タイムゾーン(string)])

具体例

...部分は省略している

SELECT ... WHERE TD_INTERVAL(TD_TIME_PARSE('2022-08-24', 'JST'), '1d') -- 指定される期間は[2022-08-24 00:00:00, 2022-08-25 00:00:00]
SELECT ... WHERE TD_INTERVAL(TD_TIME_PARSE('2022-08-24', 'JST'), '-1w') -- 指定される期間は[2022-08-15 00:00:00, 2022-08-21 00:00:00]
SELECT ... WHERE TD_INTERVAL(TD_TIME_PARSE('2022-08-24', 'JST'), '-2M') -- 指定される期間は[2022-06-01 00:00:00, 2022-08-01 00:00:00]
SELECT ... WHERE TD_INTERVAL(TD_TIME_PARSE('2022-08-24', 'JST'), '-1M/-1M') -- 指定される期間は[2022-06-01 00:00:00, 2022-07-01 00:00:00]

TD_TIME_ADD

• 引数の時間に対して、指定した期間で時間の加減ができる関数
• 期間の指定の仕方は、Treasure Dataのドキュメントを参考にするとわかりやすい

使い方

-- 戻り値はunix timestamp
TD_TIME_ADD(時間(unix_timestamp, string), 期間(string) [, タイムゾーン(string)])

具体例

...部分は省略している

SELECT TD_TIME_FORMAT(TD_TIME_ADD('2022-08-24', '1h', 'JST'), 'yyyy-MM-dd HH:mm:ss', 'JST') -- [2022-08-24 01:00:00]
SELECT TD_TIME_PARSE(TD_TIME_ADD('2022-08-24', '-1d', 'JST'), 'yyyy-MM-dd HH:mm:ss', 'JST') -- [2022-08-23 00:00:00]
SELECT TD_TIME_PARSE(TD_TIME_ADD('2022-08-24', '-2w', 'JST'), 'yyyy-MM-dd HH:mm:ss', 'JST') -- [2022-08-10 00:00:00]

TD_DATE_TRUNC

• 指定した単位で時間を丸めることのできる関数
  • 日付を月初にしたり、時刻を0時に変換することができる
• 指定できる単位はドキュメントを参考にするとわかりやすい

使い方

-- 戻り値はunix timestamp
TD_DATE_TRUNC(単位(string), 時間(unix_timestamp) [, タイムゾーン(string)])

具体例

SELECT TD_TIME_FORMAT(TD_DATE_TRUNC('week', TD_TIME_PARSE('2022-08-24')), 'yyyy-MM-dd', 'JST') -- [2022-08-22]
SELECT TD_TIME_FORMAT(TD_DATE_TRUNC('month', TD_TIME_PARSE('2022-08-24')), 'yyyy-MM-dd', 'JST') -- [2022-08-01]
SELECT TD_TIME_FORMAT(TD_DATE_TRUNC('year', TD_TIME_PARSE('2022-08-24')), 'yyyy-MM-dd', 'JST') -- [2022-01-01]