'
Articles

標準SQLにおけるDatetime関数

author
3 minutes, 34 seconds Read

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

すべての出力はISO 8601に従って、日付と時刻をTで分離して自動的にフォーマットされます。

CURRENT_DATETIME

CURRENT_DATETIME()

説明

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

Return Data Type

DATETIME

例

SELECT CURRENT_DATETIME() as now;+----------------------------+| now |+----------------------------+| 2016-05-19T10:38:47.046465 |+----------------------------+

DATETIME

Description

  1. 年、月、日、時、分、秒を表す INT64 値を用いて DATETIME オブジェクトを構築する。
  2. DATEオブジェクトとオプションのTIMEオブジェクトを使用してDATETIMEオブジェクトを構築します。
  3. TIMESTAMPオブジェクトを使用してDATETIMEオブジェクトを構築します。 タイムゾーンを指定するオプションのパラメータをサポートしています。

データ型を返す

例

EXTRACT

EXTRACT(part FROM datetime_expression)

説明

与えられたdatetime_expressionから指定されたpartに対応する値を返します。

許容される part の値は次のとおりです：

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK: 指定された範囲の日付の週番号を返します。 週は日曜日から始まり、その年の最初の日曜日より前の日付は第0週です。
  • WEEK(<WEEKDAY>): 範囲内の日付の datetime_expression の週番号を返します。 週は WEEKDAY から始まり、その年の最初の WEEKDAY より前の datetime 日は第 0 週になります。 WEEKDAY の有効値は SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, そして SATURDAY.
  • ISOWEEK である。 datetime_expression のISO 8601週番号を返す。 ISOWEEK は月曜日から始まります。 返される値は、. 各ISO年の最初のISOWEEKは、グレゴリオ暦年の最初の木曜日の前の月曜日から始まります。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: ISO 8601週番号を持つ年を返します。これは、date_expressionが属する週の木曜日を含むグレゴリオ暦です。
  • DATE
  • TIME

返される値は、低次の時間期間を切り詰めた値です。

Return Data Type

INT64 ただし、以下の場合を除く。

  • If part is DATE, return a DATE object.
  • If part is TIME, return a TIME object., if is TIME objects.If I took a DATE, return a EXTRACT for a EXTRACT, if EXTRACT, except in these cases.For example when extracting seconds, the

    truncates the millisecond and microsecond value.The other than the following cases.

例

次の例では、EXTRACTはHOURtime partに対応する値を返す。

SELECT EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00)) as hour;+------------------+| hour |+------------------+| 15 |+------------------+

次の例では、EXTRACTはdatetimeの列から異なるtime partに対応する値を返している。 EXTRACTは最初の列を日曜日から始まる週を使って計算し、2番目の列を月曜日から始まる週を使って計算する。

DATETIME_ADD

DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)

説明

DATETIMEオブジェクトにpartの単位int64_expressionを追加する。

DATETIME_ADD は part に対して以下の値をサポートします。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEKに追加。 7 DAYs に相当します。
  • MONTH
  • QUARTER
  • YEAR

Date が月の最終日（またはそれに近い）場合、MONTH、QUARTER および YEAR 部分に特別な処理が必要です。 3562>

戻り値のデータ型

DATETIME

例

DATETIME_SUB

DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)

説明

DATETIMEからpart単位のint64_expression減算を行ないます。

