TreasureData Prestoで日付取得のまとめ #TreasureData

範囲指定にするか、SELECTにするか、formatする・しないでいつも迷うので忘備録かねて整理。
他にもめぼしいものがあれば追加していきます。

取得系

基本的にはFORMATする
RANGEの中にも使える

そのまま表示する場合

SELECT
  TD_TIME_FORMAT(time, 'yyyy-MM-dd HH:mm:ss', 'JST')
  -- ⇒ ex. 2018-06-19 15:59:41

加減する場合

ex. 2日前

SELECT
  TD_TIME_FORMAT(TD_TIME_ADD(TD_SCHEDULED_TIME(), '-2d'), 'yyyy-MM-dd HH:mm:ss', 'JST')
  -- ⇒ ex. 2018-06-17 15:59:41

正の場合は、＋をつけずに整数で！ ex. 2d
（ーをよく使うから＋のときに無意識に＋つけてしまったら動かなかった。反省）

NOW()を使いたい場合

スケジュールじゃなくて、今の時間を基準に実行したい場合
（基本的にはTD_SCHEDULED_TIMEでRUNするときに時間を指定すれば良いけど）

SELECT
  DATE_FORMAT(DATE_ADD('hour', 9, NOW()), '%Y-%m-%d %H:%i:%s')
  -- ⇒ ex. 2018-06-19 15:59:41

加減する場合は、9+24などに置き換える

文字列を日付に整形する場合

CSVそのまま突っ込んだりしたときや、TDへのデータ送信時の設定によっては
日付が文字列型になっていたり、末尾に「.000」がついてることがある
しかもそれがJSTじゃなければ大変

ex. string_created_at(VARCHAR) : 2019-02-07 00:51:27.000

SELECT
  TD_TIME_FORMAT(TD_TIME_PARSE(SUBSTR(string_created_at, 1, 19)), 'yyyy-MM-dd HH:mm:ss', 'JST') as created_at
  -- => '2019-02-07 09:51:27'

時刻いらない場合などはSUBSTRで取る文字数を変更してください

日付の差を取得したい場合

ex. 初回訪問〜最終訪問日までの日数

SELECT
  date_diff(
    'day'
    ,cast(TD_TIME_FORMAT(first_pageviews_time, 'yyyy-MM-dd', 'JST') as date)
    ,cast(TD_TIME_FORMAT(last_pageviews_time, 'yyyy-MM-dd', 'JST') as date)
  )
  -- => ex. 2

SELECT
  ROUND((last_pageviews_time - first_pageviews_time)*1.0 / (60 * 60 * 24), 1)
  -- => ex. 2.2

どちらも+1すればその日を含む（ROUNDの方は切り上げ）

特定期間の最初を取りたい場合

TD_DATE_TRUNCで第一引数の冒頭に時間を設定する
truncにもformatにもそれぞれjstが必要

ex. 2019-10-21 13:45:07 に実行

SELECT
  TD_TIME_FORMAT(TD_DATE_TRUNC('month', TD_SCHEDULED_TIME(), 'JST'), 'yyyy-MM-dd HH:mm:ss', 'JST') 
  -- => 月初 2019-10-01 00:00:00
  ,TD_TIME_FORMAT(TD_DATE_TRUNC('day', TD_SCHEDULED_TIME(), 'JST'), 'yyyy-MM-dd HH:mm:ss', 'JST')
  -- => その日の最初 2019-10-21 00:00:00
  ,TD_TIME_FORMAT(TD_DATE_TRUNC('year', TD_SCHEDULED_TIME(), 'JST'), 'yyyy-MM-dd HH:mm:ss', 'JST')
  -- => その年の最初 2019-01-01 00:00:00

範囲取得系

ex. 昨日1日間のデータを取りたいとき
→ 2日前〜1日前という条件にする

第2引数がFROM, 第3がTOなので、それぞれなければnullで良い
第3引数は「未満」なので、00:00:00で良い

TD_TIME_RANGE(
    time
    ,TD_TIME_FORMAT(TD_TIME_ADD(TD_SCHEDULED_TIME(), '-2d'), 'yyyy-MM-dd 00:00:00', 'JST')
    ,TD_TIME_FORMAT(TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d'), 'yyyy-MM-dd 00:00:00', 'JST')
    ,'JST'
)

※ TD_SCHEDULED_TIMEはtimestampであることに注意

NOW() 使う場合

SELECTの流用

TD_TIME_RANGE(
    time
    ,DATE_FORMAT(DATE_ADD('hour', 9-24, NOW()), '%Y-%m-%d %H:%i:%s')
    ,null
    ,'JST'
)

応用編：日次データを◯日分遡って集める

日次データを◯日分遡って集めたいとき。素直にやるなら…

べた書きで日付を指定して、その箇所を全部書き換えて、日数分実行
TD_SCHEDULED_TIMEにしておいて、RUNの実行時に1日ずつ変更して、日数分実行

…という方法が考えらるが、どっちも日数分実行しなきゃいけないからめんどくさい。
そんなときは、cronを設定して、1分＝1日ずつデータを出力するようにすれば、◯分でデータが取れる

ちょうど、今の「分」と取りたい「日」の値が重なりそうな場合

ex. 6/11〜20までのデータが取りたい。そして、あともうちょっとで15時01分になりそう。（15時は何時でも良い）

-- ex. cron： 「 1-20/1 15 * * * 」とする

SELECT
    CONCAT('2018-06-',TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'mm', 'JST'), ' 00:00:00')
  -- ⇒ ex. 2018-06-01 00:00:00

今の「分」と取りたい「日」の値がしばらく重ならなそうな場合

ex. 6/11〜20までのデータが取りたい。そして、今ちょうど15時20分を過ぎて15時25分になった。せっかちなのであと1時間は待てない。

-- ex. cron： 「 31-50/1 15 * * * 」とする場合

SELECT
    CONCAT('2018-06-',CAST(CAST(TD_TIME_FORMAT(TD_SCHEDULED_TIME(), 'mm', 'JST') as INTEGER) -20 as VARCHAR), ' 00:00:00')
  -- ⇒ ex. 2018-06-01 00:00:00

これでcron設定して実行すると、1分ずつ1日分のデータを吐き出してくれて超便利です (^o^)