標準 SQL の日時関数

BigQuery は、以下の DATETIME 関数をサポートします。

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

説明

現在時刻を DATETIME オブジェクトとして返します。

この関数では、省略可能な timezone パラメータを使用できます。タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

戻りデータの型

DATETIME

SELECT CURRENT_DATETIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 2016-05-19T10:38:47.046465 |
+----------------------------+

DATETIME

1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression, time_expression)
3. DATETIME(timestamp_expression [, timezone])

説明

  1. 年、月、日、時、分、秒を表す INT64 値を使用して DATETIME オブジェクトを構築します。
  2. DATE オブジェクトと TIME オブジェクトを使用して DATETIME オブジェクトを構築します。
  3. TIMESTAMP オブジェクトを使用して DATETIME オブジェクトを構築します。タイムゾーンを指定するには省略可能なパラメータを使用できます。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの UTC が使用されます。

戻りデータの型

DATETIME

SELECT
  DATETIME(2008, 12, 25, 05, 30, 00) as datetime_ymdhms,
  DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") as datetime_tstz;

+---------------------+---------------------+
| datetime_ymdhms     | datetime_tstz       |
+---------------------+---------------------+
| 2008-12-25T05:30:00 | 2008-12-24T21:30:00 |
+---------------------+---------------------+

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)

説明

partINT64_expr 単位を DATETIME オブジェクトに追加します。

DATETIME_ADD は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK。7 DAY と等価。
  • MONTH
  • QUARTER
  • YEAR

日付が月の最後の日である(または最後の日に近い)場合、MONTH、QUARTER、YEAR のパーツには特別な処理が必要です。結果の月の日数が元の DATETIME の日付よりも少ない場合、結果の日はその新しい月の最終日になります。

戻りデータの型

DATETIME

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later;

+-----------------------------+------------------------+
| original_date               | later                  |
+-----------------------------+------------------------+
| 2008-12-25T15:30:00         | 2008-12-25T15:40:00    |
+-----------------------------+------------------------+

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL INT64_expr part)

説明

DATETIME から partINT64_expr 単位を差し引きます。

DATETIME_SUB は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK。7 DAY と等価。
  • MONTH
  • QUARTER
  • YEAR

日付が月の最後の日である(または最後の日に近い)場合、MONTH、QUARTER、YEAR のパーツには特別な処理が必要です。結果の月の日数が元の DATETIME の日付よりも少ない場合、結果の日はその新しい月の最終日になります。

戻りデータの型

DATETIME

SELECT
  DATETIME "2008-12-25 15:30:00" as original_date,
  DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;

+-----------------------------+------------------------+
| original_date               | earlier                |
+-----------------------------+------------------------+
| 2008-12-25T15:30:00         | 2008-12-25T15:20:00    |
+-----------------------------+------------------------+

DATETIME_DIFF

DATETIME_DIFF(datetime_expression, datetime_expression, part)

説明

2 つの DATETIME オブジェクト間で、指定された part の間隔全体の数を返します。最初の DATETIME が 2 番目よりも前の場合、出力は負になります。計算によって得られた型がオーバーフローする場合、エラーをスローします。たとえば、2 つの DATETIME オブジェクト間のマイクロ秒単位の差異が INT64 値をオーバーフローする場合などです。

DATETIME_DIFF は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK: この日付パーツは日曜日から始まります。
  • WEEK(<WEEKDAY>): この日付パートは WEEKDAY から始まります。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。
  • ISOWEEK: ISO 8601 の週の境界を使用します。ISO 週は月曜日から始まります。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: ISO 8601 の週番号が付いた年の境界を使用します。ISO 年の境界は、対応するグレゴリオ暦年に木曜日が属している最初の週の月曜日です。

戻りデータの型

INT64

SELECT
  DATETIME "2010-07-07 10:20:00" as first_datetime,
  DATETIME "2008-12-25 15:30:00" as second_datetime,
  DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
    DATETIME "2008-12-25 15:30:00", DAY) as difference;

+----------------------------+------------------------+------------------------+
| first_datetime             | second_datetime        | difference             |
+----------------------------+------------------------+------------------------+
| 2010-07-07T10:20:00        | 2008-12-25T15:30:00    | 559                    |
+----------------------------+------------------------+------------------------+

SELECT
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', DAY) as days_diff,
  DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
    DATETIME '2017-10-14 00:00:00', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

上記の例は、24 時間離れている 2 つの DATETIME に対する DATETIME_DIFF の結果を示しています。DATETIME_DIFF は、これらの DATETIME の範囲内でパーツの境界の数をカウントするため、パーツ WEEK が指定された DATETIME_DIFF は 1 を返します。各 WEEK は日曜日から始まるため、2017-10-14 00:00:00 土曜日と 2017-10-15 00:00:00 日曜日の間にはパーツの境界が 1 つあります。

