標準 SQL 中的 Date 函式

BigQuery 支援下列 DATE 函式。

CURRENT_DATE

CURRENT_DATE([time_zone])

說明

傳回指定或預設時區的目前日期。

這個函式支援選用 time_zone 參數。這個參數是代表要使用之時區的字串。如果沒有指定時區,就會使用預設時區 UTC。請參閱時區定義,瞭解如何指定時區。

如果 time_zone 參數評估為 NULL,這個函式會傳回 NULL

傳回資料類型

DATE

範例

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

說明

傳回對應於指定日期部分的值。part 必須是以下其中一項:

  • DAYOFWEEK:以星期日作為一週的第一天傳回 [1,7] 範圍中的值。
  • DAY
  • DAYOFYEAR
  • WEEK:傳回 [0, 53] 範圍中日期的週數。一週以星期日開始,而一年中第一個星期日之前的日期為第 0 週。

  • WEEK(<WEEKDAY>):傳回 [0, 53] 範圍中日期的週數。一週以 WEEKDAY 開始。一年中第一個 WEEKDAY 之前的日期為第 0 週。WEEKDAY 的有效值為 SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY

  • ISOWEEK:傳回 date_expressionISO 8601 週數。ISOWEEK 以星期一開始。傳回 [1, 53] 範圍中的值。每個 ISO 年的第一個 ISOWEEK 從公曆年第一個星期四之前的星期一開始。

  • MONTH

  • QUARTER:傳回 [1,4] 範圍中的值。

  • YEAR

  • ISOYEAR:傳回 ISO 8601 週數年,這是包含 date_expression 所屬的星期四的公曆年。

傳回資料類型

INT64

範例

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

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

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

在以下範例中,EXTRACT 會傳回接近年底的日期資料欄中時間部分的對應值。

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

在以下範例中,date_expression 在星期日。EXTRACT 會使用以星期日開始的週計算第一個資料欄,並且會使用以星期一開始的週計算第二個資料欄。

WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
  date,
  EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;

+------------+-------------+-------------+
| date       | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45          | 44          |
+------------+-------------+-------------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

說明

  1. 從表示年、月與日的 INT64 值中建構 DATE。
  2. timestamp_expression 轉換為 DATE 資料類型。它支援指定時區的選用參數。如果沒有指定時區,就會使用預設時區 (世界標準時間)。

傳回資料類型

DATE

範例

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL INT64_expr date_part)

說明

在 DATE 中加上指定時間間隔。

DATE_ADD 支援下列 date_part 值:

  • DAY
  • WEEK。等於 7 DAY
  • MONTH
  • QUARTER
  • YEAR

當日期位於 (或接近) 一個月的最後一天時,MONTH、QUARTER 與 YEAR 部分需要特殊處理。如果結果月的天數少於原始日期天數,結果日就是新月的最後一天。

傳回資料類型

DATE

範例

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL INT64_expr date_part)

說明

從 DATE 中減去指定時間間隔。

DATE_SUB 支援下列 date_part 值:

  • DAY
  • WEEK。等於 7 DAY
  • MONTH
  • QUARTER
  • YEAR

當日期位於 (或接近) 一個月的最後一天時,MONTH、QUARTER 與 YEAR 部分需要特殊處理。如果結果月的天數少於原始日期天數,結果日就是新月的最後一天。

傳回資料類型

DATE

範例

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression, date_expression, date_part)

說明

傳回兩個 date_expression 之間的 date_part 邊界數。 如果第一個日期發生在第二個日期之前,結果就是非負數。

DATE_DIFF 支援下列 date_part 值:

  • DAY
  • WEEK 這個日期部分從星期日開始。
  • WEEK(<WEEKDAY>):這個日期部分從 WEEKDAY 開始,WEEKDAY 的有效值是 SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
  • ISOWEEK:使用 ISO 8601 週邊界。ISO 週從星期一開始。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:使用 ISO 8601週數年邊界。ISO 年邊界是星期四屬於對應公曆年之第一週的星期一。

傳回資料類型

INT64

範例

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+

SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

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

以上範例顯示連續兩天的 DATE_DIFF 結果。 日期部分為 WEEKDATE_DIFF 會傳回 1,因為 DATE_DIFF 會在這個日期範圍中計算日期部分邊界數。每個 WEEK 都從星期日開始,因此在 2017-10-14 星期六與 2017-10-15 星期日之間有一個日期部分邊界。

以下範例顯示不同年份之兩個日期的 DATE_DIFF 結果。日期部分為 YEARDATE_DIFF 會傳回 3,因為它會計算兩個日期之間的公曆年邊界數。日期部分為 ISOYEARDATE_DIFF 會傳回 2,因為第二個日期屬於 ISO 年 2015。2015 日歷年的第一個星期四是 2015-01-01,所以 ISO 年 2015 在上一個 2014-12-29 星期一開始。

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

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

