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