Bigquery | 日付・時間系のクエリまとめ ( query reference 日本語訳 )

CURRENT_DATE

現在の日付を返します。
フォーマットは 年-月-日 です。

Returns a human-readable string of the current date in the format %Y-%m-%d.

SELECT CURRENT_DATE();

結果: 2013-02-01

CURRENT_TIME

サーバーの現在時刻を、読みやすい形で返します。
フォーマットは 時-分-秒 の形です。

Returns a human-readable string of the server's current time in the format %H:%M:%S.

SELECT CURRENT_TIME();

結果: 01:32:56

CURRENT_TIMESTAMP

サーバーの現在時刻を、タイムスタンプで返します。
フォーマットは 年-月-日 時:分:秒 です。

Returns a TIMESTAMP data type of the server's current time in the format %Y-%m-%d %H:%M:%S.

SELECT CURRENT_TIMESTAMP();
結果: 2013-02-01 01:33:35 UTC

DATE(<タイムスタンプ>)

タイムスタンプを与えると、日付を返します。
フォーマットは 年-月-日 の形です。

Returns a human-readable string of a TIMESTAMP data type in the format %Y-%m-%d.

SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));

結果: 2012-10-01

DATE_ADD (<タイムスタンプ>, <期間>, <期間の種類>)

タイムスタンプに指定した期間を加えます。
「期間の種類」には年、月、日、時、分、秒を与えることが出来ます。

もし「期間」にマイナスを与えた場合は、その分を減らします。

Adds the specified interval to a TIMESTAMP data type. Possible interval_units values include YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. If interval is a negative number, the interval is subtracted from the TIMESTAMP data type.

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");

結果: 2017-10-01 02:03:04 UTC

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");

結果: 2007-10-01 02:03:04 UTC

DATEDIFF(<タイムスタンプ１>,<タイムスタンプ２>)

二つのタイプスタンプを与えると、「日付の差」を整数で返します。

Returns the number of days between two TIMESTAMP data types.

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

結果: 466

DAY(<タイムスタンプ>)

タイムスタンプを与えると、1 から 31 までの整数で「日」を返します。

Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.

SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));

結果: 2

DAYOFWEEK(<タイムスタンプ>)

タイムスタンプを与えると、それが「週の中での何日目か」を返します。
得られるのは 1(日曜日)から7(土曜日)の整数です。

Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.

SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));

結果: 2

DAYOFYEAR(<タイムスタンプ>)

タイムスタンプを与えると、それが「年の何日目か」を 1 から 366 の整数で返します。
たとえば「1」は「1月1日」を表します。

( * 訳注 ... 366があるのは「うるう年」だけです )

Returns the day of the year of a TIMESTAMP data type as an integer between 1 and 366, inclusively. The integer 1 refers to January 1.

SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));

結果: 275

FORMAT_UTC_USEC(<\UNIX形式のタイムスタンプ>)

UNIXのタイムスタンプを与えると、年-月-日 時:分:秒.uuuuuu の形で返します。

Returns a human-readable string representation of a UNIX timestamp in the format YYYY-MM-DD HH:MM:SS.uuuuuu.

SELECT FORMAT_UTC_USEC(1274259481071200);

結果: 2010-05-19 08:58:01.071200

HOUR(<タイムスタンプ>)

タイムスタンプを与えると、0 から 23 までの整数で「時」を返します。

Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.

SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));

結果: 5 ( 訳注 5時という意味 )

MINUTE(<タイムスタンプ>)

タイムスタンプを与えると、0 から 59 の整数で「分」を返します。

Returns the minutes of a TIMESTAMP data type as an integer between 0 and 59, inclusively.

SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));

結果: 23

MONTH(<タイムスタンプ>)

タイムスタンプを与える、0 から 12 までの整数で「月」を返します。

Returns the month of a TIMESTAMP data type as an integer between 1 and 12, inclusively.

SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));

結果: 10

MSEC_TO_TIMESTAMP(<UNIXタイムスタンプ>)

UNIXタイムスタンプ形式でミリ秒を与えると、タイムスタンプを返します。

Converts a UNIX timestamp in milliseconds to a TIMESTAMP data type.

