Edited at

TreasureData Prestoで日付取得のまとめ

More than 1 year has passed since last update.

範囲指定にするか、SELECTにするか、formatする・しないでいつも迷うので忘備録かねて整理。

他にもめぼしいものがあれば追加していきます。


SELECT

基本的には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


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などに置き換える


範囲取得

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^)