SQL 时间和日期函数

InfluxDB 3 Core SQL 实现支持在处理时间序列数据时非常有用的时间和日期函数。

• current_date
• current_time
• current_timestamp
• date_bin
• date_bin_gapfill
• date_bin_wallclock
• date_bin_wallclock_gapfill
• date_trunc
• datetrunc
• date_format
• date_part
• datepart
• extract
• from_unixtime
• make_date
• now
• today
• to_char
• to_date
• to_local_time
• to_timestamp
• to_timestamp_micros
• to_timestamp_millis
• to_timestamp_nanos
• to_timestamp_seconds
• to_unixtime
• tz

current_date

返回当前的 UTC 日期。

current_date() 返回值在查询时确定，并且返回相同的日期，无论函数在查询计划中的哪个位置执行。

current_date()

SELECT
  time,
  temp,
  current_date()::TIMESTAMP AS current_date
FROM home
WHERE
  time > current_date()::TIMESTAMP - INTERVAL '5 years'
LIMIT 3

current_time

返回当前的 UTC 时间。

current_time() 返回值在查询时确定，并且返回相同的时间，无论函数在查询计划中的哪个位置执行。

current_time()

SELECT
  time,
  temp,
  current_time() AS current_time
FROM home
LIMIT 3

current_timestamp

now 的别名。

date_bin

计算时间间隔并返回最接近指定时间戳的间隔的开始时间。 使用 date_bin 通过将行分组到基于时间的“桶”或“窗口”中，并将聚合或选择器函数应用于每个窗口，来下采样时间序列数据。

例如，如果您将数据“分桶”或“窗口化”为 15 分钟间隔，则输入时间戳 2023-01-01T18:18:18Z 将更新为它所在的 15 分钟桶的开始时间：2023-01-01T18:15:00Z。

date_bin(interval, expression[, origin_timestamp])

参数

• interval：桶间隔。 支持以下间隔单位

  • 纳秒
  • 微秒
  • 毫秒
  • 秒
  • 分钟
  • 小时
  • 天
  • 周
  • 月
  • 年
  • 世纪

• expression：要操作的时间表达式。 可以是常量、列或函数。

• origin_timestamp：用于确定桶边界的起始点。 默认为 Unix 纪元。

SELECT
  date_bin(INTERVAL '1 day', time, TIMESTAMP '1970-01-01 00:00:00Z') AS time,
  avg("water_level") AS water_level_avg
FROM "h2o_feet"
WHERE
  time >= timestamp '2019-09-10T00:00:00Z'
  AND time <= timestamp '2019-09-20T00:00:00Z'
GROUP BY 1
ORDER BY time DESC

date_bin_gapfill

计算时间间隔并返回最接近指定时间戳的间隔的开始时间。 如果时间间隔内没有行存在，则会插入一个新行，其 time 值设置为间隔开始时间，GROUP BY 子句中的所有列都已填充，聚合列中为 null 值。

将 date_bin_gapfill 与 interpolate 或 locf 结合使用，以在指定的时间间隔 填充数据空白。

date_bin_gapfill(interval, expression[, origin_timestamp])

参数

• interval：桶间隔。 支持以下间隔单位

  • 纳秒
  • 微秒
  • 毫秒
  • 秒
  • 分钟
  • 小时
  • 天
  • 周
  • 月
  • 年
  • 世纪

• expression：要操作的时间表达式。 可以是常量、列或函数。

• origin_timestamp：用于确定桶边界的起始点。 默认为 Unix 纪元。

相关函数

interpolate, locf

SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time) as _time,
  room,
  avg(temp) as temp
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY _time, room

SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time) as _time,
  room,
  interpolate(avg(temp))
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY _time, room

SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time) as _time,
  room,
  locf(avg(temp))
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY _time, room

date_bin_wallclock

使用指定时间值的时区计算时间间隔，并返回最接近指定时间戳的间隔的开始时间。 使用 date_bin_wallclock 通过将行分组到基于时间的“桶”或“窗口”中，这些“桶”或“窗口”基于特定时区的“挂钟”时间，并将聚合或选择器函数应用于每个窗口，来下采样时间序列数据。

时区偏移

许多地区使用时区偏移（例如夏令时 (DST)）。 如果挂钟时间桶在指定时区中不存在的时间开始，则时间戳将调整为与该时区一天开始时间的偏移量相同的时间。

如果挂钟时间代表该地区的一个模棱两可的时间，则行为取决于指定间隔的大小。 如果间隔大于两个可能时间戳之间的差异，则使用较早的时间戳。 否则，该函数使用与输入时间戳的 UTC 偏移量匹配的时间戳。

