标准 SQL 中的时间戳函数

BigQuery 支持以下 TIMESTAMP 函数。

注意:如果发生溢出,这些函数会返回运行时错误;结果值受到所定义的日期和时间戳最小值/最大值限制。

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

说明

括号是可选项。此函数在 20 个小时的时段中围绕插入的闰秒进行模糊处理。 CURRENT_TIMESTAMP() 生成一个 TIMESTAMP 值,该值是连续非模糊的值,每分钟刚好 60 秒,不会重复闰秒期间的值。

支持的输入类型

不适用

结果数据类型

TIMESTAMP

示例

SELECT CURRENT_TIMESTAMP() as now;

+-------------------------------+
| now                           |
+-------------------------------+
| 2016-05-16 18:12:47.145482+00 |
+-------------------------------+

EXTRACT

EXTRACT(part FROM timestamp_expression [AT TIME ZONE tz_spec])

说明

从提供的 INT64 中返回与指定的 part 相对应的 timestamp_expression 值。

可使用以下 part 值:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK:返回 [0, 53] 范围内的日期的周数。一周从星期日开始算起,一年第一个星期日之前的日期属于第 0 周。

  • WEEK(<WEEKDAY>):返回 [0, 53] 范围内的 timestamp_expression 的周数。一周从 WEEKDAY 开始算起,一年第一个 WEEKDAY 之前的 datetime 属于第 0 周。WEEKDAY 的有效值包括 SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY

  • ISOWEEK:以 datetime_expression 格式返回 ISO 8601 的周数。ISOWEEK 从星期一开始算起。返回值在 [1, 53] 范围内。每个 ISO 格式年份的第一个 ISOWEEK 从阳历年第一个星期四之前的星期一开始算起。

  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:以 ISO 8601 格式返回按周数计算的年,它是指包含 date_expression 所属那周的星期四的阳历年。
  • DATE
  • DATETIME
  • TIME

返回的值会截断排序更靠后的时间段。例如,提取秒数时,EXTRACT 截断毫秒值和微秒值。

要了解如何指定时区,请参阅时区定义

返回数据类型

通常是 INT64。如果 partDATE,则返回 DATE

示例

下例中,EXTRACT 返回了与 DAY 时间部分相对应的值。

SELECT EXTRACT(DAY
  FROM TIMESTAMP "2008-12-25 15:30:00" AT TIME ZONE "America/Los_Angeles")
  AS the_day;

+------------+
| the_day    |
+------------+
| 25         |
+------------+

下例中,EXTRACT 返回了与时间戳的一列中不同的时间部分相对应的值。

WITH Timestamps AS (
  SELECT TIMESTAMP '2005-01-03 12:34:56' AS timestamp UNION ALL
  SELECT TIMESTAMP '2007-12-31' UNION ALL
  SELECT TIMESTAMP '2009-01-01' UNION ALL
  SELECT TIMESTAMP '2009-12-31' UNION ALL
  SELECT TIMESTAMP '2017-01-02' UNION ALL
  SELECT TIMESTAMP '2017-05-26'
)
SELECT
  timestamp,
  EXTRACT(ISOYEAR FROM timestamp) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp) AS isoweek,
  EXTRACT(YEAR FROM timestamp) AS year,
  EXTRACT(WEEK FROM timestamp) AS week
FROM Timestamps
ORDER BY timestamp;
+------------------------+---------+---------+------+------+
| timestamp              | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56+00 | 2005    | 1       | 2005 | 1    |
| 2007-12-31 00:00:00+00 | 2008    | 1       | 2007 | 52   |
| 2009-01-01 00:00:00+00 | 2009    | 1       | 2009 | 0    |
| 2009-12-31 00:00:00+00 | 2009    | 53      | 2009 | 52   |
| 2017-01-02 00:00:00+00 | 2017    | 1       | 2017 | 1    |
| 2017-05-26 00:00:00+00 | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

在以下示例中,timestamp_expression 属于星期日。EXTRACT 使用从星期日开始的周数计算第一列,并使用从星期一开始的周数计算第二列。

WITH table AS (SELECT TIMESTAMP('2017-11-05 00:00:00') AS timestamp)
SELECT
  timestamp,
  EXTRACT(WEEK(SUNDAY) FROM timestamp) AS week_sunday,
  EXTRACT(WEEK(MONDAY) FROM timestamp) AS week_monday
FROM table;

+------------------------+-------------+---------------+
| timestamp              | week_sunday | week_monday |
+------------------------+-------------+---------------+
| 2017-11-05 00:00:00+00 | 45          | 44            |
+------------------------+-------------+---------------+

STRING

STRING(timestamp_expression[, timezone])

说明

