TreasureData Prestoで日付取得のまとめ

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

### 取得系

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

#### そのまま表示する場合

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

#### 加減する場合

ex. 2日前

``` sql
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するときに時間を指定すれば良いけど）

``` sql
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

``` sql
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. 初回訪問〜最終訪問日までの日数

``` sql
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
```

``` sql
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 に実行

``` sql
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で良い

``` sql
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の流用

``` sql
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時は何時でも良い）

``` sql
-- 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時間は待てない。

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