標準 SQL 中的 Timestamp 函式

BigQuery 支援下列 TIMESTAMP 函數。

注意:如果發生溢位,這些函式會傳回執行階段錯誤;結果值受限於定義的日期與時間戳記值下限/上限。

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

說明

括號是選用的。這個函式可在插入的閏秒前後 20 個小時範圍內模糊化閏秒,藉以處理閏秒。 CURRENT_TIMESTAMP() 可產生連續、不模糊、每分鐘為正好 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 傳回對應於指定 partINT64 值。

允許的 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 會截斷毫秒與微秒值。

請參閱時區定義,瞭解如何指定時區。

傳回資料類型

一般為 INT64。如果 partDATE 就會傳回 DATE

範例

在以下範例中,EXTRACT 會傳回對應於 DAY 時間部分的值。

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 會使用以星期日開始的週計算第一個資料欄,並且會使用以星期一開始的週計算第二個資料欄。

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)

說明

傳回兩個時間戳記之間的完整指定 date_part 間隔數。第一個 timestamp_expression 代表較晚的日期;如果第一個 timestamp_expression 早於第二個 timestamp_expression,就會輸出負數。如果計算溢位結果類型就會擲回錯誤,例如兩個時間戳記之間的毫秒差異就會使 INT64 值溢位。

TIMESTAMP_DIFF 支援 date_part 的下列值:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等於 60 MINUTE

傳回資料類型

INT64

範例

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

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

在下列範例中,由於第一個時間戳記早於第二個時間戳記,因此會輸出負數。

SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+

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_expression 截斷至上一個 ISO 8601 週數年邊界。ISO 年邊界是星期四屬於對應公曆年之第一週的星期一。

TIMESTAMP_TRUNC 函式支援選用 time_zone 參數。這個參數適用於下列 date_parts

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

如果您要使用預設時區世界標準時間以外的時區做為截斷操作的一部分,請使用這個參數。

TIMESTAMP 截斷成 MINUTEHOUR 時,TIMESTAMP_TRUNC 會判斷 TIMESTAMP 在指定 (或預設) 的時區中的民用時間,然後減去由該 TIMESTAMP 得來的分鐘和秒數 (當截斷成 HOUR 時) 或秒數 (當截斷成 MINUTE 時)。雖然這個方式在多數情況下可提供符合直覺的結果,但在日光節約轉換期間,時間未經校正的情況下,此結果不符合直覺。

傳回資料類型

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。第二個資料欄顯示使用從星期一開始的週之 TIMESTAMP_TRUNC 的輸出。 由於 timestamp_expression 在 UTC 的星期日,TIMESTAMP_TRUNC 會將它截斷至上一個星期一。第三個資料欄顯示具有選用時區定義引數「太平洋/奧克蘭」的相同函式。在這裡,函式會使用在星期一的紐西蘭夏令時間截斷 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-01-01,所以 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 等的名稱都區分大小寫。
  • 空格字元。 格式字串中的一或多個連續空格字元都符合時間戳記字串中的零或多個連續空格字元。此外,一律允許時間戳記字串中的前置與尾隨空格字元 -- 即使它們不在格式字串中也一樣。
  • 格式優先順序。 當二 (或多) 個格式元素具有重疊資訊 (例如 %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)

說明

int64_expression 解讀為從 1970-01-01 00:00:00 UTC 算起的秒數。

傳回資料類型

TIMESTAMP

範例

SELECT TIMESTAMP_SECONDS(1230219000) as timestamp;

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

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

說明

int64_expression 解讀為從 1970-01-01 00:00:00 UTC 算起的毫秒數。

傳回資料類型

TIMESTAMP

範例

SELECT TIMESTAMP_MILLIS(1230219000000) as timestamp;

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

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

說明

int64_expression 解讀為從 1970-01-01 00:00:00 UTC 算起的微秒數。

傳回資料類型

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 以十進位數字 (00-99) 表示的世紀 (除以 100 並截斷至整數的年份)。
%c 日期與時間表示。
%D %m/%d/%y 格式的日期。
%d 以十進位數字 (01-31) 表示的每月日期。
%e 以十進位數字 (1-31) 表示的每月日期;單一數字前面會加上空格。
%F %Y-%m-%d 格式的日期。
%G 以十進位數字表示之帶有世紀的 ISO 8601 年份。每一個 ISO 年開始於公曆年第一個週四之前的週一。請注意 %G 和 %Y 可能在公曆年的界線附近產生不同的結果,其中公曆年和 ISO 年可能會有差異。
%g 以十進位數字 (00-99) 表示之不帶有世紀的 ISO 8601 年份。每一個 ISO 年開始於公曆年第一個週四之前的週一。請注意 %g 和 %y 可能在公曆年的界線附近產生不同的結果,其中公曆年和 ISO 年可能會有差異。
%H 以十進位數字 (00-23) 表示的小時 (24 小時制)。
%I 以十進位數字 (01-12) 表示的小時 (12 小時制)。
%j 以十進位數字 (001-366) 表示的每年日期。
%k 以十進位數字 (0-23) 表示的小時 (24 小時制);單一數字前面會加上空格。
%l 以十進位數字 (1-12) 表示的小時 (12 小時制);單一數字前面會加上空格。
%M 以十進位數字 (00-59) 表示的分鐘。
%m 以十進位數字 (01-12) 表示的月份。
%n 換行字元。
%P am 或 pm。
%p AM 或 PM。
%R %H:%M 格式的時間。
%r 使用 AM/PM 標記法的 12 小時制時間。
%S 以十進位數字 (00-60) 表示的秒。
%s 從 1970-01-01 00:00:00 UTC 算起的秒數。一律覆寫其他所有格式元素,無論 %s 出現在字串中的哪個位置。 如果出現多個 %s 元素,以最後一個為優先。
%T %H:%M:%S 格式的時間。
%t 定位字元。
%U 以十進位數字 (00-53) 表示的每年週數 (星期日是每週的第一天)。
%u 以十進位數字 (1-7) 表示的星期幾 (星期一是每週的第一天)。
%V 以十進位數字 (01-53) 表示的每年週數 (星期一是每週的第一天)。 如果包含 1 月 1 日的這一週在新的一年中有四天或四天以上,它就是第 1 週;否則它就是上一年的第 53 週,而下一週才是第 1 週。
%W 以十進位數字 (00-53) 表示的每年週數 (星期一是每週的第一天)。
%w 以十進位數字 (0-6) 表示的星期幾 (星期日是每週的第一天)。
%X HH:MM:SS 格式的時間表示。
%x MM/DD/YY 格式的日期表示。
%Y 以十進位數字表示的帶世紀年份。
%y 以十進位數字 (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 四個字元的年份 (0001 ... 9999)。請注意,%Y 會產生完整轉譯年份所需之盡可能多的字元數。

時區定義

某些 date 與 timestamp 函式允許您覆寫預設時區並指定其他時區。您可以使用下列格式提供時區的 UTC 偏移來指定時區:

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

例如:

-08:00
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