Miscellaneous SQL functions

The InfluxDB Clustered SQL implementation supports the following miscellaneous functions for performing a variety of operations:

• arrow_cast
• arrow_typeof
• interpolate
• locf

arrow_cast

Casts a value to a specific Arrow data type.

arrow_cast(expression, datatype)

Arguments

• expression: Expression to cast. Can be a constant, column, or function, and any combination of arithmetic or string operators.
• datatype: Arrow data type to cast to.

SELECT
  arrow_cast(time, 'Int64') AS time,
  arrow_cast(temp, 'Utf8') AS temp,
  arrow_cast(co, 'Float64')AS co
FROM home
LIMIT 1

arrow_typeof

Returns the underlying Arrow data type of the expression:

arrow_typeof(expression)

Arguments

• expression: Expression to evaluate. Can be a constant, column, or function, and any combination of arithmetic or string operators.

SELECT
  arrow_typeof(time) AS time,
  arrow_typeof(room) AS room,
  arrow_typeof(temp) AS temp,
  arrow_typeof(co) AS co
FROM home
LIMIT 1

interpolate

Fills null values in a specified aggregated column by interpolating values from existing values. Must be used with date_bin_gapfill.

interpolate(aggregate_expression)

Arguments

• aggregate_expression: Aggregate operation on a specified expression. The operation can use any aggregate function. The expression can be a constant, column, or function, and any combination of arithmetic operators supported by the aggregate function.

Related functions

date_bin_gapfill, locf

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

locf

Fills null values in a specified aggregated column by carrying the last observed value forward. Must be used with date_bin_gapfill.

LOCF is an initialism of “last observation carried forward.”

locf(aggregate_expression)

Arguments

• aggregate_expression: Aggregate operation on a specified expression. The operation can use any aggregate function. The expression can be a constant, column, or function, and any combination of arithmetic operators supported by the aggregate function.

Related functions

date_bin_gapfill, interpolate

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