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
的有效值包括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)
说明
- 使用表示年月日的 INT64 值构造 DATE。
- 从 TIMESTAMP 表达式中提取 DATE。该函数支持使用可选参数来指定时区。如果您未指定时区,则系统会使用默认时区(即世界协调时间 (UTC))。
- 从 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
。等于 7DAY
。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
。等于 7DAY
。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
。等于 7DAY
。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 | 星期几的缩写名称。 |
%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 | 换行符。 |
%Q | 用十进制数 (1-4) 表示的季度。 |
%t | 制表符。 |
%U | 用十进制数 (00-53) 表示的一年中的周数(星期日算作一周的第一天)。 |
%u | 用十进制数 (1-7) 表示的星期名称(星期一算作一周的第一天)。 |
%V | 用十进制数 (01-53) 表示的一年中的 ISO 8601 格式周数(星期一算作一周的第一天)。如果包含 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 会生成完整显示年份所需的字符数(无论需要多少个字符)。 |