标准 SQL 中的日期时间函数

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 对象。它支持使用可选参数来指定时区。如果未指定时区,则使用默认时区世界协调时间 (UTC)。

返回数据类型

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)

说明

向 DATETIME 对象添加 INT64_expr 个单位的 part

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 中减去 INT64_expr 个单位的 part

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 结果。对部分 WEEK 执行 DATETIME_DIFF 运算会返回 1,因为 DATETIME_DIFF 会对此 DATETIME 范围内的部分边界值数量进行计数。每个 WEEK 都从星期日开始,因此在 2017-10-14 00:00:00 星期六与 2017-10-15 00:00:00 星期日之间有一个日期部分的边界值。

以下示例显示了不同年份中两个日期的 DATETIME_DIFF 结果。对日期部分 YEAR 执行 DATETIME_DIFF 运算会返回 3,因为它会计算两个 DATETIME 之间的公历年边界值数量。对日期部分 ISOYEAR 执行 DATETIME_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 结果。第一个日期是星期一,第二个日期是星期日。对日期部分 WEEK 执行 DATETIME_DIFF 运算会返回 0,因为此时间部分使用从星期日开始的周。对日期部分 WEEK(MONDAY) 执行 DATETIME_DIFF 运算会返回 1。对日期部分 ISOWEEK 执行 DATETIME_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 和时间戳的字符串表示法返回 TIMESTAMP 对象。如需查看此函数支持的格式元素列表,请参阅 DATETIME 支持的格式元素

使用 PARSE_DATETIME 时,请注意以下几点:

  • 未指定的字段。 任何未指定的字段均从 1970-01-01 00:00:00.0 开始初始化。例如,如果未指定年份,则默认为 1970,依此类推。
  • 不区分大小写的名称。 MondayFebruary 等名称不区分大小写。
  • 空格。 格式字符串中的一个或多个连续空格与 DATETIME 字符串中的零个或多个连续空格相匹配。此外,始终可在 DATETIME 字符串中使用前导空格和尾随空格(即使这些空格不在格式字符串中)。
  • 格式优先。 如果两个(或更多)格式元素的信息出现重叠(例如 %F%Y 均对年份有影响),则最后一个元素通常会替换前面的所有元素,但也存在一些例外情况(请参阅 %s%C%y 的说明)。

返回数据类型

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 格式年份。
%g 用十进制数 (00-99) 表示的两位数的 ISO 8601 格式年份。
%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 日的那一周有 4 天或超过 4 天属于新的一年,则该周为第 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 将生成尽量多的字符数以完整显示年份。
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

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