Miscellaneous SQL functions

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

• arrow_cast
• arrow_typeof
• get_field
• interpolate
• locf
• version

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

get_field

Returns a field from a map or a struct with the specified key.

get_field(collection, field)

Arguments

• collection: The map or struct to retrieve a field from.
• field: The name of field the field to retrieve from the map or struct. Must evaluate to a string.

SELECT
  get_field(influxdb_struct, 'version') AS influxdb_version
FROM
  (VALUES (struct('influxdb' AS product, 'v1' AS version)),
          (struct('influxdb' AS product, 'v2' AS version)),
          (struct('influxdb' AS product, 'v3' AS version))
  ) AS data(influxdb_struct)

SELECT
  get_field(influxdb_map, 'version') AS influxdb_version
FROM
  (VALUES (map {'product': 'influxdb', 'version': 'v1'}),
          (map {'product': 'influxdb', 'version': 'v2'}),
          (map {'product': 'influxdb', 'version': 'v3'})
  ) AS data(influxdb_map)

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

version

Returns the version of DataFusion.

version()