標準 SQL のタイムスタンプ関数

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

注: オーバーフローが生じると、これらの関数によってランタイム エラーが返されます。結果の値は定義済みの日付とタイムスタンプの最小 / 最大値によって制限されます。

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

説明

括弧は省略可能です。この関数は、挿入されたうるう秒の前後 20 時間にわたってうるう秒を分散することで、うるう秒を処理します。 CURRENT_TIMESTAMP() は、連続し、明確であり、1 分あたり正確に 60 秒が含まれ、うるう秒に対して値を繰り返さない TIMESTAMP 値を生成します。

サポートされる入力型

該当なし

結果のデータ型

TIMESTAMP

SELECT CURRENT_TIMESTAMP() as now;

+-------------------------------+
| now                           |
+-------------------------------+
| 2016-05-16 18:12:47.145482+00 |
+-------------------------------+

EXTRACT

EXTRACT(part FROM timestamp_expression [AT TIME ZONE tz_spec])

説明

提供された timestamp_expression から指定された part に対応する INT64 値を返します。

使用可能な part 値は次のとおりです。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK: 範囲 [0, 53] 内の日付の週番号を返します。週は日曜日から始まり、年の最初の日曜日より前の日付は 0 週目です。

  • WEEK(<WEEKDAY>): 範囲 [0, 53] 内の timestamp_expression の週番号を返します。週は WEEKDAY から始まります。年の最初の WEEKDAY より前の datetime は 0 週目です。WEEKDAY の有効な値は、SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY です。

  • ISOWEEK: datetime_expressionISO 8601 週番号を返します。ISOWEEK は月曜日から始まります。戻り値は範囲 [1, 53] 内に存在します。各 ISO 年の最初の ISOWEEK は、グレゴリオ暦年の最初の木曜日より前の月曜日から始まります。

  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR: ISO 8601 週番号が付けられた年を返します。これは、date_expression が属している週の木曜日が含まれるグレゴリオ暦年です。
  • DATE
  • DATETIME
  • TIME

返される値によって、それよりも下位の期間が切り捨てられます。たとえば、秒数を抽出するとき、EXTRACT はミリ秒とマイクロ秒の値を切り捨てます。

タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

戻りデータの型

通常、INT64partDATE の場合、DATE が返されます。

次の例では、EXTRACTDAY 時間部分に対応する値を返します。

SELECT EXTRACT(DAY
  FROM TIMESTAMP "2008-12-25 15:30:00" AT TIME ZONE "America/Los_Angeles")
  AS the_day;

+------------+
| the_day    |
+------------+
| 25         |
+------------+

次の例では、EXTRACT はタイムスタンプの列とは異なる時間部分に対応する値を返します。

WITH Timestamps AS (
  SELECT TIMESTAMP '2005-01-03 12:34:56' AS timestamp UNION ALL
  SELECT TIMESTAMP '2007-12-31' UNION ALL
  SELECT TIMESTAMP '2009-01-01' UNION ALL
  SELECT TIMESTAMP '2009-12-31' UNION ALL
  SELECT TIMESTAMP '2017-01-02' UNION ALL
  SELECT TIMESTAMP '2017-05-26'
)
SELECT
  timestamp,
  EXTRACT(ISOYEAR FROM timestamp) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp) AS isoweek,
  EXTRACT(YEAR FROM timestamp) AS year,
  EXTRACT(WEEK FROM timestamp) AS week
FROM Timestamps
ORDER BY timestamp;
+------------------------+---------+---------+------+------+
| timestamp              | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56+00 | 2005    | 1       | 2005 | 1    |
| 2007-12-31 00:00:00+00 | 2008    | 1       | 2007 | 52   |
| 2009-01-01 00:00:00+00 | 2009    | 1       | 2009 | 0    |
| 2009-12-31 00:00:00+00 | 2009    | 53      | 2009 | 52   |
| 2017-01-02 00:00:00+00 | 2017    | 1       | 2017 | 1    |
| 2017-05-26 00:00:00+00 | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

次の例では、timestamp_expression は日曜日になります。EXTRACT は、日曜日から始まる週を使用して最初の列を計算し、月曜日から始まる週を使用して 2 番目の列を計算します。

WITH table AS (SELECT TIMESTAMP('2017-11-05 00:00:00') AS timestamp)
SELECT
  timestamp,
  EXTRACT(WEEK(SUNDAY) FROM timestamp) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM timestamp) AS week_monday
FROM table;

+------------------------+-------------+---------------+
| timestamp              | week_sunday | week_monday |
+------------------------+-------------+---------------+
| 2017-11-05 00:00:00+00 | 45          | 44            |
+------------------------+-------------+---------------+

STRING

STRING(timestamp_expression[, timezone])

説明

timestamp_expression を STRING データ型に変換します。タイムゾーンを指定する省略可能なパラメータを使用できます。タイムゾーンの指定方法の詳細については、タイムゾーンの定義をご覧ください。