timestamp_expression 转换为 STRING 数据类型。支持使用可选参数来指定时区。要了解如何指定时区,请参阅时区定义

返回数据类型

STRING

示例

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles") as string;

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 15:30:00-08        |
+-------------------------------+

TIMESTAMP

1. TIMESTAMP(string_expression[, timezone])
2. TIMESTAMP(date_expression[, timezone])
3. TIMESTAMP(datetime_expression[, timezone])

说明

  1. 将 STRING 表达式转换为 TIMESTAMP 数据类型。

  2. 将 DATE 对象转换为 TIMESTAMP 数据类型。

  3. 将 DATETIME 对象转换为 TIMESTAMP 数据类型。

此函数支持使用可选参数来指定时区。如果未指定时区,则使用默认时区世界协调时间 (UTC)。

返回数据类型

TIMESTAMP

示例

SELECT
  CAST(TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_str,
  CAST(TIMESTAMP(DATE "2008-12-25", "America/Los_Angeles") AS STRING) AS timestamp_date,
  CAST(TIMESTAMP(DATETIME "2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_datetime;

+------------------------+------------------------+------------------------+
| timestamp_str          | timestamp_date         | timestamp_datetime     |
+------------------------+------------------------+------------------------+
| 2008-12-25 23:30:00+00 | 2008-12-25 08:00:00+00 | 2008-12-25 23:30:00+00 |
+------------------------+------------------------+------------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

说明

向时间戳添加 int64_expression 单位的 date_part,不考虑任何时区。

TIMESTAMP_ADD 支持以下 date_part 值:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等于 60 MINUTE

返回数据类型

TIMESTAMP

示例

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS later;

+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:40:00+00 |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

说明

从时间戳中减去 int64_expression 单位的 date_part,不考虑任何时区。

TIMESTAMP_SUB 支持以下 date_part 值:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等于 60 MINUTE

返回数据类型

TIMESTAMP

示例

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS earlier;

+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:20:00+00 |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression, timestamp_expression, date_part)

说明

返回两个时间戳之间所指定的整个 date_part 间隔数。如果计算结果超过结果类型的最大值(例如两个时间戳之间的微秒数之差超过 INT64 值的最大值),则引发一个错误。

TIMESTAMP_DIFF 支持以下 date_part 值:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等于 60 MINUTE

返回数据类型

INT64

示例

SELECT
  TIMESTAMP "2010-07-07 10:20:00 UTC" as first_timestamp,
  TIMESTAMP "2008-12-25 15:30:00 UTC" as second_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00 UTC",
    TIMESTAMP "2008-12-25 15:30:00 UTC", HOUR) AS hours;

+------------------------+------------------------+-------+
| first_timestamp        | second_timestamp       | hours |
+------------------------+------------------------+-------+
| 2010-07-07 10:20:00+00 | 2008-12-25 15:30:00+00 | 13410 |
+------------------------+------------------------+-------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

说明

将时间戳截断到 date_part 的粒度。

TIMESTAMP_TRUNC 支持以下 date_part 值:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>): 将T timestamp_expression 截断到上一周的边界值,每周从 WEEKDAY 开始。WEEKDAY 的有效值包括 SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
  • ISOWEEK:将 timestamp_expression 截断到上一个 ISO 8601 格式的周边界值。ISOWEEK 从星期一开始算起。每个 ISO 格式年份的第一个 ISOWEEK 包含对应阳历年的第一个星期四。早于此日期的任何 date_expression 均会截断到上一个星期一。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:将 timestamp_expression 截断到上一个 ISO 8601 格式的周编号年份边界值。ISO 格式年份的边界值是星期四属于相应公历年的第一周的星期一。

TIMESTAMP_TRUNC 函数支持可选的 time_zone 参数。此参数适用于以下 date_parts

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • WEEK(<WEEKDAY>)
  • MONTH
  • QUARTER
  • YEAR

如果您想要在截断操作过程中使用默认时区世界协调时间 (UTC) 以外的时区,请使用此参数。

返回数据类型

TIMESTAMP

示例

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'UTC') as utc,
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'America/Los_Angeles') as la;

+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25 00:00:00+00 | 2008-12-25 08:00:00+00 |
+------------------------+------------------------+

在以下示例中,timestamp_expression 的时区偏移量为 +12。第一列显示世界协调时间 (UTC) 形式的 timestamp_expression。第二列使用从星期一开始的周显示 TIMESTAMP_TRUNC 输出。由于 timestamp_expression 在世界协调时间 (UTC) 中属于星期日,因此 TIMESTAMP_TRUNC 会将其截断到上一个星期一。第三列显示使用可选的时区定义参数 'Pacific/Auckland' 的相同函数。在这里,函数会根据新西兰夏令时截断 timestamp_expression,在这个时区内,它属于星期一。