SELECT MSEC_TO_TIMESTAMP(1349053323000);

結果: 2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

結果: 2012-10-01 01:02:04 UTC

NOW()

「UNIXのタイムスタンプ」をマイクロ秒で返します。

Returns a UNIX timestamp in microseconds.

SELECT NOW();

結果: 1359685811687920

PARSE_UTC_USEC(<日付>)

年-月-日 時:分:秒[.uuuuuu] の形式で日付を与えると、マイクロ秒を返します。
[.uuuuuu] の部分は省略できます。

ちなみに TIMESTAMP_TO_USEC も同じ機能ですが、文字列の代わりにタイムスタンプを受け取るところが違います。

Converts a date string to a UNIX timestamp in microseconds. date_string must have the format YYYY-MM-DD HH:MM:SS[.uuuuuu].
The fractional part of the second can be up to 6 digits long or can be omitted.

TIMESTAMP_TO_USEC is an equivalent function that converts a TIMESTAMP data type argument instead of a date string.

SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");

結果: 1349056984000000

QUARTER(<タイムスタンプ>)

タイムスタンプを与えると、1 から ４ までの整数で「四半期」を返します。

Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.

SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));

結果: 4

SEC_TO_TIMESTAMP(<UNIXタイムスタンプ>)

UNIX形式のタイムスタンプを、タイムスタンプに変換します。

Converts a UNIX timestamp in seconds to a TIMESTAMP data type.

SELECT SEC_TO_TIMESTAMP(1355968987);

結果: 2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

結果: 2012-12-20 02:03:07 UTC

SECOND(<タイムスタンプ>)

タイムスタンプを与えると、「秒」を 0 から 59 までの整数で返します。
ただし、うるう秒が含まれる場合は 0 から 60 までの整数になります。

Returns the seconds of a TIMESTAMP data type as an integer between 0 and 59, inclusively.
During a leap second, the integer range is between 0 and 60, inclusively.

SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));

結果: 48

STRFTIME_UTC_USEC(<UNIX形式のタイムスタンプ>, <日時の形式>)

指定した形式で日時を返します。

「日時の形式」には、ハイフンやスラッシュのような区切り文字。
そして C++ の strftime で使われる特殊文字を使うことが出来ます。( 例: %d は日を表します )

Returns a human-readable date string in the format date_format_str. date_format_str can include date-related punctuation characters (such as / and -) and special characters accepted by the strftime function in C++ (such as %d for day of month).

# TRANSRATEME:

Use the UTC_USEC_TO_ functions if you plan to group query data by time intervals, such as getting all data for a certain month, because the functions are more efficient.

SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");

結果: 2010-05-19

TIME(<タイムスタンプ>)

タイムスタンプを与えると、時:分:秒 というフォーマットで文字列を返します。

Returns a human-readable string of a TIMESTAMP data type, in the format %H:%M:%S.

SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));

結果: 02:03:04

TIMESTAMP(<日付>)

日付を文字列で与えると、タイムスタンプに変換します。

Convert a date string to a TIMESTAMP data type.

SELECT TIMESTAMP("2012-10-01 01:02:03");

結果: 2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<timestamp>)

タイムスタンプをUNIXのミリ秒に変換します。

SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));

結果: 1349053323000

TIMESTAMP_TO_SEC(<timestamp>)

タイムスタンプをUNIXの秒に変換します。

SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));

結果: 1349053323

TIMESTAMP_TO_USEC(<タイムスタンプ>)

Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.
PARSE_UTC_USEC is an equivalent function that converts a data string argument instead of a TIMESTAMP data type.

SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));

結果: 1349053323000000

USEC_TO_TIMESTAMP(<expr>)

Converts a UNIX timestamp in microseconds to a TIMESTAMP data type.

SELECT USEC_TO_TIMESTAMP(1349053323000000);

結果: 2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

結果: 2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY(<UNIX形式のタイムスタンプ>)

# TRANSRATEME:

UNIXタイムスタンプでのミリ秒を、その日の始まりのものに変換します。
たとえば、May 19th at 08:58 のUNIXタイムスタンプを与えると、May 19th at 00:00 (深夜) のUNIXタイムスタンプを返します。

