標準 SQL 中的 Datetime 函式

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

範例

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)

說明

partINT64_expr 單位加到 DATETIME 物件中。

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)

說明

傳回兩個 datetime_expression 之間的 part 邊界數。 如果第一個 DATETIME 發生在第二個 DATETIME 之前,結果就是非負數。如果計算溢位結果類型就會擲回錯誤,例如兩個 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 小時之兩個 DATETIMEDATETIME_DIFF 結果。部分為 WEEKDATETIME_DIFF 會傳回 1,因為 DATETIME_DIFF 會在 DATETIME 的這個範圍中計算部分邊界數。每個 WEEK 都從星期日開始,因此在 2017-10-14 00:00:00 星期六與 2017-10-15 00:00:00 星期日之間有一個部分邊界。

以下範例顯示不同年份之兩個日期的 DATETIME_DIFF 結果。日期部分為 YEARDATETIME_DIFF 會傳回 3,因為它會計算兩個 DATETIME 之間的公曆年邊界數。日期部分為 ISOYEARDATETIME_DIFF 會傳回 2,因為第二個 DATETIME 屬於 ISO 年 2015。2015 日歷年的第一個星期四是 2015-01-01,所以 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            |
+-----------+--------------+

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

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)

說明

DATETIME 物件截斷至 part 的精細程度。

DATETIME_TRUNC 支援 part 的下列值:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>):將 datetime_expression 截斷至上一個從 WEEKDAY 開始一週的週邊界。WEEKDAY 的有效值為 SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
  • ISOWEEK:將 datetime_expression 截斷至上一個 ISO 8601 週邊界。ISOWEEK 以星期一開始。每個 ISO 年的第一個 ISOWEEK 都包含對應公曆年的第一個星期四。任何早於這一天的 date_expression 都將截斷至上一個星期一。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:將 datetime_expression 截斷至上一個 ISO 8601 週數年邊界。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-01-01,所以 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_string 和表示 DATETIMESTRING 傳回 DATETIME。如需這個函式支援的格式元素清單,請參閱 DATETIME 的支援格式元素一節。

PARSE_DATETIME 根據下列規則解析 string

  • 未指定的欄位。 任何未指定的欄位都從 1970-01-01 00:00:00.0 開始。例如,如未指定年份,則預設為 1970
  • 區分大小寫的名稱。 例如 MondayFebruary 等的名稱都區分大小寫。
  • 空白字元。 格式字串中的一或多個連續空格字元都符合 DATETIME 字串中的零或多個連續空格字元。一律允許 DATETIME 字串中的前置與尾隨空格字元,即使這些字元不在格式字串中也一樣。
  • 格式優先順序。 當二 (或多) 個格式元素具有重疊資訊時,最後一個格式元素一般會覆寫任何之前的格式元素,但有一些例外。例如,%F%Y 都會影響年份,因此較早的元素會覆寫後者。請參閱 DATETIME 的支援格式元素一節中 %s%C%y 的說明。

範例

下列範例將 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 以十進位數字 (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 算起的秒數。一律覆寫其他所有格式元素,無論 %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 年這個世紀。
%% 單一 % 字元。
%E#S 帶有分數精確度位數的秒。
%E*S 帶有完整分數精確度 (常值「*」) 的秒。
%E4Y 四個字元的年份 (0001 ... 9999)。請注意,%Y 會產生完整轉譯年份所需的字元數。
本頁內容對您是否有任何幫助?請提供意見:

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

這個網頁
需要協助嗎?請前往我們的支援網頁