SQL 时间和日期函数

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

• current_date
• current_time
• date_bin
• date_bin_gapfill
• date_bin_wallclock
• date_bin_wallclock_gapfill
• date_trunc
• datetrunc
• 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()::STRING AS current_time
FROM home
LIMIT 3

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_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()

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 格式字符串。

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;