以下範例顯示連續兩天的 DATE_DIFF 結果。第一個日期在星期一,第二個日期在星期日。日期部分為 WEEKDATE_DIFF 會傳回 0,因為這個時間部分使用從星期日開始的週。日期部分為 WEEK(MONDAY)DATE_DIFF 會傳回 1。日期部分為 ISOWEEKDATE_DIFF 也會傳回 1,因為 ISO 週從星期一開始。

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

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

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

說明

將日期截斷至指定精細程度。

DATE_TRUNC 支援 date_part 的下列值:

  • DAY
  • WEEK
  • WEEK(<WEEKDAY>):將 date_expression 截斷至上一個從 WEEKDAY 開始一週的週邊界。WEEKDAY 的有效值為 SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
  • ISOWEEK:將 date_expression 截斷至上一個 ISO 8601 週邊界。ISOWEEK 以星期一開始。每個 ISO 年的第一個 ISOWEEK 都包含對應公曆年的第一個星期四。任何早於這一天的 date_expression 都將截斷至上一個星期一。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:將 date_expression 截斷至上一個 ISO 8601 週數年邊界。ISO 年邊界是星期四屬於對應公曆年之第一週的星期一。

傳回資料類型

DATE

範例

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

在以下範例中,原始日期在星期日。由於 date_partWEEK(MONDAY)DATE_TRUNC 會傳回上一個星期一的 DATE

SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);

+------------+------------+
| original   | truncated  |
+------------+------------+
| 2017-11-05 | 2017-10-30 |
+------------+------------+

在以下範例中,原始 date_expression 在公曆年 2015。但是,帶有 ISOYEAR 日期部分的 DATE_TRUNC 會將 date_expression 截斷至 ISO 年 (而非公曆年) 的開始。2015 日歷年的第一個星期四是 2015-01-01,所以 ISO 年 2015 在上一個 2014-12-29 星期一開始。 因此 date_expression 2015-06-15 之前的 ISO 年邊界是 2014-12-29。

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

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

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(INT64_expression)

說明

INT64_expression 解讀為從 1970-01-01 算起的天數。

傳回資料類型

DATE

範例

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

說明

根據指定的 format_string 格式化 date_expr

如需這個函式支援的格式元素清單,請參閱 DATE 的支援格式元素

傳回資料類型

STRING

範例

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

說明

使用 format_string 與日期的字串表示傳回 DATE 物件。

使用 PARSE_DATE 時,請注意下列幾點:

  • 未指定的欄位。 任何未指定的欄位都從 1970-01-01 開始。
  • 區分大小寫的名稱。 例如 MondayFebruary 等的名稱都區分大小寫。
  • 空格字元。 格式字串中的一或多個連續空格字元都符合日期字串中的零或多個連續空格字元。此外,一律允許日期字串中的前置與尾隨空格字元 -- 即使它們不在格式字串中也一樣。
  • 格式優先順序。 當二 (或多) 個格式元素具有重疊資訊 (例如 %F%Y 都影響到年) 時,最後一個格式元素一般會覆寫任何之前的格式元素。

如需這個函式支援的格式元素清單,請參閱 DATE 的支援格式元素

傳回資料類型

DATE

範例

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

說明

傳回從 1970-01-01 算起的天數。

傳回資料類型

INT64

範例

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

DATE 的支援格式元素

除非另外提及,否則使用格式字串的 DATE 函式都支援下列元素:

格式元素 說明
%A 完整星期幾名稱。
%a 縮寫的星期幾名稱。
%B 完整月份名稱。
%b 或 %h 縮寫的月份名稱。
%C 以十進位數字 (00-99) 表示的世紀 (除以 100 並截斷至整數的年份)。
%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 年可能會有差異。
%j 以十進位數字 (001-366) 表示的每年日期。
%m 以十進位數字 (01-12) 表示的月份。
%n 換行字元。
%t 定位字元。
%U 以十進位數字 (00-53) 表示的每年週數 (星期日是每週的第一天)。
%u 以十進位數字 (1-7) 表示的星期幾 (星期一是每週的第一天)。
%V 以十進位數字 (01-53) 表示的每年週數 (星期一是每週的第一天)。 如果包含 1 月 1 日的這一週在新的一年中有四天或四天以上,它就是第 1 週;否則它就是上一年的第 53 週,而下一週才是第 1 週。
%W 以十進位數字 (00-53) 表示的每年週數 (星期一是每週的第一天)。
%w 以十進位數字 (0-6) 表示的星期幾 (星期日是每週的第一天)。
%x MM/DD/YY 格式的日期表示。
%Y 以十進位數字表示的帶世紀年份。
%y 以十進位數字 (00-99) 表示的不帶世紀的年份,可選用前置零。可與 %C 混合。若未指定 %C,00-68 年屬於 2000 年這個世紀,69-99 年屬於 1900 年這個世紀。
%E4Y 四個字元的年份 (0001 ... 9999)。請注意,%Y 會產生完整轉譯年份所需之盡可能多的字元數。
本頁內容對您是否有任何幫助?請提供意見:

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

這個網頁