次の例は、異なる年の 2 つの日付に対する DATETIME_DIFF の結果を示しています。日付パーツ YEAR が指定された DATETIME_DIFF は、2 つの DATETIME 間のグレゴリオ暦年の境界の数をカウントするため、3 を返します。2 番目の DATETIME は ISO 基準の 2015 年に属しているため、日付パーツ ISOYEAR が指定された DATETIME_DIFF は 2 を返します。2015 年の最初の木曜日が 2015 年 1 月 1 日であったため、ISO 基準の 2015 年は 2014 年 12 月 29 日月曜日から始まります。

SELECT
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', YEAR) AS year_diff,
  DATETIME_DIFF('2017-12-30 00:00:00',
    '2014-12-30 00:00:00', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

次の例は、2 つの日付に対する DATETIME_DIFF の結果を連続して示しています。最初の日付は月曜日で、2 番目の日付は日曜日になります。日付パーツ WEEK が指定された DATETIME_DIFF は 0 を返します。これは、この時間パーツが日曜日で始まる週を使用するためです。日付パーツ WEEK(MONDAY) が指定された DATETIME_DIFF は 1 を返します。ISO 週は月曜日から始まるので、日付パーツ ISOWEEK が指定された DATETIME_DIFF も 1 を返します。

SELECT
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

DATETIME_TRUNC

DATETIME_TRUNC(datetime_expression, part)

説明

part の粒度で DATETIME オブジェクトを切り詰めます。

DATETIME_TRUNC は、part の次の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): datetime_expression を、WEEKDAY で週が始まる先行する週の境界に切り詰めます。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。
  • ISOWEEK: datetime_expression を、先行する ISO 8601 の週の境界に切り詰めます。ISOWEEK は月曜日に始まります。各 ISO 年の最初の ISOWEEK には、対応するグレゴリオ暦年の最初の木曜日が含まれます。これより前の date_expression を前の月曜日に切り詰めます。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: datetime_expressionISO 8601 形式の週番号が付けられた先行年の境界に切り詰めます。ISO 年の境界は、対応するグレゴリオ暦年に木曜日が属している最初の週の月曜日です。

戻りデータの型

DATETIME

SELECT
  DATETIME "2008-12-25 15:30:00" as original,
  DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;

+----------------------------+------------------------+
| original                   | truncated              |
+----------------------------+------------------------+
| 2008-12-25T15:30:00        | 2008-12-25T00:00:00    |
+----------------------------+------------------------+

次の例では、元の DATETIME は日曜日になります。partWEEK(MONDAY) であるため、DATE_TRUNC は前の月曜日の DATETIME を返します。

SELECT
 datetime AS original,
 DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP "2017-11-05 00:00:00+00", "UTC") AS datetime);

+---------------------+---------------------+
| original            | truncated           |
+---------------------+---------------------+
| 2017-11-05T00:00:00 | 2017-10-30T00:00:00 |
+---------------------+---------------------+

次の例では、元の datetime_expression はグレゴリオ暦 2015 年です。ただし、ISOYEAR 日付パーツが指定された DATETIME_TRUNC は、datetime_expression をグレゴリオ暦年ではなく、ISO 年の最初に切り詰めます。2015 年の最初の木曜日が 2015 年 1 月 1 日であったため、ISO 基準の 2015 年は 2014 年 12 月 29 日月曜日から始まります。したがって datetime_expression 2015-06-15 00:00:00 より前にある ISO 年境界は 2014-12-29 です。

SELECT
  DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;

+---------------------+----------------+
| isoyear_boundary    | isoyear_number |
+---------------------+----------------+
| 2014-12-29T00:00:00 | 2015           |
+---------------------+----------------+

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

説明

指定された format_string に従って、DATETIME オブジェクトを形式設定します。この関数でサポートされる形式設定要素のリストについては、DATETIME でサポートされる形式設定要素をご覧ください。

戻りデータの型

STRING

SELECT
  FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
  AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
SELECT
  FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT
  FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_DATETIME

PARSE_DATETIME(format_string, string)

説明

format_stringDATETIMESTRING 表記を使用して、DATETIME を返します。この関数でサポートされる形式設定要素のリストについては、DATETIME でサポートされる形式設定要素をご覧ください。

