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

CURRENT_TIMESTAMP

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

クエリ
SELECT CURRENT_TIMESTAMP();

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

DATE_ADD

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

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

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

DATEDIFF

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

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

結果
0

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

結果
0

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

結果
-1

NOW

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

PARSE_UTC_USEC

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

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

結果
1471910400000000

UTC_USEC_TO_DAY

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

UTC_USEC_TO_WEEK(, )

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

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

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)を比較対象にできます。

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というカラムを変換したければ以下でできます。

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))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

該当月の日数を出す

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

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という関数があり、BigQueryにはWEEKという関数があり、どちらも1年のうち何周目かを返します。ただし、BigQueryでは曜日初めが日曜日しかないので別の方法を模索します。

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

# クエリ
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

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