date_bin_wallclock(interval, expression[, origin_timestamp])

参数

• interval：桶间隔。 支持以下间隔单位

  • 纳秒
  • 微秒
  • 毫秒
  • 秒
  • 分钟
  • 小时
  • 天
  • 周

  date_bin_wallclock 不 支持基于月、年或世纪的间隔。

• expression：要操作的时间表达式。 可以是常量、列或函数。 输出时间戳使用此时间表达式的时区。

• origin_timestamp：用于确定桶边界的起始点。 这必须是“挂钟”时间戳（无时区）。 默认为 Unix 纪元。

  避免时区不连续性中的桶

  时区偏移 会导致不连续性——时间间隔连续性中断（丢失一小时或获得一小时）——当使用 date_bin_wallclock 时，这可能会导致意外的时间戳。 避免使用导致桶落入时间不连续性内部的 interval 和 origin_timestamp 组合。

  作为一般规则，使用默认的 origin_timestamp 或相对于 Unix 纪元的偏移量等于指定 interval 的起始时间戳。

  查看时区不连续性示例

  以下查询说明了当跨越夏令时边界分桶时，两个时间戳（仅相隔一分钟）如何导致相隔两小时的时间戳

  SELECT
    tz('2020-10-25T02:29:00+01:00', 'Europe/Paris') AS original_time,
    date_bin_wallclock(
      INTERVAL '1 hour',
      tz('2020-10-25T02:29:00+01:00', 'Europe/Paris'),
      '1970-01-01T00:30:00'
    ) AT TIME ZONE 'UTC' AS utc_bin_time
  UNION
  SELECT
    tz('2020-10-25T02:30:00+01:00', 'Europe/Paris') AS original_time,
    date_bin_wallclock(
      INTERVAL '1 hour',
      tz('2020-10-25T02:30:00+01:00', 'Europe/Paris'),
      '1970-01-01T00:30:00'
    ) AT TIME ZONE 'UTC' AS utc_bin_time
  ORDER BY original_time;
  

  original_time	utc_bin_time
  2020-10-25T02:29:00+01:00	2020-10-24T23:30:00Z
  2020-10-25T02:30:00+01:00	2020-10-25T01:30:00Z

SELECT
  date_bin_wallclock(INTERVAL '12 hours', tz(time, 'America/Los_Angeles')) AS time,
  room,
  avg(temp) AS avg_temp
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T20:00:00Z'
GROUP BY 1, room

date_bin_wallclock_gapfill

使用指定时间值的时区计算时间间隔，并返回最接近指定时间戳的间隔的开始时间。 如果时间间隔内没有行存在，则会插入一个新行，其 time 值设置为间隔开始时间，GROUP BY 子句中的所有列都已填充，聚合列中为 null 值。

将 date_bin_wallclock_gapfill 与 interpolate 或 locf 结合使用，以在指定的时区中的指定时间间隔 填充数据空白。

时区偏移

许多地区使用时区偏移（例如夏令时 (DST)）。 如果挂钟时间桶在指定时区中不存在的时间开始，则时间戳将调整为与该时区一天开始时间的偏移量相同的时间。

如果挂钟时间代表该地区的一个模棱两可的时间，则行为取决于指定间隔的大小。 如果间隔大于两个可能时间戳之间的差异，则使用较早的时间戳。 否则，该函数使用与输入时间戳的 UTC 偏移量匹配的时间戳。

date_bin_wallclock_gapfill(interval, expression[, origin_timestamp])

参数

• interval：桶间隔。 支持以下间隔单位

  • 纳秒
  • 微秒
  • 毫秒
  • 秒
  • 分钟
  • 小时
  • 天
  • 周

  date_bin_wallclock_gapfill 不 支持基于月、年或世纪的间隔。

• expression：要操作的时间表达式。 可以是常量、列或函数。 输出时间戳使用此时间表达式的时区。

• origin_timestamp：用于确定桶边界的起始点。 这必须是“挂钟”时间戳（无时区）。 默认为 Unix 纪元。

  避免时区不连续性中的桶

  时区偏移 会导致不连续性——时间间隔连续性中断（丢失一小时或获得一小时）——当使用 date_bin_wallclock_gapfill 时，这可能会导致意外的时间戳。 避免使用导致桶落入时间不连续性内部的 interval 和 origin_timestamp 组合。

  作为一般规则，使用默认的 origin_timestamp 或相对于 Unix 纪元的偏移量等于指定 interval 的起始时间戳。

  查看时区不连续性示例

相关函数

interpolate, locf