SELECT
  timestamp,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY)) AS utc_truncated,
  TIMESTAMP_TRUNC(timestamp, WEEK(MONDAY), 'Pacific/Auckland') AS nzdt_truncated
FROM (SELECT TIMESTAMP('2017-11-06 00:00:00+12') AS timestamp);

+------------------------+------------------------+------------------------+
| timestamp              | utc_truncated          | nzdt_truncated         |
+------------------------+------------------------+------------------------+
| 2017-11-05 12:00:00+00 | 2017-10-30 07:00:00+00 | 2017-11-05 11:00:00+00 |
+------------------------+------------------------+------------------------+

在以下示例中,原始 timestamp_expression 是阳历年 2015 年。但对日期部分 ISOYEAR 执行 TIMESTAMP_TRUNC 会将 timestamp_expression 截断到 ISO 格式年份的开端,而非阳历年的开端。阳历年 2015 年的第一个星期四是 2015-01-01,因此 ISO 格式年份 2015 年开始于上一个星期一,也就是 2014-12-29。因此,timestamp_expression 2015-06-15 00:00:00+00 之前的 ISO 格式年份边界值是 2014-12-29。

SELECT
  TIMESTAMP_TRUNC('2015-06-15 00:00:00+00', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM TIMESTAMP '2015-06-15 00:00:00+00') AS isoyear_number;

+------------------------+----------------+
| isoyear_boundary       | isoyear_number |
+------------------------+----------------+
| 2014-12-29 00:00:00+00 | 2015           |
+------------------------+----------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

说明

根据指定的 format_string 设置时间戳的格式。

如需查看此函数支持的格式元素列表,请参阅 TIMESTAMP 支持的格式元素

返回数据类型

STRING

示例

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles")
  AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 07:30:00 2008 |
+--------------------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, string[, time_zone])

说明

使用 format_string 和时间戳的字符串表示形式返回 TIMESTAMP 对象。

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

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

如需查看此函数支持的格式元素列表,请参阅 TIMESTAMP 支持的格式元素

返回数据类型

TIMESTAMP

示例

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008", "America/Los_Angeles") as parsed;

+-------------------------+
| parsed                  |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

说明

int64_expression 解释为从 1970-01-01 00:00:00 UTC 开始计算的秒数。

返回数据类型

TIMESTAMP

示例

SELECT TIMESTAMP_SECONDS(1230219000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

说明

int64_expression 解释为从 1970-01-01 00:00:00 UTC 开始计算的毫秒数。

返回数据类型

TIMESTAMP

示例

SELECT TIMESTAMP_MILLIS(1230219000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

说明

int64_expression 解释为从 1970-01-01 00:00:00 UTC 开始计算的微秒数。

返回数据类型

TIMESTAMP

示例

SELECT TIMESTAMP_MICROS(1230219000000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

说明

返回从 1970-01-01 00:00:00 UTC 开始计算的秒数。截断更高级别的精度。

返回数据类型

INT64

示例

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00") as seconds;

+------------+
| seconds    |
+------------+
| 1230219000 |
+------------+

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

说明

返回从 1970-01-01 00:00:00 UTC 开始计算的毫秒数。截断更高级别的精度。

返回数据类型

INT64

示例

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 UTC") as millis;

+---------------+
| millis        |
+---------------+
| 1230219000000 |
+---------------+

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

说明

返回从 1970-01-01 00:00:00 UTC 开始计算的微秒数。截断更高级别的精度。

返回数据类型

INT64

示例

SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00") as micros;

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

TIMESTAMP 支持的格式元素

除非另有说明,否则使用格式字符串的 TIMESTAMP 函数均支持以下元素:

格式元素 说明
%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 UTC 开始计算的秒数。无论 %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 年代。
%Z 时区名称。
%z 根据情况采用 +HHMM 或 -HHMM 格式表示与本初子午线的偏移量,其中正值表示格林威治东区。
%% 单个 % 字符。
%Ez 与 RFC 3339 兼容的数字时区(+HH:MM 或 -HH:MM)。
%E#S 具有 # 位小数精度的秒数。
%E*S 具有完整小数精度的秒数(字面量“*”)。
%E4Y 四个字符的年份 (0001 ... 9999)。注意:%Y 将生成尽量多的字符数以完整显示年份。

时区定义

某些日期和时间戳函数可用于替换默认时区并指定其他时区。您可采用以下格式提供其世界协调时间 (UTC) 偏移量,从而指定一个时区:

(+|-)H[H][:M[M]]

例如:

-08:00
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

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