SQL 时间和日期函数

InfluxDB 集群 SQL 实现支持在处理时间序列数据时有用的时间和日期函数。

• current_date
• current_time
• date_bin
• date_bin_gapfill
• date_bin_wallclock
• date_bin_wallclock_gapfill
• date_trunc
• datetrunc
• date_part
• datepart
• 提取
• from_unixtime
• make_date
• 现在
• 今天
• to_char
• to_date
• to_local_time
• to_timestamp
• to_timestamp_micros
• to_timestamp_millis
• to_timestamp_nanos
• to_timestamp_seconds
• to_unixtime
• tz

当前日期

返回当前的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

当前时间

返回当前的UTC时间。

当前时间（current_time()）的返回值是在查询时确定的，无论函数在查询计划中的何时执行，都返回相同的时间。

current_time()

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

日期分箱

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

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

date_bin(interval, expression[, origin_timestamp])

参数：

• 间隔: 盒子间隔。支持以下间隔单位：

  • nanoseconds
  • microseconds
  • milliseconds
  • seconds
  • minutes
  • hours
  • days
  • weeks
  • months
  • years
  • century

• 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

日期分箱填充

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

使用 date_bin_gapfill 与 interpolate 或 locf 来 填充数据中的空缺 在指定的时间间隔。

date_bin_gapfill(interval, expression[, origin_timestamp])

参数：

• 间隔: 盒子间隔。支持以下间隔单位：

  • nanoseconds
  • microseconds
  • milliseconds
  • seconds
  • minutes
  • hours
  • days
  • weeks
  • months
  • years
  • century

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

时区变化

许多地区使用时区变化（例如夏令时（DST））。 如果墙钟时间段在指定的时区中开始的时间不存在，则时间戳会调整为该时区当天开始的时间的相同偏移量。

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

date_bin_wallclock(interval, expression[, origin_timestamp])

参数：

• 间隔: 盒子间隔。支持以下间隔单位：

  • nanoseconds
  • microseconds
  • milliseconds
  • seconds
  • minutes
  • hours
  • days
  • weeks

  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;
  

  原始时间	UTC 二进制时间
  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

日期_bin_wallclock_gapfill

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

使用 date_bin_wallclock_gapfill 与 interpolate 或 locf 来 填补数据中的间隙 在指定时区的指定时间间隔内。

时区变化

许多地区使用时区变化（例如夏令时（DST））。 如果墙钟时间段在指定的时区中开始的时间不存在，则时间戳会调整为该时区当天开始的时间的相同偏移量。

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

date_bin_wallclock_gapfill(interval, expression[, origin_timestamp])

参数：

• 间隔: 盒子间隔。支持以下间隔单位：

  • nanoseconds
  • microseconds
  • milliseconds
  • seconds
  • minutes
  • hours
  • days
  • weeks

  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(precision, expression) 

参数：

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

  • year
  • month
  • week
  • day
  • hour
  • 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

日期截断

date_trunc的别名。

日期部分

以整数形式返回指定的日期部分。

date_part(part, expression)

参数：

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

  • year
  • month
  • week (week of the year)
  • day (day of the month)
  • hour
  • minute
  • second
  • millisecond
  • microsecond
  • nanosecond
  • dow (day of the week)
  • day (day of the year)

• 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

日期部分

date_part 的别名。

提取

以整数形式返回时间值的子字段。 与 date_part 类似，但参数不同。

extract(field FROM source)

参数

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

  • year
  • month
  • week (week of the year)
  • day (day of the month)
  • hour
  • minute
  • second
  • millisecond
  • microsecond
  • nanosecond
  • dow (day of the week)
  • day (day of the year)

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

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

从unix时间戳转换

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

from_unixtime(expression)

参数：

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

相关函数

to_unixtime

SELECT
  from_unixtime(1672531200000000000) AS RFC3339

创建日期

根据组成部分（年份、月份、日期）返回一个日期。

make_date(year, month, day)

参数

• year: 制作日期时使用的年份。可以是常量、列或函数，以及任何算术运算符的组合。
• month: 创建日期时使用的月份。 可以是常量、列或函数，以及任何算术运算符的组合。
• day: 用于生成日期的天数。 可以是常量、列或函数，以及任何组合的算术运算符

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

现在

返回当前的UTC时间戳。

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

now()

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

今天

current_date 的别名。

到字符

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

to_char(expression, format)

参数

• expression: 操作的表达式。可以是一个常量、列或返回日期、时间、时间戳或持续时间的函数。
• 格式: Rust Chrono 格式字符串 用于转换表达式。

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

转日期

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

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

参数

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

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

转换为本地时间

将带有时区的时间戳转换为不带时区的时间戳 （没有偏移或时区信息）。此函数考虑了时间变化 如夏令时（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

到时间戳

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

to_timestamp(expression)

参数：

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

SELECT to_timestamp(1704067200000000000)

将时间戳转换为微秒

将值转换为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

到时间戳毫秒

将值转换为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

到时间戳纳秒

将值转换为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

到时间戳（秒）

将值转换为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

转换为 Unix 时间戳

将一个值转换为自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

时区

将时间戳转换为提供的时区。如果没有提供第二个参数，默认使用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;