SELECT
  date_bin_wallclock_gapfill(INTERVAL '30 minutes', tz(time, 'America/Los_Angeles')) as time,
  room,
  avg(temp) as temp
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY 1, room

SELECT
  date_bin_wallclock_gapfill(INTERVAL '30 minutes', tz(time, 'America/Los_Angeles')) as time,
  room,
  interpolate(avg(temp))
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY 1, room

SELECT
  date_bin_wallclock_gapfill(INTERVAL '30 minutes', tz(time, 'America/Los_Angeles')) as time,
  room,
  locf(avg(temp))
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY 1, room

date_trunc

将时间戳值截断为指定的精度。

date_trunc(precision, expression) 

参数

• precision：要截断到的时间精度。 支持以下精度

  • 年
  • 月
  • 周
  • 天
  • 小时
  • 分钟
  • 秒

• expression：要操作的时间表达式。 可以是常量、列或函数。

别名

• datetrunc

SELECT
  avg(water_level) AS level,
  date_trunc('hour', time) AS hour
FROM h2o_feet
WHERE
  time >= timestamp '2019-09-10T00:00:00Z'
  AND time <= timestamp '2019-09-12T00:00:00Z'
GROUP BY hour
ORDER BY hour

SELECT
  mean(water_level) as level,
  date_trunc('week',time) AS week
FROM h2o_feet
WHERE
  time >= timestamp '2019-08-01T00:00:00Z'
  AND time <= timestamp '2019-10-31T00:00:00Z'
GROUP BY week
ORDER BY week

datetrunc

date_trunc 的别名。

date_format

to_char 的别名。

date_part

返回日期的指定部分，作为整数。

date_part(part, expression)

参数

• part：要返回的日期部分。 支持以下日期部分

  • 年
  • 月
  • week （一年中的第几周）
  • day （一个月中的第几天）
  • 小时
  • 分钟
  • 秒
  • millisecond
  • microsecond
  • nanosecond
  • dow （星期几）
  • day （一年中的第几天）

• expression：要操作的时间表达式。 可以是常量、列或函数。

别名

• datepart

SELECT
  date_part('hour', time) AS hour,
  time,
  "level description", 
  location
FROM h2o_feet
WHERE
  time >= timestamp '2019-08-17T02:54:00Z'
  AND time <= timestamp '2019-08-17T03:06:00Z'
ORDER BY time

datepart

date_part 的别名。

extract

从时间值返回子字段作为整数。 类似于 date_part，但参数不同。

extract(field FROM source)

参数

• field：要返回的日期部分或字段。 支持以下日期字段

  • 年
  • 月
  • week （一年中的第几周）
  • day （一个月中的第几天）
  • 小时
  • 分钟
  • 秒
  • millisecond
  • microsecond
  • nanosecond
  • dow （星期几）
  • day （一年中的第几天）

• source：要操作的源时间表达式。 可以是常量、列或函数。

SELECT 
  extract(day from time) AS day
FROM 
  h2o_feet 
LIMIT 1

from_unixtime

将整数转换为 RFC3339 时间戳格式 (YYYY-MM-DDT00:00:00.000000000Z)。 输入解析为 Unix 纳秒时间戳，并返回相应的 RFC3339 时间戳。

from_unixtime(expression)

参数

• expression：要操作的整数表达式。 可以是常量、列或函数，以及算术运算符的任意组合。

相关函数

to_unixtime

SELECT
  from_unixtime(1672531200000000000) AS RFC3339

make_date

使用组成部分（年、月、日）返回日期。

make_date(year, month, day)

参数

• year：制作日期时要使用的年份。 可以是常量、列或函数，以及算术运算符的任意组合。
• month：制作日期时要使用的月份。 可以是常量、列或函数，以及算术运算符的任意组合。
• day：制作日期时要使用的天数。 可以是常量、列或函数，以及算术运算符的任意组合

SELECT make_date(2024, 01, 01)::STRING AS date

now

返回当前的 UTC 时间戳。

now() 返回值在查询时确定，并且返回相同的时间戳，无论函数在查询计划中的哪个位置执行。

now()

别名

• current_timestamp

SELECT
  "water_level",
  "time"
FROM h2o_feet
WHERE
  time <= now() - interval '12 minutes'

today

current_date 的别名。

to_char

基于 Rust Chrono 格式字符串 返回日期、时间、时间戳或持续时间的字符串表示形式。

to_char(expression, format)

参数

• expression：要操作的表达式。 可以是常量、列或函数，结果为日期、时间、时间戳或持续时间。
• format：用于转换表达式的 Rust Chrono 格式字符串。

