Predict time series field values with the HOLT_WINTERS() function - Time Series Database

HOLT_WINTERS() returns N predicted field values using the Holt-Winters seasonal method. Use it to:

Predict when a field value will cross a given threshold.
Compare predicted values with actual values to detect anomalies.

Syntax

SELECT HOLT_WINTERS[_WITH-FIT](<function>(<field_key>),<N>,<S>) [INTO_clause] FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

Parameters

Parameter	Description
`N`	Number of predicted field values to return. Each predicted point is spaced at the same time interval as specified in `GROUP BY time()`. For example, `GROUP BY time(6m)` with `N=3` returns 3 predicted values at 6-minute intervals.
`S`	Seasonal pattern length, measured in data points. The seasonal period equals `S` × the `GROUP BY time()` interval. For example, `GROUP BY time(2m)` with `S=3` produces a seasonal pattern every 6 minutes (every 3 data points). Set `S` to `0` or `1` to disable seasonal adjustment.

Function variants

Variant	Returns
`HOLT_WINTERS(function(field_key), N, S)`	N seasonally adjusted predicted field values.
`HOLT_WINTERS_WITH_FIT(function(field_key), N, S)`	Fitted values for the input data, plus N seasonally adjusted predicted field values.

Both variants support INT64 and FLOAT64 field values.

Working with unevenly spaced data

Both functions require data at equal time intervals. To process unevenly spaced data, wrap the field key in an InfluxQL aggregate or selector function (such as FIRST()) and include a GROUP BY time() clause. This normalizes the data into equal-length time buckets before the Holt-Winters calculation runs.

Predict field values

This example uses the NOAA_water_database dataset and Chronograf for visualization.

The goal is to predict future water_level values at location='santa_monica' for the period 2015-08-22 22:12:00 to 2015-08-28 03:00:00.

Step 1: Match the trend of the raw data

Write a GROUP BY time() query that captures the overall shape of the water_level data. This step determines the GROUP BY time() interval you'll use in the final prediction query.

Step 2: Determine the seasonal pattern

Examine the query results from Step 1 to identify the repeating cycle in the data.

Step 3: Apply HOLT_WINTERS()

Add the Holt-Winters function to the query, using the interval from Step 1 and the seasonal pattern from Step 2.

SELECT HOLT_WINTERS_WITH_FIT(FIRST("water_level"), 10, 4)
FROM "NOAA_water_database"."autogen"."h2o_feet"
WHERE "location" = 'santa_monica'
  AND time >= '2015-08-22 22:12:00'
  AND time <= '2015-08-28 03:00:00'
GROUP BY time(379m, 348m)

In HOLT_WINTERS_WITH_FIT(FIRST("water_level"), 10, 4):

10 is N — the query returns 10 predicted values, each spaced 379m apart (the same interval as in GROUP BY time()).
4 is S — the seasonal pattern identified in Step 2: 4 data points per season, one season every 25 hours and 15 minutes.

FAQ

Why does the query return fewer predicted points than N?

When the Holt-Winters algorithm encounters numerical instability in the data, it stops generating predictions early rather than producing unreliable values.

The most common causes are:

The dataset does not have a consistent, repeating pattern over the query window.
The S value does not match the actual cycle length in the data.

To diagnose the issue, verify that your data has a regular seasonal pattern. Then re-examine Steps 1 and 2: the GROUP BY time() interval should align with the distance between peaks and troughs, and S should equal the number of data points in one complete cycle. Adjusting these two values is usually enough to resolve the problem.