TreasureData 前日分のログを指定する方法
最近Treasurdataを使っています 何回か同じ事を調べたのでメモです
hiveとprestoで現在のunixtimestampの取得方法が違ったので以下に書きます
hive
select * from a where
TD_TIME_RANGE(
time,
TD_TIME_FORMAT(TD_TIME_ADD(unix_timestamp(), '-1d'), 'yyyy-MM-dd 00:00:00', 'JST'),
TD_TIME_FORMAT(unix_timestamp(), 'yyyy-MM-dd 00:00:00', 'JST'),
'JST'
)
1時間前の場合
select * from a where
TD_TIME_RANGE(
time,
TD_TIME_FORMAT(TD_TIME_ADD(unix_timestamp(), '-1h'), 'yyyy-MM-dd HH:00:00', 'JST'),
TD_TIME_FORMAT(unix_timestamp(), 'yyyy-MM-dd HH:00:00', 'JST'),
'JST'
)
presto
select * from a where
TD_TIME_RANGE(
time,
TD_TIME_FORMAT(TD_TIME_ADD(CAST(TO_UNIXTIME(NOW()) as BIGINT), '-1d'), 'yyyy-MM-dd 00:00:00', 'JST'),
TD_TIME_FORMAT(CAST(TO_UNIXTIME(NOW()) as BIGINT), 'yyyy-MM-dd 00:00:00', 'JST')
'JST'
)
1時間前の場合
select * from a where
TD_TIME_RANGE(
time,
TD_TIME_FORMAT(TD_TIME_ADD(CAST(TO_UNIXTIME(NOW()) as BIGINT), '-1h'), 'yyyy-MM-dd HH:00:00', 'JST'),
TD_TIME_FORMAT(CAST(TO_UNIXTIME(NOW()) as BIGINT), 'yyyy-MM-dd HH:00:00', 'JST')
'JST'
)