别名

• date_format

SELECT
  to_char('2024-01-01T12:22:01Z'::TIMESTAMP, '%a %e-%b-%Y %H:%M:%S') AS datestring

to_date

将值转换为日期 (YYYY-MM-DD)。 支持字符串和数字类型作为输入。 字符串解析为 YYYY-MM-DD，除非指定了其他格式。 数字值被解释为自 Unix 纪元 以来的天数。

to_date(expression[, ..., format_n])

参数

• expression：要操作的表达式。 可以是常量、列或函数，以及算术运算符的任意组合。
• format_n：可选的 Rust strftime 模式，用于解析字符串表达式。 格式按照它们出现的顺序尝试。 该函数返回从第一个成功解析的格式的时间戳。 如果没有格式成功解析，则该函数返回错误。

SELECT
  to_date('1-Jan-2024', '%e-%b-%Y')::STRING AS date

to_local_time

将带时区的时间戳转换为不带时区的时间戳（无偏移或时区信息）。 此函数考虑了夏令时 (DST) 等时移。

to_local_time(expression)

参数

• expression：要操作的时间表达式。 可以是常量、列或函数。

SELECT
  to_local_time('2024-01-01 00:00:00'::TIMESTAMP) AS "local time";

SELECT
  to_local_time((arrow_cast('2024-01-01 00:00:00', 'Timestamp(Nanosecond, Some("UTC"))')) AT TIME ZONE 'America/Los_Angeles') AS "local time"

SELECT
  date_bin(interval '1 day', time, to_local_time(0::TIMESTAMP)) AT TIME ZONE 'America/Los_Angeles' AS time,
  avg(f1),
  avg(f2)
FROM
  (VALUES (arrow_cast('2024-01-01 12:00:00', 'Timestamp(Nanosecond, Some("UTC"))'), 1.23, 4.56),
          (arrow_cast('2024-01-01 13:00:00', 'Timestamp(Nanosecond, Some("UTC"))'), 2.46, 8.1),
          (arrow_cast('2024-01-01 14:00:00', 'Timestamp(Nanosecond, Some("UTC"))'), 4.81, 16.2)
  ) AS data(time, f1, f2)
GROUP BY 1

to_timestamp

将值转换为 RFC3339 时间戳格式 (YYYY-MM-DDT00:00:00Z)。 支持时间戳、整数和无符号整数类型作为输入。 整数和无符号整数解析为 Unix 纳秒时间戳，并返回相应的 RFC3339 时间戳。

to_timestamp(expression)

参数

• expression：要操作的表达式。 可以是常量、列或函数，以及算术运算符的任意组合。

SELECT to_timestamp(1704067200000000000)

to_timestamp_micros

将值转换为 RFC3339 微秒时间戳格式 (YYYY-MM-DDT00:00:00.000000Z)。 支持时间戳、整数和无符号整数类型作为输入。 整数和无符号整数解析为 Unix 微秒时间戳，并返回相应的 RFC3339 时间戳。

to_timestamp_micros(expression[, ..., format_n])

参数

• expression：要操作的表达式。 可以是常量、列或函数，以及算术运算符的任意组合。
• format_n：可选的 Rust strftime 模式，用于解析字符串表达式。 格式按照它们出现的顺序尝试。 该函数返回从第一个成功解析的格式的时间戳。 如果没有格式成功解析，则该函数返回错误。

SELECT to_timestamp_micros(1704067200000001)

