【トレジャーデータ】timeを使わない場合での日付操作コマンドメモ

2018-12-07 2021-11-29

◆特徴

トレジャーデータでパーティションされるのは、デフォルト設定のtimeのみである。それ以外の項目に対して、検索をしてもインデックスがない為、高速な検索ができない。

◆課題点

過去分のデータを手動でトレジャーデータに挿入すると、timeにはデータを挿入した日付が入ってしまう為、time関数を使った日付指定が使えなくなる。

例　2018/11/1～11/3までに発生したデータを、11/4にinsertした場合、timeはすべて11/4になる

time, ymd , id

11/4, 11/1, d1

11/4, 11/2, d2

11/4, 11/3, d3

※表記の都合上、11/4としてますが、実際はUnixtimeになるので、「1541257200」(2018/11/4)が入っている。

◆対策

　上記のymdで日付範囲を指定して、データ抽出を行う場合はこんな感じで対応

SELECT 
　　*
FROM
　　table1
WHERE
  substr(ymd, 1, 10) >= TD_TIME_FORMAT(TD_TIME_ADD(TD_SCHEDULED_TIME(), '-7d'), 'yyyy-MM-dd', 'JST')

-- timestamp型のデータから日付操作を行う方法
SELECT 
  updated_at
  , CAST(updated_at AS timestamp)  -- timestamp型に変換
  , DATE_ADD('hour' , 9 , CAST(updated_at AS timestamp)) -- updated_atの9時間後のデータを取得(UTC→JST変換などで利用）
  , DATE_ADD('day' , 3 , CAST(updated_at AS timestamp))  -- updated_atの3日後のデータを取得
  , DATE_ADD('month', 1, CAST(updated_at AS timestamp)) -- updated_atの1ヶ月後のデータを取得
FROM
  table1