标准 SQL 中的日期函数

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   |
+--------------+

如果存在名为 current_date 的列，则列名称和不带英文括号的函数调用有歧义。如需确保函数调用，请添加英文括号；如需确保列名称，请使用范围变量限定列名称。例如，以下查询选择 the_date 列中的函数和 current_date 列中的表列。

WITH t AS (SELECT 'column value' AS `current_date`)
SELECT current_date() AS the_date, t.current_date FROM t;

+------------+--------------+
| the_date   | current_date |
+------------+--------------+
| 2016-12-25 | column value |
+------------+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

说明

返回与指定日期部分相对应的值。part 必须是以下其中一项：

  • DAYOFWEEK：返回 [1,7] 范围内的值，其中星期日是一周的第一天。
  • DAY
  • DAYOFYEAR
  • WEEK：返回日期属于一年中的第几周，范围为 [0, 53]。一周从星期日开始算起。一年第一个星期日之前的日期属于第 0 周。
  • WEEK(<WEEKDAY>)：返回日期属于一年中的第几周，范围为 [0, 53]。一周从 WEEKDAY 开始算起。一年之中第一个 WEEKDAY 之前的日期属于第 0 周。WEEKDAY 的有效值包括 SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY 和 SATURDAY。
  • ISOWEEK：以 ISO 8601 格式返回 date_expression 属于一年之中的第几周。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])
3. DATE(datetime_expression)

说明

  1. 使用表示年月日的 INT64 值构造 DATE。
  2. 从 TIMESTAMP 表达式中提取 DATE。该函数支持使用可选参数来指定时区。如果您未指定时区，则系统会使用默认时区（即世界协调时间 (UTC)）。
  3. 从 DATETIME 表达式中提取 DATE。

返回数据类型

DATE

示例

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

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

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression 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_expression 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_a, date_expression_b, date_part)

说明

返回两个 DATE 对象 (date_expression_a - date_expression_b) 之间完整指定的 date_part 间隔数。如果第一个 DATE 早于第二个，那么输出为负数。

DATE_DIFF 支持以下 date_part 值：

  • DAY
  • WEEK：此日期部分从星期日开始算起。
  • WEEK(<WEEKDAY>)：此日期部分从 WEEKDAY 开始算起。WEEKDAY 的有效值包括 SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY 和 SATURDAY。
  • 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 结果。 日期部分为 WEEK 的 DATE_DIFF 会返回 1，因为 DATE_DIFF 会计算此日期范围内日期部分边界值的数量。每个 WEEK 都从星期日开始算起，因此在 2017-10-14 星期六与 2017-10-15 星期日之间有一个日期部分的边界值。