SELECT to_timestamp_micros('01:01:59.123456789 01-01-2024', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y') AS microsecond

to_timestamp_millis

将值转换为 RFC3339 毫秒时间戳格式 (YYYY-MM-DDT00:00:00.000Z)。 支持时间戳、整数和无符号整数类型作为输入。 整数和无符号整数解析为 Unix 毫秒时间戳，并返回相应的 RFC3339 时间戳。

to_timestamp_millis(expression[, ..., format_n])

参数

• expression：要操作的表达式。 可以是常量、列或函数，以及算术运算符的任意组合。
• format_n：可选的 Rust strftime 模式，用于解析字符串表达式。 格式按照它们出现的顺序尝试。 该函数返回从第一个成功解析的格式的时间戳。 如果没有格式成功解析，则该函数返回错误。

SELECT to_timestamp_millis(1704067200001) AS time

SELECT to_timestamp_millis('01:01:59.123456789 01-01-2024', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y') AS millisecond

to_timestamp_nanos

将值转换为 RFC3339 纳秒时间戳格式 (YYYY-MM-DDT00:00:00.000000000Z)。 支持时间戳、整数和无符号整数类型作为输入。 整数和无符号整数解析为 Unix 纳秒时间戳，并返回相应的 RFC3339 时间戳。

to_timestamp_nanos(expression[, ..., format_n])

参数

• expression：要操作的表达式。 可以是常量、列或函数，以及算术运算符的任意组合。
• format_n：可选的 Rust strftime 模式，用于解析字符串表达式。 格式按照它们出现的顺序尝试。 该函数返回从第一个成功解析的格式的时间戳。 如果没有格式成功解析，则该函数返回错误。

SELECT to_timestamp_nanos(1704067200000000001)

SELECT to_timestamp_nanos('01:01:59.123456789 01-01-2024', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y') AS nanosecond

to_timestamp_seconds

将值转换为 RFC3339 秒时间戳格式 (YYYY-MM-DDT00:00:00Z)。 支持时间戳、整数和无符号整数类型作为输入。 整数和无符号整数解析为 Unix 秒时间戳，并返回相应的 RFC3339 时间戳。

to_timestamp_seconds(expression[, ..., format_n]) 

参数

• expression：要操作的表达式。 可以是常量、列或函数，以及算术运算符的任意组合。
• format_n：可选的 Rust strftime 模式，用于解析字符串表达式。 格式按照它们出现的顺序尝试。 该函数返回从第一个成功解析的格式的时间戳。 如果没有格式成功解析，则该函数返回错误。

SELECT to_timestamp_seconds(1704067201)

SELECT to_timestamp_seconds('01:01:59.123456789 01-01-2024', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y') AS second

to_unixtime

将值转换为自 Unix 纪元 以来的秒数。 支持字符串、时间戳和浮点数作为输入。 如果未提供 Rust Chrono 格式字符串，则字符串解析为 RFC3339Nano 时间戳。

to_unixtime(expression[, ..., format_n])

参数

• expression：要操作的表达式。 可以是常量、列或函数，以及算术运算符的任意组合。
• format_n：可选的 Rust strftime 模式，用于解析字符串表达式。 格式按照它们出现的顺序尝试。 该函数返回从第一个成功解析的格式的时间戳。 如果没有格式成功解析，则该函数返回错误。

相关函数

from_unixtime

SELECT to_unixtime('2024-01-01T01:01:59.123456789Z') AS unixtime

SELECT
  to_unixtime('01:01:59.123456789 01-01-2024', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y') AS unixtime

tz

将时间戳转换为提供的时区。 如果未提供第二个参数，则默认为 UTC。

tz(time_expression[, timezone])

参数

• time_expression：要操作的时间。 可以是常量、列或函数，以及算术运算符的任意组合。
• timezone：要将值转换为的 时区字符串。 默认为 'UTC'。 该函数返回转换为指定时区的时间戳。 如果传递了不正确的时区字符串或提供了错误的数据类型，则该函数返回错误。

SELECT tz('2024-01-01T01:00:00Z', 'America/New_York') AS time_tz

SELECT tz(time, 'Australia/Sydney') AS time_tz, time FROM home ORDER BY time LIMIT 3;

tz 和 AT TIME ZONE 之间的差异

当输入时间戳没有时区时，tz 和 AT TIME ZONE 不同。

• 当对没有时区的输入时间戳（InfluxDB 中的默认行为）使用 AT TIME ZONE 运算符时，该运算符返回相同的时间戳，但带有时间区偏移量（也称为“挂钟”时间）——例如

  '2024-01-01 00:00:00'::TIMESTAMP AT TIME ZONE 'America/Los_Angeles'
  
  -- Returns
  2024-01-01T00:00:00-08:00
  

• 当对带有时间区的输入时间戳使用时，tz() 函数和 AT TIME ZONE 运算符都返回转换为指定时区的时间的时间戳——例如

  '2024-01-01T00:00:00-00:00' AT TIME ZONE 'America/Los_Angeles'
  tz('2024-01-01T00:00:00-00:00', 'America/Los_Angeles')
  
  -- Both return
  2023-12-31T16:00:00-08:00
  

• tz() 始终将输入时间戳转换为指定的时区。 如果输入时间戳没有时区，则该函数假定它是 UTC 时间戳——例如

  tz('2024-01-01 00:00:00'::TIMESTAMP, 'America/Los_Angeles')
  -- Returns
  2023-12-31T16:00:00-08:00
  

  tz('2024-01-01T00:00:00+1:00', 'America/Los_Angeles')
  -- Returns
  2023-12-31T15:00:00-08:00
  

SELECT
  '2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels' as time_timestamp,
  tz('2024-04-01T00:00:20', 'Europe/Brussels') as time_tz;