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

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_expression 格式返回 ISO 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 数据类型。它支持使用可选参数来指定时区。如果未指定时区,则使用默认时区世界协调时间 (UTC)。

返回数据类型

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_part 之间的 date_expression 边界数。如果第一个日期发生在第二个日期之前,则结果为非正数。

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 结果。 对日期部分 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 的有效值包括 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 格式年份。
%g 用十进制数 (00-99) 表示的两位数的 ISO 8601 格式年份。
%j 用十进制数 (001-366) 表示的一年中的某一天。
%m 用十进制数 (01-12) 表示的月份。
%n 换行符。
%t 制表符。
%U 用十进制数 (00-53) 表示的一年中的周数(星期日算作一周的第一天)。
%u 用十进制数 (1-7) 表示的星期名称(星期一算作一周的第一天)。
%V 用十进制数 (01-53) 表示的一年中的周数(星期一算作一周的第一天)。如果包含 1 月 1 日的那一周有 4 天或超过 4 天属于新的一年,则该周为第 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 将生成尽量多的字符数以完整显示年份。
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面