以下示例显示了不同年份中两个日期的 DATE_DIFF 结果。日期部分为 YEAR 的 DATE_DIFF 会返回 3，因为它会计算两个日期之间的公历年边界值数量。日期部分为 ISOYEAR 的 DATE_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 结果。第一个日期是星期一，第二个日期是星期日。日期部分为 WEEK 的 DATE_DIFF 会返回 0，因为此日期部分使用从星期日开始算起的周。日期部分为 WEEK(MONDAY) 的 DATE_DIFF 会返回 1。日期部分为 ISOWEEK 的 DATE_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 的有效值包括 SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY 和 SATURDAY。
  • 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_part 是 WEEK(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   |
+------------+

SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+

SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

LAST_DAY

LAST_DAY(date_expression[, date_part])

说明

从日期表达式返回最后一天。此函数通常用于返回当月的最后一天。

您可以选择指定返回最后一天的日期部分。如果不使用此参数，则默认值为 MONTH。LAST_DAY 支持以下 date_part 值：

  • YEAR
  • QUARTER
  • MONTH
  • WEEK。等于 7 DAY。
  • WEEK(<WEEKDAY>)。<WEEKDAY> 表示一周的起始日。有效值为 SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY、SATURDAY。
  • ISOWEEK。使用 ISO 8601 格式的周边界值。ISO 格式的周从星期一开始。
  • ISOYEAR。使用 ISO 8601 格式的周编号年份边界值。ISO 格式年份的边界值是其星期四属于相应公历年的第一周的星期一。

返回数据类型

DATE

示例

以下两条语句都会返回当月的最后一天：

SELECT LAST_DAY(DATE '2008-11-25', MONTH) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-30 |
+------------+

SELECT LAST_DAY(DATE '2008-11-25') AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-30 |
+------------+

以下语句会返回当年的最后一天：

SELECT LAST_DAY(DATE '2008-11-25', YEAR) AS last_day

+------------+
| last_day   |
+------------+
| 2008-12-31 |
+------------+

以下语句会返回从星期日开始的一周的最后一天：

SELECT LAST_DAY(DATE '2008-11-10', WEEK(SUNDAY)) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-15 |
+------------+

以下语句会返回从星期一开始的一周的最后一天：

SELECT LAST_DAY(DATE '2008-11-10', WEEK(MONDAY)) AS last_day

+------------+
| last_day   |
+------------+
| 2008-11-16 |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

说明

将日期的字符串表示法转换为 DATE 对象。

format_string 包含用于定义 date_string 格式的格式元素。date_string 中的每个元素都必须在 format_string 中有对应的元素。format_string 中每个元素的位置必须与 date_string 中每个元素的位置一致。

-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")

-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")

格式字符串完全支持大多数格式元素，但 %Q、%a、%A、%g、%G、%j、%u、%U、%V、%w、%W 除外。

使用 PARSE_DATE 时，请注意以下几点：

  • 未指定的字段。任何未指定的字段在初始化时均以 1970-01-01 为准。
  • 不区分大小写的名称。Monday、February 等名称不区分大小写。
  • 空格。格式字符串中的一个或多个连续空格与日期字符串中的零个或多个连续空格相匹配。此外，始终可在日期字符串中使用前导空格和尾随空格（即使格式字符串中不包含这些空格）。
  • 格式优先顺序。如果两个（或更多）格式元素具有重叠信息（例如，%F 和 %Y 均对年份有影响），则最后一个格式元素通常会替换前面的所有元素。

返回数据类型

DATE

示例

以下示例将 MM/DD/YY 格式的字符串转换为 DATE 对象：

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

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

以下示例将 YYYYMMDD 格式的字符串转换为 DATE 对象：

SELECT PARSE_DATE("%Y%m%d", "20081225") 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 星期几的缩写名称。 Wed
%B 月份的全名。 1 月
%b 或 %h 月份的缩写名称。 Jan
%C 用十进制数 (00-99) 表示的世纪（年份除以 100 并截断为整数）。 20
%D 采用 %m/%d/%y 格式的日期。 01/20/21
%d 用十进制数 (01-31) 表示的月份中的某一天。 20
%e 用十进制数 (1-31) 表示的月份中的某一天；个位数前面附加一个空格。 20
%F 采用 %Y-%m-%d 格式的日期。 2021-01-20
%G 用十进制数表示的带有世纪信息的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。 请注意，%G 和 %Y 可能会在公历年的边界值附近产生不同的结果，其中公历年和 ISO 格式年份可能会有所不同。 2021
%g 用十进制数 (00-99) 表示的两位数的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。请注意，％G 和 ％Y 可能会在公历年边界值附近产生不同的结果，其中公历年和 ISO 格式年份可能会有所不同。 21
%j 用十进制数 (001-366) 表示的一年中的某一天。 020
%m 用十进制数 (01-12) 表示的月份。 01
%n 换行符。
%Q 用十进制数 (1-4) 表示的季度。 1
%t 制表符。
%U 用十进制数 (00-53) 表示的一年中的周数（星期日算作一周的第一天）。 03
%u 用十进制数 (1-7) 表示的星期名称（星期一算作一周的第一天）。 3
%V 用十进制数 (01-53) 表示的一年中的 ISO 8601 格式周数（星期一算作一周的第一天）。如果包含 1 月 1 日的那一周有 4 天或超过 4 天属于新的一年，则该周为第 1 周；否则为上一年的第 53 周，而下一周为新年的第 1 周。 03
%W 用十进制数 (00-53) 表示的一年的周数（星期一算作一周的第一天）。 03
%w 用十进制数 (0-6) 表示的星期名称（星期日算作一周的第一天）。 3
%x 采用 MM/DD/YY 格式表示的日期。 01/20/21
%Y 用十进制数表示的四位数的年份。 2021
%y 用十进制数 (00-99) 表示的不带世纪信息的年份（可选用前导零）。可与 ％C 混合使用。如果未指定 ％C，则 00-68 年属于 2000 年代，而 69-99 年属于 1900 年代。 21
%E4Y 用四个字符表示的年份 (0001 ... 9999)。注意：%Y 将生成尽量多的字符数以完整显示年份。 2021