Shifts a UNIX timestamp in microseconds to the beginning of the day it occurs in.
For example, if unix_timestamp occurs on May 19th at 08:58, this function returns a UNIX timestamp for May 19th at 00:00 (midnight).

SELECT UTC_USEC_TO_DAY(1274259481071200);

結果: 1274227200000000

UTC_USEC_TO_HOUR(<UNIXタイムスタンプ>)

UNIXタイムスタンプを与えると、その「時」の最初のマイクロ秒を返します。
たとえば、与えたUNIXタイムスタンプが 8:58 のものであれば、同日の 8:00 のマイクロ秒を返します。

Shifts a UNIX timestamp in microseconds to the beginning of the hour it occurs in.
For example, if unix_timestamp occurs at 08:58, this function returns a UNIX timestamp for 08:00 on the same day.

SELECT UTC_USEC_TO_HOUR(1274259481071200);

結果: 1274256000000000

UTC_USEC_TO_MONTH(<UNIXタイムスタンプ>)

UNIXタイムスタンプをマイクロ秒で与えると。
「その月の最初のマイクロ秒」を返します。

Shifts a UNIX timestamp in microseconds to the beginning of the month it occurs in.
For example, if unix_timestamp occurs on March 19th, this function returns a UNIX timestamp for March 1st of the same year.

SELECT UTC_USEC_TO_MONTH(1274259481071200);

結果: 1272672000000000

UTC_USEC_TO_WEEK(<UNIXタイムスタンプ>,<曜日>)

UNIXタイムスタンプをマイクロ秒で返します。

一番目には「UNIXタイムスタンプ」をマイクロ秒で。
二番目には「曜日」を 0(日曜日) から 6(土曜日) の間で与えてください。

たとえばUNIXタイムスタンプに 2008年4月11日(金曜)を与えて。
曜日に2(火曜日)を与えたとしたら。

2008年4月18日(火曜日)のUNIXマイクロ秒を返します。

Returns a UNIX timestamp in microseconds that represents a day in the week of the unix_timestamp argument.
This function takes two arguments: a UNIX timestamp in microseconds, and a day of the week from 0 (Sunday) to 6 (Saturday).

For example, if unix_timestamp occurs on Friday, 2008-04-11, and you set day_of_week to 2 (Tuesday), the function returns a UNIX timestamp for Tuesday, 2008-04-08.

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

結果: 1207612800000000

UTC_USEC_TO_YEAR(<UNIXタイムスタンプ>)

UNIXタイムスタンプを与えると、その年初のUNIXタイムスタンプを、ミリ秒で返します。

たとえば UNIXタイムスタンプが2010年のものならば、1274259481071200 を返します。
これは 2010-01-01 00:00 の最初のタイムスタンプです。

Returns a UNIX timestamp in microseconds that represents the year of the unix_timestamp argument.
For example, if unix_timestamp occurs in 2010, the function returns 1274259481071200, the microsecond representation of 2010-01-01 00:00.

SELECT UTC_USEC_TO_YEAR(1274259481071200);

結果: 1262304000000000

WEEK(<タイムスタンプ>)

タイムスタンプを与えると、1から53までの整数で「週」を返します。

この「週」というのは、日曜日が始まりです。

なのでたとえば、1月1日が日曜日以外であれば「1」の週には7日未満しかありません。
この場合、最初の日曜日は、「2」の週に含まれることになります。

Returns the week of a TIMESTAMP data type as an integer between 1 and 53, inclusively.
Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.

SELECT WEEK(TIMESTAMP('2014-12-31'));

結果: 53

YEAR(<タイムスタンプ>)

タイムスタンプを与えると年を返します。

Returns the year of a TIMESTAMP data type.

SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));

結果: 2012

補足

• 2016年04月時点の訳です
• 公式ガイド ( https://cloud.google.com/bigquery/query-reference ) の翻訳です。
• やや意訳的です。
• もし間違いがあれば編集クエストをお送りください。

チャットメンバー募集

何か質問、悩み事、相談などあればLINEオープンチャットもご利用ください。

メンター受付