docs-v2/content/shared/influxql-v3-reference/functions/misc.md

Use InfluxQL miscellaneous functions to perform different operations in InfluxQL queries.

• fill()

fill()

Fills null field values returned from empty time windows in GROUP BY time() queries with a specified fill value.

Supported only in the GROUP BY clause.

fill(behavior)

Arguments

• behavior: Defines the behavior of the fill operation. If no FILL clause is included, the default behavior is fill(null).

  The following options are available:

  • numeric literal: Replaces null values with the specified numeric literal.
  • linear: Uses linear interpolation between existing values to replace null values.
  • none: Removes rows with null field values.
  • null: Keeps null values and associated timestamps.
  • previous: Replaces null values with the most recent non-null value.

Examples

The following example uses the Bitcoin price sample dataset.

{{< tabs-wrapper >}} {{% tabs "small" %}} fill(numeric_literal) fill(linear) fill(none) fill(null) fill(previous) {{% /tabs %}} {{% tab-content %}}

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-01T02:00:00Z'
GROUP BY
  time(30m)
  fill(0)

{{% influxql/table-meta %}} name: bitcoin {{% /influxql/table-meta %}}

time	mean
2023-05-01T00:00:00Z	29319.9092
2023-05-01T00:30:00Z	29307.4416
2023-05-01T01:00:00Z	0
2023-05-01T01:30:00Z	29263.2886

{{% /tab-content %}} {{% tab-content %}}

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-01T02:00:00Z'
GROUP BY
  time(30m)
  fill(linear)

{{% influxql/table-meta %}} name: bitcoin {{% /influxql/table-meta %}}

time	mean
2023-05-01T00:00:00Z	29319.9092
2023-05-01T00:30:00Z	29307.4416
2023-05-01T01:00:00Z	29285.3651
2023-05-01T01:30:00Z	29263.2886

{{% /tab-content %}} {{% tab-content %}}

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-01T02:00:00Z'
GROUP BY
  time(30m)
  fill(none)

{{% influxql/table-meta %}} name: bitcoin {{% /influxql/table-meta %}}

time	mean
2023-05-01T00:00:00Z	29319.9092
2023-05-01T00:30:00Z	29307.4416
2023-05-01T01:30:00Z	29263.2886

{{% /tab-content %}} {{% tab-content %}}

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-01T02:00:00Z'
GROUP BY
  time(30m)
  fill(null)

{{% influxql/table-meta %}} name: bitcoin {{% /influxql/table-meta %}}

time	mean
2023-05-01T00:00:00Z	29319.9092
2023-05-01T00:30:00Z	29307.4416
2023-05-01T01:00:00Z
2023-05-01T01:30:00Z	29263.2886

{{% /tab-content %}} {{% tab-content %}}

SELECT
  MEAN(price)
FROM bitcoin
WHERE
  code = 'USD'
  AND time >= '2023-05-01T00:00:00Z'
  AND time < '2023-05-01T02:00:00Z'
GROUP BY
  time(30m)
  fill(previous)

{{% influxql/table-meta %}} name: bitcoin {{% /influxql/table-meta %}}

time	mean
2023-05-01T00:00:00Z	29319.9092
2023-05-01T00:30:00Z	29307.4416
2023-05-01T01:00:00Z	29307.4416
2023-05-01T01:30:00Z	29263.2886

{{% /tab-content %}} {{< /tabs-wrapper >}}