BigQueryのunixtimeなカラムをdateに変換する
3号です。ログをBigQueryにつっこむのが流行ってます。
で、timeってカラムがunixtimestampだったとして、XX時〜YY時までのアクセスを抽出したいとします。こんな感じになります。
SELECT * FROM DATASET.TABLE where time between TIMESTAMP_TO_SEC(DATE_ADD(TIMESTAMP("2014-12-04 22:00:00"), -9, "HOUR")) and TIMESTAMP_TO_SEC(DATE_ADD(TIMESTAMP("2014-12-05 23:01:00"), -9, "HOUR")) order by time desc; time ------------ 1417788060 1417788059 1417788058
抽出結果にdatetime型を並記して表示したいときはこんな感じです。
SELECT SEC_TO_TIMESTAMP(time) as UTC, FORMAT_UTC_USEC((time + 32400) * 1000000) as JST, * FROM DATASET.TABLE where time between TIMESTAMP_TO_SEC(DATE_ADD(TIMESTAMP("2014-12-04 22:00:00"), -9, "HOUR")) and TIMESTAMP_TO_SEC(DATE_ADD(TIMESTAMP("2014-12-05 23:01:00"), -9, "HOUR")) order by time desc; UTC JST time ------------------------------------------------------------------------ 2014-12-05 14:01:00 UTC 2014-12-05 23:01:00.000000 1417788060 2014-12-05 14:00:59 UTC 2014-12-05 23:00:59.000000 1417788059 2014-12-05 14:00:58 UTC 2014-12-05 23:00:58.000000 1417788058
「現在時刻から一分以内」とかだとこんな感じ。
SELECT SEC_TO_TIMESTAMP(time) as UTC, FORMAT_UTC_USEC((time + 32400) * 1000000) as JST, * FROM DATASET.TABLE where time between TIMESTAMP_TO_SEC(DATE_ADD(CURRENT_TIMESTAMP(), -1, 'MINUTE')) and TIMESTAMP_TO_SEC(CURRENT_TIMESTAMP()) order by time desc;
そんな感じ。