---
title: Miscellaneous SQL functions
description: Use miscellaneous SQL functions to perform a variety of operations in SQL queries.
url: https://docs.influxdata.com/influxdb3/cloud-serverless/reference/sql/functions/misc/
estimated_tokens: 6856
product: InfluxDB Cloud Serverless
version: cloud-serverless
---

# Miscellaneous SQL functions

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

-   [arrow\_cast](#arrow_cast)
-   [arrow\_typeof](#arrow_typeof)
-   [get\_field](#get_field)
-   [interpolate](#interpolate)
-   [locf](#locf)
-   [version](#version)

## arrow\_cast

Casts a value to a specific Arrow data type.

```sql
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](/influxdb3/cloud-serverless/reference/sql/data-types/#sql-and-arrow-data-types) to cast to.

[](#view-arrow_cast-query-example)

View `arrow_cast` query example

*The following example uses the sample data set provided in [Get started with InfluxDB tutorial](/influxdb3/cloud-serverless/get-started/write/#construct-line-protocol).*

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

| time | temp | co |
| --- | --- | --- |
| 1641024000000000000 | 21.0 | 0 |

## arrow\_typeof

Returns the underlying [Arrow data type](https://arrow.apache.org/datafusion/user-guide/sql/data_types.html) of the expression:

```sql
arrow_typeof(expression)
```

### Arguments

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

[](#view-arrow_typeof-query-example)

View `arrow_typeof` query example

*The following example uses the sample data set provided in [Get started with InfluxDB tutorial](/influxdb3/cloud-serverless/get-started/write/#construct-line-protocol).*

```sql
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
```

| time | room | temp | co |
| --- | --- | --- | --- |
| Timestamp(Nanosecond, None) | Dictionary(Int32, Utf8) | Float64 | Int64 |

## get\_field

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

Typically, `get_field` is indirectly invoked via field access syntax such as `my_struct['field_name']` which results in the call: `get_field(my_struct, 'field_name')`.

```sql
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.

[](#view-get_field-example-with-a-struct-column)

View `get_field` example with a struct column

```sql
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)
```

| influxdb_version |
| --- |
| v1 |
| v2 |
| v3 |

[](#view-get_field-example-with-a-map-column)

View `get_field` example with a map column

```sql
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)
```

| influxdb_version |
| --- |
| v1 |
| v2 |
| v3 |

## interpolate

Fills null values in a specified aggregated column by interpolating values from existing values. Must be used with [`date_bin_gapfill`](/influxdb3/cloud-serverless/reference/sql/functions/time-and-date/#date_bin_gapfill).

```sql
interpolate(aggregate_expression)
```

### Arguments

-   **aggregate\_expression**: Aggregate operation on a specified expression. The operation can use any [aggregate function](/influxdb3/cloud-serverless/reference/sql/functions/aggregate/). 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](/influxdb3/cloud-serverless/reference/sql/functions/time-and-date/#date_bin_gapfill), [locf](#locf)

[](#view-interpolate-query-example)

View `interpolate` query example

*The following example uses the sample data set provided in the [Get started with InfluxDB tutorial](/influxdb3/cloud-serverless/get-started/write/#construct-line-protocol).*

```sql
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
```

| _time | room | AVG(home.temp) |
| --- | --- | --- |
| 2022-01-01T08:00:00Z | Kitchen | 21 |
| 2022-01-01T08:30:00Z | Kitchen | 22 |
| 2022-01-01T09:00:00Z | Kitchen | 23 |
| 2022-01-01T09:30:00Z | Kitchen | 22.85 |
| 2022-01-01T10:00:00Z | Kitchen | 22.7 |
| 2022-01-01T08:00:00Z | Living Room | 21.1 |
| 2022-01-01T08:30:00Z | Living Room | 21.25 |
| 2022-01-01T09:00:00Z | Living Room | 21.4 |
| 2022-01-01T09:30:00Z | Living Room | 21.6 |
| 2022-01-01T10:00:00Z | Living Room | 21.8 |

## locf

Fills null values in a specified aggregated column by carrying the last observed value forward. Must be used with [`date_bin_gapfill`](/influxdb3/cloud-serverless/reference/sql/functions/time-and-date/#date_bin_gapfill).

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

```sql
locf(aggregate_expression)
```

### Arguments

-   **aggregate\_expression**: Aggregate operation on a specified expression. The operation can use any [aggregate function](/influxdb3/cloud-serverless/reference/sql/functions/aggregate/). 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](/influxdb3/cloud-serverless/reference/sql/functions/time-and-date/#date_bin_gapfill), [interpolate](#interpolate)

[](#view-locf-query-example)

View `locf` query example

*The following example uses the sample data set provided in the [Get started with InfluxDB tutorial](/influxdb3/cloud-serverless/get-started/write/#construct-line-protocol).*

```sql
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
```

| _time | room | AVG(home.temp) |
| --- | --- | --- |
| 2022-01-01T08:00:00Z | Kitchen | 21 |
| 2022-01-01T08:30:00Z | Kitchen | 21 |
| 2022-01-01T09:00:00Z | Kitchen | 23 |
| 2022-01-01T09:30:00Z | Kitchen | 23 |
| 2022-01-01T10:00:00Z | Kitchen | 22.7 |
| 2022-01-01T08:00:00Z | Living Room | 21.1 |
| 2022-01-01T08:30:00Z | Living Room | 21.1 |
| 2022-01-01T09:00:00Z | Living Room | 21.4 |
| 2022-01-01T09:30:00Z | Living Room | 21.4 |
| 2022-01-01T10:00:00Z | Living Room | 21.8 |

## version

Returns the version of [DataFusion](https://datafusion.apache.org/) used by the query engine.

The `version()` function returns the DataFusion query engine version, not the InfluxDB product version. To identify your InfluxDB version, see [Identify version](/influxdb3/cloud-serverless/admin/identify-version/).

```sql
version()
```

[](#view-version-query-example)

View `version` query example

```sql
SELECT version()
```

| version() |
| --- |
| Apache DataFusion 49.0.2, aarch64 on linux |

The output includes the DataFusion version, CPU architecture, and operating system.