BigQueryで時間系のクエリを試してみる

よく使いそうな時間系のクエリを試してみます。特にKPIを表示する場合は日本時間に変換しなければならないのでそのための処理も書きます。
随時あれば足していきます。

# CURRENT_TIMESTAMP

現在のタイムスタンプを表示

```sql
クエリ
SELECT CURRENT_TIMESTAMP();

結果
2016-08-23 01:50:31 UTC	
```

# DATE_ADD

時間を足したり引いたりする。正負をつけることによって過去や未来を表せる

```sql
１日前
SELECT DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY');

結果
2016-08-22 01:55:05 UTC	
```

# DATEDIFF

２つのタイムスタンプの日付の違いを出す

```sql
同じ日
SELECT DATEDIFF(CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())

結果
0
```

```sql
同じ日
SELECT DATEDIFF(CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())

結果
0
```

```sql
次の日
SELECT DATEDIFF(TIMESTAMP('2016-8-23 05:23:48'), TIMESTAMP('2016-08-24 12:18:35'));

結果
-1
```

# NOW

UNIX タイムスタンプを μ 秒単位で返します

# PARSE_UTC_USEC

日時文字列をUNIXタイムスタンプで μ 秒単位で返します。タイムゾーンはUTCになるので注意。

```sql
該当の日付のunixtimeを出す
SELECT PARSE_UTC_USEC("2016-08-23");

結果
1471910400000000
```

# UTC_USEC_TO_DAY

μ 秒単位の UNIX タイムスタンプをシフトして、その日の 0 時 0 分 0 秒に変換します

# UTC_USEC_TO_WEEK(<unix_timestamp>, <day_of_week>)

その日が含まれる週の特定の曜日のUSECを返す。

例えば、2017年4月8日(土)で日曜日を返すようなクエリを以下のようにかくと`2017-04-02`がかえる。<day_of_week>は日曜日が0なので、月曜日を返したい場合は1にすればいい。

```sql
SELECT 
    DATE(UTC_USEC_TO_WEEK(
      TIMESTAMP_TO_USEC(TIMESTAMP('2017-04-08 00:00:00 UTC')), 
      0
    ))
```

# 実践編

ここでは実際にKPIのグラフを作成するときに使った関数の組み合わせをケースにしたがって紹介します。

## 直近7日にログインしたユーザIDの一覧を出したい

signed_in_atというカラムにUTCの文字列(例: 2016-08-09T03:11:45.271Z)で時間が入っているとします。
このカラムは最後のログインした時間が入っています。

以下のようにすると、当日の0時のμ 秒単位の UNIX タイムスタンプがでます。```9 - 24 * 0```となっている点で0の部分をかえるとX日前が表現できます。こちらにWHERE句で、```UTC_USEC_TO_DAY(signed_in_at)```を比較対象にできます。

```sql
USEC_TO_TIMESTAMP((UTC_USEC_TO_DAY(TIMESTAMP_TO_USEC(DATE_ADD(CURRENT_TIMESTAMP(), 9 - 24 * 0, 'HOUR')))
- 9 * 60 * 60 * 1000 * 1000))
```

## UTCのタイムスタンプで入っている値をJSTにして表示する

signed_in_atというカラムを変換したければ以下でできます。

```sql
DATE(USEC_TO_TIMESTAMP(PARSE_UTC_USEC(signed_in_at) + 9 * 3600 * 1000 * 1000))
または
DATE(USEC_TO_TIMESTAMP(PARSE_UTC_USEC(signed_in_at) + 32400000000))
```

## 日付の文字列と現在との日数の差を調べる

登録日から何日たっていたとかが調べられます。

```sql
DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));
```

## 日付文字列から年月を取得する

dateを日付文字列(例えば"2016-01-01")だとすると以下です

```sql
STRFTIME_UTC_USEC(PARSE_UTC_USEC("2016-01-01"), "%Y-%m")
```

## 年月日の文字列からその月の月初を出す

たとえば`2017-01-30`なら`2017-01-01`と返すようにします

```sql
SELECT DATE(UTC_USEC_TO_MONTH(PARSE_UTC_USEC("2017-01-30")))
```

## 年月日の文字列からその月の最後の日を出す

月初を出してから、次の月を出すために`DATE_ADD`で1ヶ月ずらし、その前日をとっています。

```sql
SELECT DATE(DATE_ADD(DATE_ADD(USEC_TO_TIMESTAMP(UTC_USEC_TO_MONTH(PARSE_UTC_USEC("2017-01-30"))), 1, "MONTH"), -1, "DAY"))
```

## 日本時間で、次の日の日付を出す

```sql
SELECT DATE(DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(), 9, 'HOUR'), 1, 'DAY'))
```

## 該当月の日数を出す

例えば、2017年3月なら31日まであるので、31と出せるようにします。
その月の最後の日を出してからDAY関数で何日目か出します。

```sql
SELECT DAY(DATE(DATE_ADD(DATE_ADD(USEC_TO_TIMESTAMP(UTC_USEC_TO_MONTH(PARSE_UTC_USEC("2017-03-01"))), 1, "MONTH"), -1, "DAY")))
```

## 週別(週の初めは月曜日)でデータを出す

MySQLには[WEEKOFYEAR](https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html)という関数があり、BigQueryには[WEEK](https://cloud.google.com/bigquery/docs/reference/legacy-sql#week)という関数があり、どちらも1年のうち何周目かを返します。ただし、BigQueryでは曜日初めが日曜日しかないので別の方法を模索します。

今回は、`UTC_USEC_TO_WEEK`を使いその週の月曜日の日付を出します。2017/4/9は日曜日なのですがそこまでは2017/4/3が結果として返ります。これで週ごとにgroup byなどができます。

```sql
# クエリ
SELECT 
  *
FROM
  (SELECT 
    DATE(UTC_USEC_TO_WEEK(
      TIMESTAMP_TO_USEC(TIMESTAMP('2017-04-08 00:00:00 UTC')), 
      1
    ))),
  (SELECT
    DATE(UTC_USEC_TO_WEEK(
      TIMESTAMP_TO_USEC(TIMESTAMP('2017-04-09 00:00:00 UTC')), 
      1
    ))),
  (SELECT
    DATE(UTC_USEC_TO_WEEK(
      TIMESTAMP_TO_USEC(TIMESTAMP('2017-04-10 00:00:00 UTC')), 
      1
    )))

# 結果
2017-04-03	 
2017-04-03	 
2017-04-10
```



# 参考
* https://cloud.google.com/bigquery/query-reference
* http://bqjpreference.appspot.com/#datetimefunctions
* http://qiita.com/Yinaura/items/303d6a563e1625d9f87d