DATETIME_SUB は part について以下の値をサポートしています。 7 DAYs に相当します。

  • MONTH
  • QUARTER
  • YEAR

    • MONTH, QUARTER, YEAR 部分について、その日が月の最終日（またはそれに近い）場合は特別な処理が必要です。 もし結果の月の日数が元のDATETIMEの日数より少ない場合、結果の日数は新しい月の最終日となります。

    戻り値データ型

    DATETIME

    例

    DATETIME_DIFF

    DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)

    説明

    二つのDATETIMEオブジェクト間のpart間隔（datetime_expression_a – datetime_expression_b）の整数指定数を戻す。最初のDATETIMEが二つ目より早い場合、出力結果は負の値となる。 2つのDATETIMEオブジェクトの間のマイクロ秒の差がINT64値をオーバーフローする場合など、計算が結果の型をオーバーフローする場合はエラーを投げます。

    DATETIME_DIFF は part に対して次の値をサポートします:

    • MICROSECOND
    • MILLISECOND
    • SECOND
    • MINUTE
    • HOUR
    • DAY

      • ： 日付部分が日曜日から始まります。

    • WEEK(<WEEKDAY>): この日付部分は WEEKDAY で始まります。 WEEKDAY の有効な値は SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, そして SATURDAY です。
    • ISOWEEK の場合。 ISO 8601の週番号を使用。 ISOの週は月曜日から始まります。
    • MONTH
    • QUARTER
    • YEAR
    • ISOYEAR: ISO 8601の週番号を使用します。

    戻り値データ型

    INT64

    例

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

    次の例は、異なる年の2つの日付に対するDATETIME_DIFFの結果である。 DATETIME_DIFF の日付部分が YEAR の場合、2つの DATETIME の間にあるグレゴリオ暦の境界の数を数えるので、3が返されます。 DATETIME_DIFFに日付部分ISOYEARを指定すると、2番目のDATETIMEがISO年2015に属するので、2が返されます。 2015暦年の最初の木曜日は2015-01-01なので、ISO年2015はその前の月曜日、2014-12-29から始まります。

    次の例は、2日連続の場合のDATETIME_DIFFの結果を示しています。 最初の日付は月曜日で、2番目の日付は日曜日にあたります。 DATETIME_DIFFに日付部分WEEKを指定すると、このタイムパートは日曜日から始まる週を使用するため、0が返されます。 DATETIME_DIFFに日付部分WEEK(MONDAY)を指定すると、1が返されます。

    DATETIME_TRUNC

    DATETIME_TRUNC(datetime_expression, part)

    Description

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

    DATETIME_TRUNC は part に対して以下の値をサポートしています:

    • MICROSECOND
    • MILLISECOND
    • SECOND
    • MINUTE
    • HOUR
    • DAY
    • WEEK

      • WEEK(<WEEKDAY>): 週がWEEKDAYから始まる場合、datetime_expressionを直前の週境界まで切り詰めます。 WEEKDAY の有効な値は、SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, および SATURDAY です。

    • ISOWEEK: ISO 8601 週の境界で datetime_expression を切り詰めます。 ISOWEEKは月曜日から始まります。 各ISO年の最初のISOWEEKは、対応するグレゴリオ暦の最初の木曜日を含んでいます。
    • MONTH
    • QUARTER
    • YEAR
    • ISOYEAR: datetime_expression を前の ISO 8601 週番号の年に切り詰めます。

    データ型を返す

    DATETIME

    例

    次の例では、元の DATETIME は日曜日に当たります。 part は WEEK(MONDAY) なので、DATE_TRUNC は前の月曜日の DATETIME を返す。

    次の例では、元の datetime_expression は Gregoriancalendar year 2015 にある。 しかし、ISOYEARの日付部分を持つDATETIME_TRUNCは、datetime_expressionをグレゴリオ暦ではなくISO年の始まりに切り捨てます。 2015年の最初の木曜日は2015-01-01なので、ISO年2015はその前の月曜日、2014-12-29に始まります。したがって、datetime_expression2015-06-15 00:00:00の前のISO年の境界は2014-12-29です。

    FORMAT_DATETIME

    FORMAT_DATETIME(format_string, datetime_expression)

    説明

    指定したformat_stringに従ってDATETIMEオブジェクトをフォーマットします。 3562>

    返されるデータ型

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

    LAST_DAY

    LAST_DAY(datetime_expression)

    説明

    datetime expressionから日付を含む最終日を返します。これは、一般的に月の最終日を返すために使用されます。

    オプションで、最終日を返す日付部分を指定できます。このパラメータを使用しない場合、デフォルト値は MONTH です。LAST_DAY は date_part に対して次の値をサポートしています。

    • YEAR
    • QUARTER
    • MONTH
    • WEEK.Date:Date:Date.Day(Date-Time:日).Date:Date(Date-time:日).Date:Date(Date-time)。 7 DAYs.
    • WEEK(<WEEKDAY>)に相当する。 <WEEKDAY>は週の開始日を表します。有効な値はSUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, およびSATURDAYです。 ISO 8601の週区分を使用します。 ISOの週は月曜日から始まる。
    • ISOYEAR. ISO年の境界は、木曜日が対応するグレゴリオ暦の年に属する最初の週の月曜日です。

    返すデータ型

    DATE

    例

    これらは両方とも月の最終日を返します：

    SELECT LAST_DAY(DATETIME '2008-11-25', MONTH) AS last_day+------------+| last_day |+------------+| 2008-11-30 |+------------+
    SELECT LAST_DAY(DATETIME '2008-11-25') AS last_day+------------+| last_day |+------------+| 2008-11-30 |+------------+

    これは年の最終日を返します：

    SELECT LAST_DAY(DATETIME '2008-11-25 15:30:00', YEAR) AS last_day+------------+| last_day |+------------+| 2008-12-31 |+------------+

    これは日曜日で始まる週の最後の曜日を返します。

    SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(SUNDAY)) AS last_day+------------+| last_day |+------------+| 2008-11-15 |+------------+

    月曜日から始まる週の最終日を返します:

    SELECT LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(MONDAY)) AS last_day+------------+| last_day |+------------+| 2008-11-16 |+------------+

    PARSE_DATETIME

    PARSE_DATETIME(format_string, datetime_string)

    Description

    文字列表現の時日をDATETIMEオブジェクトに変換します。

    format_string は、datetime_string がどのようにフォーマットされるかを定義する format 要素を含んでいます。 datetime_stringの各要素は、format_stringに対応する要素を持たなければなりません。 format_string の各要素の位置は、datetime_string の各要素の位置と一致しなければなりません。

    フォーマット文字列は、%Q, %a, %A, %g, %G, %j, %P, %u, %U, %V, %w そして %W を除くほとんどの format element に完全に対応しています。

    PARSE_DATETIME は以下の規則に従って string を解析します:

    • Unspecified fields(未指定フィールド)。 指定されていないフィールドはすべて1970-01-01 00:00:00.0から初期化される。 例えば、年が指定されていない場合、それは1970に初期化されます。
    • 大文字小文字を区別しない名前。 Monday や February のような名前は、大文字と小文字を区別しません。
    • 空白文字。 フォーマット文字列中の1つ以上の連続した空白は，DATETIME文字列中の0つ以上の連続した空白と一致します。
    • フォーマット文字列にない場合でも、DATETIME文字列の先頭および末尾の空白は常に許容されます。 2つ以上の format 要素が重複する情報を持つとき、いくつかの例外を除いて、一般に最後のものがそれ以前のものを上書きします。 例えば、%Fと%Yの両方が年に影響するので、最初の要素が後の要素より優先されます。

    Return Data Type

    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 |+---------------------+
    SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 PM') AS datetime+---------------------+| datetime |+---------------------+| 2018-08-30T14:23:38 |+---------------------+

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

    SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018') AS datetime;+---------------------+| datetime |+---------------------+| 2018-12-19T00:00:00 |+---------------------+

    DATETIME

    でサポートされるフォーマット要素

    特に断りのない限り、フォーマット文字列を使用する DATETIME 関数は次の要素をサポートしています：

    DATETIME

    のフォーマット要素

    Similar Posts

    コメントを残す

    メールアドレスが公開されることはありません。