戻りデータの型

STRING

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles") as string;

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 15:30:00-08        |
+-------------------------------+

TIMESTAMP

1. TIMESTAMP(string_expression[, timezone])
2. TIMESTAMP(date_expression[, timezone])
3. TIMESTAMP(datetime_expression[, timezone])

説明

  1. STRING 表現を TIMESTAMP データ型に変換します。

  2. DATE オブジェクトを TIMESTAMP データ型に変換します。

  3. DATETIME オブジェクトを TIMESTAMP データ型に変換します。

この関数では、タイムゾーンを指定する省略可能なパラメータを使用できます。タイムゾーンが指定されていない場合、デフォルトのタイムゾーンの UTC が使用されます。

戻りデータの型

TIMESTAMP

SELECT
  CAST(TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_str,
  CAST(TIMESTAMP(DATE "2008-12-25", "America/Los_Angeles") AS STRING) AS timestamp_date,
  CAST(TIMESTAMP(DATETIME "2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_datetime;

+------------------------+------------------------+------------------------+
| timestamp_str          | timestamp_date         | timestamp_datetime     |
+------------------------+------------------------+------------------------+
| 2008-12-25 23:30:00+00 | 2008-12-25 08:00:00+00 | 2008-12-25 23:30:00+00 |
+------------------------+------------------------+------------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

説明

タイムゾーンに関係なく、date_partint64_expression 単位をタイムスタンプに加算します。

TIMESTAMP_ADD は、date_part に対して次の値をサポートしています。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。60 MINUTE と等価。

返されるデータ型

TIMESTAMP

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS later;

+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:40:00+00 |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

説明

タイムゾーンに関係なく、タイムスタンプから date_partint64_expression 単位を減算します。

TIMESTAMP_SUB は、date_part に対して次の値をサポートしています。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。60 MINUTE と等価。

戻りデータの型

TIMESTAMP

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS earlier;

+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:20:00+00 |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression, timestamp_expression, date_part)

説明

2 つのタイムスタンプ間の、全体が指定された date_part の間隔を示す数を返します。計算によって得られた型がオーバーフローする場合、エラーをスローします。たとえば、2 つのタイムスタンプの間のマイクロ秒単位の差異が INT64 値をオーバーフローする場合などです。

TIMESTAMP_DIFF は、date_part に対して次の値をサポートしています。

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。60 MINUTE と等価。

戻りデータの型

INT64

SELECT
  TIMESTAMP "2010-07-07 10:20:00 UTC" as first_timestamp,
  TIMESTAMP "2008-12-25 15:30:00 UTC" as second_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00 UTC",
    TIMESTAMP "2008-12-25 15:30:00 UTC", HOUR) AS hours;

+------------------------+------------------------+-------+
| first_timestamp        | second_timestamp       | hours |
+------------------------+------------------------+-------+
| 2010-07-07 10:20:00+00 | 2008-12-25 15:30:00+00 | 13410 |
+------------------------+------------------------+-------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

説明

date_part の粒度までタイムスタンプを切り詰めます。

TIMESTAMP_TRUNC は、date_part に対して次の値をサポートしています。

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

TIMESTAMP_TRUNC 関数はオプションの time_zone パラメータをサポートします。このパラメータは次の date_parts に適用されます。

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>)
  • MONTH
  • QUARTER
  • YEAR

このパラメータは、デフォルトのタイムゾーン UTC 以外のタイムゾーンを切り詰めオペレーションの一部として使用する場合に使用します。

戻りデータの型

TIMESTAMP

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'UTC') as utc,
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'America/Los_Angeles') as la;

+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25 00:00:00+00 | 2008-12-25 08:00:00+00 |
+------------------------+------------------------+

次の例では、timestamp_expression のタイムゾーンのオフセットは +12 です。最初の列には、UTC 時間の timestamp_expression が表示されています。2 番目の列には、月曜日から始まる週を使用した TIMESTAMP_TRUNC の出力が表示されています。timestamp_expression は UTC では日曜日になるため、TIMESTAMP_TRUNC はそれを前の月曜日に切り詰めます。3 番目の列には、オプションのタイムゾーン定義引数の 'Pacific/Auckland' が指定された同じ関数が表示されています。この関数は、ニュージーランド夏時間を使用して timestamp_expression を切り詰めます。ここでは月曜日になります。

SELECT
  timestamp,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY)) AS utc_truncated,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY), 'Pacific/Auckland') AS nzdt_truncated
FROM (SELECT TIMESTAMP('2017-11-06 00:00:00+12') AS timestamp);

+------------------------+------------------------+------------------------+
| timestamp              | utc_truncated          | nzdt_truncated         |
+------------------------+------------------------+------------------------+
| 2017-11-05 12:00:00+00 | 2017-10-30 07:00:00+00 | 2017-11-05 11:00:00+00 |
+------------------------+------------------------+------------------------+

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

