【GoogleBigQueryTips】standardSQLで、秒・ミリ秒・マイクロ秒タイムスタンプを->日付に変換する..他

はじめに

タイムスタンプを日付に変換するというのは、分析をしているとしょっちゅう使うクエリではあるのものの、毎回忘れてしまうのでこちらに残しておきます。

特に、FirebaseAnalyticsから、BigQueryにデータをエクスポートしている場合には、"初めてアプリを開いた日"がミリ秒のタイムスタンプとして保持されているので、ユーザー分析を行う上で以下の変換を行うことは良くあるかと思います。

ご参考までに!

秒 --> 日付に変換

EXTRACT(DATE FROM TIMESTAMP_SECONDS("タイムスタンプ[秒]")　AT TIME ZONE 'Asia/Tokyo') AS date
```

## ミリ秒 --> 日付に変換
```sql:
EXTRACT(DATE FROM TIMESTAMP_MILLIS("タイムスタンプ[ミリ秒]")　AT TIME ZONE 'Asia/Tokyo') AS date
```

## マイクロ秒 --> 日付に変換
```sql:
EXTRACT(DATE FROM TIMESTAMP_MICROS("タイムスタンプ[マイクロ秒]")　AT TIME ZONE 'Asia/Tokyo') AS date
```

## おまけ
## UTCタイムスタンプ --> 　時間帯を抽出
```sql:
FORMAT_TIMESTAMP("%H", timestamp, 'Asia/Tokyo') as hour
```

## UTCタイムスタンプ --> 　　週を抽出
```sql:
EXTRACT(DAYOFWEEK FROM date(timestamp, 'Asia/Tokyo')) as week
```

##  FROM句で○○日前を指定
```sql:
FROM XXX
WHERE
 _TABLE_SUFFIX BETWEEN REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 8 day) AS string), '-', '')
  AND REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 1 day) AS string), '-', '')
```
##  timestampと本日との差分を抽出する
*以下は、年変換する事で年齢を抽出

```sql:
ROUND(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),birthday,DAY)/365,0) as AGE
```