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 通过将行分组到基于时间的“bin”或“窗口”中，并对每个窗口应用聚合或选择器函数，来对时间序列数据进行降采样。

例如，如果您将数据“bin”或“window”到 15 分钟的间隔，则输入时间戳 2023-01-01T18:18:18Z 将被更新到其所属的 15 分钟 bin 的开始时间：2023-01-01T18:15:00Z。

date_bin(interval, expression[, origin_timestamp])

参数

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

  • 纳秒
  • microseconds
  • 毫秒
  • 秒
  • 分钟
  • 小时
  • days
  • weeks
  • months
  • years
  • 世纪

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

• origin_timestamp：用于确定 bin 边界的起始点。默认为 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：bin 间隔。支持以下间隔单位

  • 纳秒
  • microseconds
  • 毫秒
  • 秒
  • 分钟
  • 小时
  • days
  • weeks

以下间隔目前不支持

• months

• years

• 世纪

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

• origin_timestamp：用于确定 bin 边界的起始点。默认为 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 通过将行分组到基于“挂钟”时间在特定时区中的时间间隔“bin”或“窗口”中，并对每个窗口应用聚合或选择器函数，来对时间序列数据进行降采样。

时区偏移

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

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

date_bin_wallclock(interval, expression[, origin_timestamp])

参数

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

  • 纳秒
  • microseconds
  • 毫秒
  • 秒
  • 分钟
  • 小时
  • days
  • weeks

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

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

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

  避免在时区不连续处使用 bin

  时区偏移会导致不连续——时间间隔连续性的中断（丢失一小时或增加一小时）——在使用 date_bin_wallclock 时可能导致意外的时间戳。避免使用导致 bin 落入时间不连续的 interval 和 origin_timestamp 组合。

  作为一般规则，请使用默认的 origin_timestamp 或与 Unix 纪元偏移量相关且等于指定 interval 的 origin timestamp。

  查看时区不连续示例

  以下查询说明了两个仅相差一分钟的时间戳，如何在夏令时边界的 binning 后导致相差两小时的时间戳

  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)）。如果挂钟时间 bin 开始于指定时区中不存在的时间，则时间戳将调整为该时区中与一天开始时间相同的偏移量。

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

date_bin_wallclock_gapfill(interval, expression[, origin_timestamp])

参数

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

  • 纳秒
  • microseconds
  • 毫秒
  • 秒
  • 分钟
  • 小时
  • days
  • weeks

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

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

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

  避免在时区不连续处使用 bin

  时区偏移会导致不连续——时间间隔连续性的中断（丢失一小时或增加一小时）——在使用 date_bin_wallclock_gapfill 时可能导致意外的时间戳。避免使用导致 bin 落入时间不连续的 interval 和 origin_timestamp 组合。

  作为一般规则，请使用默认的 origin_timestamp 或与 Unix 纪元偏移量相关且等于指定 interval 的 origin timestamp。

  查看时区不连续示例

相关函数

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：要截断到的时间精度。支持以下精度

  • year
  • month
  • 周
  • 天
  • 小时
  • minute
  • second

• 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：要返回的日期部分。支持以下日期部分

  • year
  • month
  • week （一年中的周数）
  • day （月份中的天数）
  • 小时
  • minute
  • second
  • 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：要返回的日期部分或字段。支持以下日期字段

  • year
  • month
  • week （一年中的周数）
  • day （月份中的天数）
  • 小时
  • minute
  • second
  • millisecond
  • microsecond
  • nanosecond
  • dow （一周中的天数）
  • day （一年中的天数）

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

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

from_unixtime

将整数（Unix 时间戳，秒）转换为时间戳值。底层结果是时间戳（Timestamp(TimeUnit::Second, None)）。如果您将查询结果输出为 JSON（API 默认）、CSV 或 pretty（CLI 默认），时间戳将格式化为 ISO 8601 字符串（YYYY-MM-DDTHH:MM:SS，不带时区指示符）。输出到 Parquet 时，将保留原始整数值（例如 1641042000）。

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;