PARSE_DATETIME は、次のルールに従って string を解析します。

  • 指定されていないフィールド。指定されていないフィールドはすべて、1970-01-01 00:00:00.0 から初期設定されます。たとえば、年が指定されていない場合、デフォルトは 1970 です。
  • 大文字と小文字の区別がない名前MondayFebruary などの名前は、大文字と小文字が区別されません。
  • 空白文字。形式設定文字列内の 1 つ以上の連続する空白文字は DATETIME 文字列内の 0 個以上の連続する空白文字と同じです。DATETIME 文字列の先頭と末尾の空白は、形式設定文字列に含まれていなくても常に許可されます。
  • 形式設定の優先度。2 つ以上の形式設定要素に重複する情報がある場合、一部の例外を除き、最後の設定によって前の設定がオーバーライドされます。たとえば、%F%Y は両方とも年に影響するので、前者は後者をオーバーライドします。%s%C%y の説明については、DATETIME でサポートされる形式設定要素をご覧ください。

戻りデータの型

DATETIME

次の例では、STRING リテラルを DATETIME として解析します。

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;

+---------------------+
| datetime            |
+---------------------+
| 1998-10-18T13:45:55 |
+---------------------+

次の例では、自然言語形式の日付を含む STRING リテラルを DATETIME として解析します。

SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
  AS datetime;

+---------------------+
| datetime            |
+---------------------+
| 2018-12-19T00:00:00 |
+---------------------+

DATETIME に使用できる形式設定要素

特に指定されていない限り、形式設定文字列を使用する DATETIME 関数は次の要素をサポートします。

形式設定要素 説明
%A 完全な曜日名。
%a 省略された曜日名。
%B 完全な月の名前。
%b または %h 省略された月の名前。
%C 10 進数として表示される世紀(年を 100 で割って、整数値になるよう切り捨てる)(00~99)。
%c 日付および時刻の表記。
%D %m/%d/%y 形式の日付。
%d 10 進数として表示される、月内の日付(01~31)。
%e 10 進数として表示される、月内の日付(1~31)。1 桁の場合は前にスペースが入れられます。
%F %Y-%m-%d 形式の日付。
%G ISO 8601 の年。10 進数として表され、世紀の部分の数字が含まれます。各 ISO 年は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。グレゴリオ暦年と ISO 年が同一ではない可能性があるグレゴリオ暦年の境界付近では、%G と %Y で異なる結果が生成されることがあります。
%g ISO 8601 の年。10 進数として表され、世紀の部分の数字は含まれません(00~99)。各 ISO 年は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。%g と %y は、グレゴリオ暦年と ISO 年が異なる可能性があるグレゴリオ暦年の境界付近では異なる結果になることがあります。
%H 10 進数で表示される時間(24 時間制)(00~23)。
%I 10 進数で表示される時間(12 時間制)(00~12)。
%j 10 進数として表示される、年内の日付(001~366)。
%k 10 進数として表示される時間(24 時間制)(0~23)。1 桁の場合は前にスペースが入れられます。
%l 10 進数として表示される時間(12 時間制)(1~12)。1 桁の場合は前にスペースが入れられます。
%M 10 進数として表示される分(00~59)。
%m 10 進数として表示される月(01~12)。
%n 改行文字。
%P am または pm のいずれか。
%p AM または PM のいずれか。
%Q 10 進数として表示される四半期(1~4)。
%R %H:%M 形式の時刻。
%r AM/PM 表記を使用する 12 時間制の時刻。
%S 10 進数として表示される秒(00~60)。
%s 1970 年 1 月 1 日 00:00:00 から数えた秒数。文字列内のどこに %s が出現するかにかかわらず、他のすべての形式要素を常にオーバーライドします。複数の %s 要素が使用されている場合、最後のものが優先されます。
%T %H:%M:%S 形式の時刻。
%t タブ文字。
%U 10 進数として表示される、年内の週番号(週の 1 日目が日曜日)(00~53)。
%u 10 進数として表示される曜日(週の 1 日目が月曜日)(1~7)。
%V 1 年の中での ISO 8601 の週番号(週の 1 日目は月曜日)。10 進数として表されます(01~53)。1 月 1 日を含む週に新しい年の 4 日以上の日数がある場合、その週が week 1 になります。そうでない場合、その週は前の年の week 53 になり、その次の週が新しい年の week 1 になります。
%W 10 進数として表示される、年内の週番号(週の 1 日目が月曜日)(00~53)。
%w 10 進数として表示される曜日(週の 1 日目が日曜日)(0~6)。
%X HH:MM:SS 形式の時刻表記。
%x MM/DD/YY 形式の日付表記。
%Y 10 進数として表示される、世紀を含む年。
%y 10 進数(00-99)として表示される年。世紀は含みません。先頭に 0 を追加することもできます。%C と混在できます。%C が指定されていない場合、年 00~68 は 2000 年代、年 69~99 は 1900 年代です。
%% 単一の % 文字。
%E#S 小数第 # 位の精度で示される秒。
%E*S 完全な小数の精度で示される秒(リテラル '*')。
%E4Y 4 文字の年(0001 ... 9999)。%Y によって、年を完全に表現するために必要な数の文字が生成されます。