読者です 読者をやめる 読者になる 読者になる

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;

そんな感じ。