標準 SQL の日時関数

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

CURRENT_DATETIME

CURRENT_DATETIME([timezone])

説明

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

この関数はオプションの timezone パラメータをサポートします。タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

戻りデータの型

DATETIME

SELECT CURRENT_DATETIME() as now;

+----------------------------+
| now                        |
+----------------------------+
| 2016-05-19 10: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 オブジェクトを構築します。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-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)

説明

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

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-25 15:30:00         | 2008-12-25 15: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-25 15:30:00         | 2008-12-25 15:20:00    |
+-----------------------------+------------------------+

DATETIME_DIFF

DATETIME_DIFF(datetime_expression, datetime_expression, part)

説明

2 つの datetime_expression の間にある part の境界の数を返します。最初の DATETIME が 2 番目の DATETIME よりも前にある場合、結果は正以外の値になります。計算によって得られた型がオーバーフローする場合、エラーをスローします。たとえば、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-07 10:20:00        | 2008-12-25 15: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: ISO 8601 週の境界まで datetime_expression を切り詰めます。ISOWEEK は月曜日から始まります。各 ISO 年の最初の ISOWEEK には、対応するグレゴリオ暦年の最初の木曜日が含まれます。これより前の date_expression を前の月曜日に切り詰めます。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: ISO 8601 週番号が付けられた前の年の境界まで datetime_expression を切り詰めます。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-25 15:30:00        | 2008-12-25 00: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') AS datetime);

+---------------------+---------------------+
| original            | truncated           |
+---------------------+---------------------+
| 2017-11-05 00:00:00 | 2017-10-30 00: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-29 00: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;

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 でサポートされる形式設定要素をご覧ください。

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

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

上記のクエリは、次の出力を返します。

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

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

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

上記のクエリは、次の出力を返します。

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

戻りデータの型

DATETIME

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 10 進数として表示される、世紀を含む ISO 8601 年。各 ISO 年は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。%G と %Y は、グレゴリオ暦年と ISO 年が異なる可能性があるグレゴリオ暦年の境界付近では異なる結果になることがあります。
%g 10 進数として表示される、世紀を含まない ISO 8601 年(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 のいずれか。
%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 10 進数として表示される、年内の週番号(週の 1 日目が月曜日)(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 によって生成されます。
このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。