BigQuery 的日期／時間型別

BigQuery 入門

Matt Forrest

Field CTO

為何日期與時間重要

輕鬆篩選長期資料集
擷取日期／時間片段
分割（Partition）策略

Dates（日期）

DATE 型別範例

SELECT 
    DATE(2010, 05, 19) as launch


/*------------*
 | launch     |
 +------------+
 | 2010-05-19 |
 *------------*/

Timestamps（時間戳）

Timestamp 範例：

SELECT 
    TIMESTAMP("2010-05-19 00:00:00+00") as launch


/*-------------------------*
 | launch                  |
 +-------------------------+
 | 2010-05-19 00:00:00 UTC |
 *-------------------------*/

Datetime 與 Time（日期時間／時間）

Time 與 Datetime 範例：

SELECT
  TIME(12, 00, 00) as noon,

  DATETIME(2019, 05, 19, 00, 00, 00) as launch


/*----------+----------------------*
 | noon     | launch               |
 +----------+----------------------+
 | 12:00:00 | 2019-05-19T00:00:00  |
 *----------+----------------------*/

日期與時間戳片段（parts）

日

DAY、DAYOFWEEK、DAYOFYEAR。

週

WEEK、WEEKDAY、ISOWEEK。

月／年

MONTH、QUARTER、YEAR、ISOYEAR。

時間

HOUR、MINUTE、SECOND、MILLISECOND、MICROSECOND。

¹ https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_trunc

ADD、SUBTRACT 與 DIFF

加上 5 天

SELECT 
    DATE_ADD(DATE '2010-05-19', 
             INTERVAL 5 DAY) 
             AS five_days_later;


/*--------------------*
 | five_days_later    |
 +--------------------+
 | 2010-05-24         |
 *--------------------*/

計算差異

SELECT 
    DATE_DIFF(DATE '2010-05-24', 
             DATE '2010-05-19', DAY) 
             AS difference;


/*-------------*
 | difference  |
 +-------------+
 | 5           |
 *-------------*/

EXTRACT（擷取）

找出星期幾

SELECT 
    EXTRACT(DAYOFWEEK FROM DATE '2010-05-19') 
             AS day_of_week;


/*--------------*
 | day_of_week  |
 +--------------+
 | 4            |
 *--------------*/

FORMAT（格式化）

SELECT
    FORMAT_DATE(
      '%x', DATE '2010-05-19') 
    AS with_slashes;


/*--------------*
 | with_slashes |
 +--------------+
 | 05/19/10     |
 *--------------*/

SELECT
    FORMAT_DATE(
      '%A', DATE '2010-05-19') 
    AS dow;


/*--------------*
 | dow          |
 +--------------+
 | Wednesday    |
 *--------------*/

¹ https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time

目前日期／時間戳

取得目前的 timestamp：

SELECT
  CURRENT_TIMESTAMP(); 

/*---------------------------------*
 | current_timestamp               |
 +---------------------------------+
 | 2023-11-13 17:35:25.951432 UTC  |
 *---------------------------------*/

取得目前的日期：

SELECT
  CURRENT_DATE();

/*---------------*
 | current_date  |
 +---------------+
 | 2023-11-13    |
 *---------------*/

小抄速覽

資料型別

日期是精確的日，timestamp 是絕對的日期與時間。

日期／時間戳片段

日期／時間戳的片段（如 MONTH 或 HOUR）

加與減

用於加減日期片段的函式（如 DATE_ADD）

差異

以單位計算兩個日期的差（如 TIMESTAMP_SUB）

擷取與格式化

以 date parts 擷取日期片段並格式化日期（如 EXTRACT、FORMAT）

目前日期／時間戳

取得現在的日期或時間戳（如 CURRENT_TIMESTAMP）

一起來練習吧！

BigQuery 入門