SELECT
  TIMESTAMP_TRUNC('2015-06-15 00:00:00+00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM TIMESTAMP '2015-06-15 00:00:00+00') AS isoyear_number;

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

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

説明

指定された format_string に従って、タイムスタンプを形式設定します。

この関数でサポートされる形式設定要素のリストについては、TIMESTAMP でサポートされる形式設定要素をご覧ください。

戻りデータの型

STRING

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles")
  AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 07:30:00 2008 |
+--------------------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, string[, time_zone])

説明

タイムスタンプの format_string および文字列表記を使用して、TIMESTAMP オブジェクトを返します。

PARSE_TIMESTAMP を使用する場合、次の点に注意してください。

  • 指定されていないフィールド。 指定されていないフィールドはすべて、1970-01-01 00:00:00.0 から初期設定されます。この初期設定値は、関数のタイムゾーン引数によって指定されたタイムゾーンを使用します(存在する場合)。存在しない場合、初期設定値はデフォルトのタイムゾーン UTC を使用します。たとえば、年が指定されていない場合、デフォルトで 1970 に設定されます。
  • 大文字と小文字の区別がない名前。 MondayFebruary などの名前は、大文字と小文字が区別されません。
  • 空白文字。 形式設定文字列内の 1 つ以上の連続する空白文字は、タイムスタンプ文字列内のゼロ以上の連続する空白文字と同じです。また、タイムスタンプ文字列内の先頭および末尾の空白文字は、それらが形式設定文字列内にない場合でも常に許可されます。
  • 形式設定の優先度。 2 つ以上の形式設定要素に重複する情報がある場合(たとえば、%F%Y の両方が年に影響を及ぼす場合など)、一部の例外を除き、最後の設定によって前の設定がオーバーライドされます(%s%C、および %y の説明を参照)。

この関数でサポートされる形式設定要素のリストについては、TIMESTAMP でサポートされる形式設定要素をご覧ください。

戻りデータの型

TIMESTAMP

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008", "America/Los_Angeles") as parsed;

+-------------------------+
| parsed                  |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

説明

1970-01-01 00:00:00 UTC 以降の秒数として int64_expression を解釈します。

戻りデータの型

TIMESTAMP

SELECT TIMESTAMP_SECONDS(1230219000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

説明

1970-01-01 00:00:00 UTC 以降のミリ秒数として int64_expression を解釈します。

戻りデータの型

TIMESTAMP

SELECT TIMESTAMP_MILLIS(1230219000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

説明

1970-01-01 00:00:00 UTC 以降のマイクロ秒数として int64_expression を解釈します。

戻りデータの型

TIMESTAMP

SELECT TIMESTAMP_MICROS(1230219000000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

説明

1970-01-01 00:00:00 UTC 以降の秒数を返します。これよりも高い精度のレベルは切り捨てます。

戻りデータの型

INT64

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00") as seconds;

+------------+
| seconds    |
+------------+
| 1230219000 |
+------------+

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

説明

1970-01-01 00:00:00 UTC 以降のミリ秒数を返します。これよりも高い精度のレベルは切り捨てます。

戻りデータの型

INT64

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 UTC") as millis;

+---------------+
| millis        |
+---------------+
| 1230219000000 |
+---------------+

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

説明

1970-01-01 00:00:00 UTC 以降のマイクロ秒数を返します。これよりも高い精度のレベルは切り捨てます。

戻りデータの型

INT64

SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00") as micros;

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

TIMESTAMP でサポートされる形式設定要素

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

形式設定要素 説明
%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 年。
%g 10 進数として表示される、世紀を含まない ISO 8601 年(00~99)。
%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-01-01 00:00:00 UTC 以降の秒数。文字列内で %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)。オプションの先頭ゼロを使用します。%C と混在できます。%C が指定されていない場合、年 00~68 は 2000 年代になり、年 69~99 は 1900 年代になります。
%Z タイムゾーンの名前。
%z 必要に応じて +HHMM または -HHMM の形式で示されるグリニッジ子午線からのオフセット。正の値はグリニッジよりも東側にある場所を示します。
%% 単一の % 文字。
%Ez RFC 3339 と互換性のある数値タイムゾーン(+HH:MM または -HH:MM)。
%E#S 小数第 # 位の精度で示される秒。
%E*S 完全な小数の精度で示される秒(リテラル '*')。
%E4Y 4 文字の年(0001 ... 9999)。%Y によって、年を完全に表現するために必要な数の文字が生成されます。

タイムゾーンの定義

特定の日付およびタイムスタンプ関数を使用して、デフォルトのタイムゾーンをオーバーライドし、別の値を指定できます。次の形式を使用し、UTC オフセットを指定してタイムゾーンを指定できます。

(+|-)H[H][:M[M]]

例:

-08:00
このページは役立ちましたか?評価をお願いいたします。

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

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