InfluxQL Continuous Queries

Introduction

Continuous queries (CQ) are InfluxQL queries that run automatically andperiodically on realtime data and store query results in aspecified measurement.

Basic SyntaxAdvanced SyntaxCQ Management
Examples of Basic SyntaxExamples of Advanced SyntaxCQ Use Cases
Common Issues with Basic SyntaxCommon Issues with Advanced SyntaxFurther information

Syntax

Basic syntax

  1. CREATE CONTINUOUS QUERY <cq_name> ON <database_name>
  2. BEGIN
  3.   <cq_query>
  4. END

Description of basic syntax

The cq_query

The cq_query requires afunction,an INTO clause,and a GROUP BY time() clause:

  1. SELECT <function[s]> INTO <destination_measurement> FROM <measurement> [WHERE <stuff>] GROUP BY time(<interval>)[,<tag_key[s]>]

Note: Notice that the cq_query does not require a time range in a WHERE clause.InfluxDB automatically generates a time range for the cq_query when it executes the CQ.Any user-specified time ranges in the cq_query’s WHERE clause will be ignoredby the system.

Schedule and coverage

Continuous queries operate on real-time data.They use the local server’s timestamp, the GROUP BY time() interval, andInfluxDB database’s preset time boundaries to determine when to execute and what timerange to cover in the query.

CQs execute at the same interval as the cq_query’s GROUP BY time() interval,and they run at the start of the InfluxDB database’s preset time boundaries.If the GROUP BY time() interval is one hour, the CQ executes at the start ofevery hour.

When the CQ executes, it runs a single query for the time range betweennow() and now() minus theGROUP BY time() interval.If the GROUP BY time() interval is one hour and the current time is 17:00,the query’s time range is between 16:00 and 16:59.999999999.

Examples of basic syntax

The examples below use the following sample data in the transportationdatabase.The measurement bus_data stores 15-minute resolution data on the number of buspassengers and complaints:

  1. name: bus_data

time passengers complaints2016-08-28T07:00:00Z 5 92016-08-28T07:15:00Z 8 92016-08-28T07:30:00Z 8 92016-08-28T07:45:00Z 7 92016-08-28T08:00:00Z 8 92016-08-28T08:15:00Z 15 72016-08-28T08:30:00Z 15 72016-08-28T08:45:00Z 17 72016-08-28T09:00:00Z 20 7

Automatically downsampling data

Use a simple CQ to automatically downsample data from a single fieldand write the results to another measurement in the same database.

  1. CREATE CONTINUOUS QUERY "cq_basic" ON "transportation"
  2. BEGIN
  3.   SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
  4. END

cq_basic calculates the average hourly number of passengers from thebus_data measurement and stores the results in the average_passengersmeasurement in the transportation database.

cq_basic executes at one-hour intervals, the same interval as theGROUP BY time() interval.Every hour, cq_basic runs a single query that covers the time range betweennow() and now() minus the GROUP BY time() interval, that is, the timerange between now() and one hour prior to now().

Annotated log output on the morning of August 28, 2016:

  1. >
  2. At **8:00** `cq_basic` executes a query with the time range `time >= '7:00' AND time < '08:00'`.
  3. `cq_basic` writes one point to the `average_passengers` measurement:
  4. >
  5.     name: average_passengers
  6.     ------------------------
  7.     time                   mean
  8.     2016-08-28T07:00:00Z   7
  9. >
  10. At **9:00** `cq_basic` executes a query with the time range `time >= '8:00' AND time < '9:00'`.
  11. `cq_basic` writes one point to the `average_passengers` measurement:
  12. >
  13.     name: average_passengers
  14.     ------------------------
  15.     time                   mean
  16.     2016-08-28T08:00:00Z   13.75

Here are the results:

  1. > SELECT * FROM "average_passengers"
  2. name: average_passengers
  3. ------------------------
  4. time                   mean
  5. 2016-08-28T07:00:00Z   7
  6. 2016-08-28T08:00:00Z   13.75
Automatically downsampling data into another retention policy

Fully qualifythe destination measurement to store the downsampled data in a non-DEFAULTretention policy (RP).

  1. CREATE CONTINUOUS QUERY "cq_basic_rp" ON "transportation"
  2. BEGIN
  3.   SELECT mean("passengers") INTO "transportation"."three_weeks"."average_passengers" FROM "bus_data" GROUP BY time(1h)
  4. END

cq_basic_rp calculates the average hourly number of passengers from thebus_data measurement and stores the results in the transportation database,the three_weeks RP, and the average_passengers measurement.

cq_basic_rp executes at one-hour intervals, the same interval as theGROUP BY time() interval.Every hour, cq_basic_rp runs a single query that covers the time range betweennow() and now() minus the GROUP BY time() interval, that is, the timerange between now() and one hour prior to now().

Annotated log output on the morning of August 28, 2016:

  1. >
  2. At **8:00** `cq_basic_rp` executes a query with the time range `time >= '7:00' AND time < '8:00'`.
  3. `cq_basic_rp` writes one point to the `three_weeks` RP and the `average_passengers` measurement:
  4. >
  5.     name: average_passengers
  6.     ------------------------
  7.     time                   mean
  8.     2016-08-28T07:00:00Z   7
  9. >
  10. At **9:00** `cq_basic_rp` executes a query with the time range
  11. `time >= '8:00' AND time < '9:00'`.
  12. `cq_basic_rp` writes one point to the `three_weeks` RP and the `average_passengers` measurement:
  13. >
  14.     name: average_passengers
  15.     ------------------------
  16.     time                   mean
  17.     2016-08-28T08:00:00Z   13.75

Here are the results:

  1. > SELECT * FROM "transportation"."three_weeks"."average_passengers"
  2. name: average_passengers
  3. ------------------------
  4. time                   mean
  5. 2016-08-28T07:00:00Z   7
  6. 2016-08-28T08:00:00Z   13.75

cq_basic_rp uses CQs and retention policies to automatically downsample dataand keep those downsampled data for an alternative length of time.See the Downsampling and Data Retentionguide for an in-depth discussion about this CQ use case.

Automatically downsampling a database with backreferencing

Use a function with a wildcard (*) and INTO query’sbackreferencing syntaxto automatically downsample data from all measurements and numerical fields ina database.

  1. CREATE CONTINUOUS QUERY "cq_basic_br" ON "transportation"
  2. BEGIN
  3.   SELECT mean(*) INTO "downsampled_transportation"."autogen".:MEASUREMENT FROM /.*/ GROUP BY time(30m),*
  4. END

cq_basic_br calculates the 30-minute average of passengers and complaintsfrom every measurement in the transportation database (in this case, there’s only thebus_data measurement).It stores the results in the downsampled_transportation database.

cq_basic_br executes at 30 minutes intervals, the same interval as theGROUP BY time() interval.Every 30 minutes, cq_basic_br runs a single query that covers the time rangebetween now() and now() minus the GROUP BY time() interval, that is,the time range between now() and 30 minutes prior to now().

Annotated log output on the morning of August 28, 2016:

  1. >
  2. At **7:30**, `cq_basic_br` executes a query with the time range `time >= '7:00' AND time < '7:30'`.
  3. `cq_basic_br` writes two points to the `bus_data` measurement in the `downsampled_transportation` database:
  4. >
  5.     name: bus_data
  6.     --------------
  7.     time                   mean_complaints   mean_passengers
  8.     2016-08-28T07:00:00Z   9                 6.5
  9. >
  10. At **8:00**, `cq_basic_br` executes a query with the time range `time >= '7:30' AND time < '8:00'`.
  11. `cq_basic_br` writes two points to the `bus_data` measurement in the `downsampled_transportation` database:
  12. >
  13.     name: bus_data
  14.     --------------
  15.     time                   mean_complaints   mean_passengers
  16.     2016-08-28T07:30:00Z   9                 7.5
  17. >
  18. [...]
  19. >
  20. At **9:00**, `cq_basic_br` executes a query with the time range `time >= '8:30' AND time < '9:00'`.
  21. `cq_basic_br` writes two points to the `bus_data` measurement in the `downsampled_transportation` database:
  22. >
  23.     name: bus_data
  24.     --------------
  25.     time                   mean_complaints   mean_passengers
  26.     2016-08-28T08:30:00Z   7                 16

Here are the results:

  1. > SELECT * FROM "downsampled_transportation."autogen"."bus_data"
  2. name: bus_data
  3. --------------
  4. time                   mean_complaints   mean_passengers
  5. 2016-08-28T07:00:00Z   9                 6.5
  6. 2016-08-28T07:30:00Z   9                 7.5
  7. 2016-08-28T08:00:00Z   8                 11.5
  8. 2016-08-28T08:30:00Z   7                 16
Automatically downsampling data and configuring CQ time boundaries

Use anoffset intervalin the GROUP BY time() clause to alter both the CQ’s default execution time andpreset time boundaries.

  1. CREATE CONTINUOUS QUERY "cq_basic_offset" ON "transportation"
  2. BEGIN
  3.   SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h,15m)
  4. END

cq_basic_offsetcalculates the average hourly number of passengers from thebus_data measurement and stores the results in the average_passengersmeasurement.

cq_basic_offset executes at one-hour intervals, the same interval as theGROUP BY time() interval.The 15 minute offset interval forces the CQ to execute 15 minutes after thedefault execution time; cq_basic_offset executes at 8:15 instead of 8:00.

Every hour, cq_basic_offset runs a single query that covers the time rangebetween now() and now() minus the GROUP BY time() interval, that is, thetime range between now() and one hour prior to now().The 15 minute offset interval shifts forward the generated preset time boundaries in theCQ’s WHERE clause; cq_basic_offset queries between 7:15 and 8:14.999999999 instead of 7:00 and 7:59.999999999.

Annotated log output on the morning of August 28, 2016:

  1. >
  2. At **8:15** `cq_basic_offset` executes a query with the time range `time >= '7:15' AND time < '8:15'`.
  3. `cq_basic_offset` writes one point to the `average_passengers` measurement:
  4. >
  5.     name: average_passengers
  6.     ------------------------
  7.     time                   mean
  8.     2016-08-28T07:15:00Z   7.75
  9. >
  10. At **9:15** `cq_basic_offset` executes a query with the time range `time >= '8:15' AND time < '9:15'`.
  11. `cq_basic_offset` writes one point to the `average_passengers` measurement:
  12. >
  13.     name: average_passengers
  14.     ------------------------
  15.     time                   mean
  16.     2016-08-28T08:15:00Z   16.75

Here are the results:

  1. > SELECT * FROM "average_passengers"
  2. name: average_passengers
  3. ------------------------
  4. time                   mean
  5. 2016-08-28T07:15:00Z   7.75
  6. 2016-08-28T08:15:00Z   16.75

Notice that the timestamps are for 7:15 and 8:15 instead of 7:00 and 8:00.

Common issues with basic syntax

Handling time intervals with no data

CQs do not write any results for a time interval if no data fall within thattime range.

Note that the basic syntax does not support usingfill()to change the value reported for intervals with no data.Basic syntax CQs ignore fill() if it’s included in the CQ query.A possible workaround is to use theadvanced CQ syntax.

Resampling previous time intervals

The basic CQ runs a single query that covers the time range between now()and now() minus the GROUP BY time() interval.See the advanced syntax for how to configure the query’stime range.

Backfilling results for older data

CQs operate on realtime data, that is, data with timestamps that occurrelative to now().Use a basicINTO queryto backfill results for data with older timestamps.

Missing tags in the CQ results

By default, allINTO queriesconvert any tags in the source measurement to fields in the destinationmeasurement.

Include GROUP BY * in the CQ to preserve tags in the destination measurement.

Advanced syntax

  1. CREATE CONTINUOUS QUERY <cq_name> ON <database_name>
  2. RESAMPLE EVERY <interval> FOR <interval>
  3. BEGIN
  4.   <cq_query>
  5. END

Description of advanced syntax

The cq_query

See Description of Basic Syntax.

Scheduling and coverage

CQs operate on real-time data. With the advanced syntax, CQs use the localserver’s timestamp, the information in the RESAMPLE clause, and the InfluxDBserver’s preset time boundaries to determine when to execute and what time range tocover in the query.

CQs execute at the same interval as the EVERY interval in the RESAMPLEclause, and they run at the start of InfluxDB’s preset time boundaries.If the EVERY interval is two hours, InfluxDB executes the CQ at the top ofevery other hour.

When the CQ executes, it runs a single query for the time range betweennow() and now() minus the FOR interval in the RESAMPLE clause.If the FOR interval is two hours and the current time is 17:00, the query’stime range is between 15:00 and 16:59.999999999.

Both the EVERY interval and the FOR interval acceptduration literals.The RESAMPLE clause works with either or both of the EVERY and FOR intervalsconfigured.CQs default to the relevantbasic syntax behaviorif the EVERY interval or FOR interval is not provided (see the first issue inCommon Issues with Advanced Syntaxfor an anomalous case).

Examples of advanced syntax

The examples below use the following sample data in the transportation database.The measurement bus_data stores 15-minute resolution data on the number of buspassengers:

  1. name: bus_data

time passengers2016-08-28T06:30:00Z 22016-08-28T06:45:00Z 42016-08-28T07:00:00Z 52016-08-28T07:15:00Z 82016-08-28T07:30:00Z 82016-08-28T07:45:00Z 72016-08-28T08:00:00Z 82016-08-28T08:15:00Z 152016-08-28T08:30:00Z 152016-08-28T08:45:00Z 172016-08-28T09:00:00Z 20

Configuring execution intervals

Use an EVERY interval in the RESAMPLE clause to specify the CQ’s executioninterval.

  1. CREATE CONTINUOUS QUERY "cq_advanced_every" ON "transportation"
  2. RESAMPLE EVERY 30m
  3. BEGIN
  4.   SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
  5. END

cq_advanced_every calculates the one-hour average of passengersfrom the bus_data measurement and stores the results in theaverage_passengers measurement in the transportation database.

cq_advanced_every executes at 30-minute intervals, the same interval as theEVERY interval.Every 30 minutes, cq_advanced_every runs a single query that covers the timerange for the current time bucket, that is, the one-hour time bucket thatintersects with now().

Annotated log output on the morning of August 28, 2016:

  1. >
  2. At **8:00**, `cq_advanced_every` executes a query with the time range `WHERE time >= '7:00' AND time < '8:00'`.
  3. `cq_advanced_every` writes one point to the `average_passengers` measurement:
  4. >
  5.     name: average_passengers
  6.     ------------------------
  7.     time                   mean
  8.     2016-08-28T07:00:00Z   7
  9. >
  10. At **8:30**, `cq_advanced_every` executes a query with the time range `WHERE time >= '8:00' AND time < '9:00'`.
  11. `cq_advanced_every` writes one point to the `average_passengers` measurement:
  12. >
  13.     name: average_passengers
  14.     ------------------------
  15.     time                   mean
  16.     2016-08-28T08:00:00Z   12.6667
  17. >
  18. At **9:00**, `cq_advanced_every` executes a query with the time range `WHERE time >= '8:00' AND time < '9:00'`.
  19. `cq_advanced_every` writes one point to the `average_passengers` measurement:
  20. >
  21.     name: average_passengers
  22.     ------------------------
  23.     time                   mean
  24.     2016-08-28T08:00:00Z   13.75

Here are the results:

  1. > SELECT * FROM "average_passengers"
  2. name: average_passengers
  3. ------------------------
  4. time                   mean
  5. 2016-08-28T07:00:00Z   7
  6. 2016-08-28T08:00:00Z   13.75

Notice that cq_advanced_every calculates the result for the 8:00 time intervaltwice.First, it runs at 8:30 and calculates the average for every available data pointbetween 8:00 and 9:00 (8,15, and 15).Second, it runs at 9:00 and calculates the average for every available datapoint between 8:00 and 9:00 (8, 15, 15, and 17).Because of the way InfluxDBhandles duplicate points, the second result simply overwrites the first result.

Configuring time ranges for resampling

Use a FOR interval in the RESAMPLE clause to specify the length of the CQ’stime range.

  1. CREATE CONTINUOUS QUERY "cq_advanced_for" ON "transportation"
  2. RESAMPLE FOR 1h
  3. BEGIN
  4.   SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(30m)
  5. END

cq_advanced_for calculates the 30-minute average of passengersfrom the bus_data measurement and stores the results in the average_passengersmeasurement in the transportation database.

cq_advanced_for executes at 30-minute intervals, the same interval as theGROUP BY time() interval.Every 30 minutes, cq_advanced_for runs a single query that covers the timerange between now() and now() minus the FOR interval, that is, the timerange between now() and one hour prior to now().

Annotated log output on the morning of August 28, 2016:

  1. >
  2. At **8:00** `cq_advanced_for` executes a query with the time range `WHERE time >= '7:00' AND time < '8:00'`.
  3. `cq_advanced_for` writes two points to the `average_passengers` measurement:
  4. >
  5.     name: average_passengers
  6.     ------------------------
  7.     time                   mean
  8.     2016-08-28T07:00:00Z   6.5
  9.     2016-08-28T07:30:00Z   7.5
  10. >
  11. At **8:30** `cq_advanced_for` executes a query with the time range  `WHERE time >= '7:30' AND time < '8:30'`.
  12. `cq_advanced_for` writes two points to the `average_passengers` measurement:
  13. >
  14.     name: average_passengers
  15.     ------------------------
  16.     time                   mean
  17.     2016-08-28T07:30:00Z   7.5
  18.     2016-08-28T08:00:00Z   11.5
  19. >
  20. At **9:00** `cq_advanced_for` executes a query with the time range `WHERE time >= '8:00' AND time < '9:00'`.
  21. `cq_advanced_for` writes two points to the `average_passengers` measurement:
  22. >
  23.     name: average_passengers
  24.     ------------------------
  25.     time                   mean
  26.     2016-08-28T08:00:00Z   11.5
  27.     2016-08-28T08:30:00Z   16

Notice that cq_advanced_for will calculate the result for every time intervaltwice.The CQ calculates the average for the 7:30 time interval at 8:00 and at 8:30,and it calculates the average for the 8:00 time interval at 8:30 and 9:00.

Here are the results:

  1. > SELECT * FROM "average_passengers"
  2. name: average_passengers
  3. ------------------------
  4. time                   mean
  5. 2016-08-28T07:00:00Z   6.5
  6. 2016-08-28T07:30:00Z   7.5
  7. 2016-08-28T08:00:00Z   11.5
  8. 2016-08-28T08:30:00Z   16
Configuring execution intervals and CQ time ranges

Use an EVERY interval and FOR interval in the RESAMPLE clause to specifythe CQ’s execution interval and the length of the CQ’s time range.

  1. CREATE CONTINUOUS QUERY "cq_advanced_every_for" ON "transportation"
  2. RESAMPLE EVERY 1h FOR 90m
  3. BEGIN
  4.   SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(30m)
  5. END

cq_advanced_every_for calculates the 30-minute average ofpassengers from the bus_data measurement and stores the results in theaverage_passengers measurement in the transportation database.

cq_advanced_every_for executes at one-hour intervals, the same interval as theEVERY interval.Every hour, cq_advanced_every_for runs a single query that covers the timerange between now() and now() minus the FOR interval, that is, the timerange between now() and 90 minutes prior to now().

Annotated log output on the morning of August 28, 2016:

  1. >
  2. At **8:00** `cq_advanced_every_for` executes a query with the time range `WHERE time >= '6:30' AND time < '8:00'`.
  3. `cq_advanced_every_for` writes three points to the `average_passengers` measurement:
  4. >
  5.     name: average_passengers
  6.     ------------------------
  7.     time                   mean
  8.     2016-08-28T06:30:00Z   3
  9.     2016-08-28T07:00:00Z   6.5
  10.     2016-08-28T07:30:00Z   7.5
  11. >
  12. At **9:00** `cq_advanced_every_for` executes a query with the time range `WHERE time >= '7:30' AND time < '9:00'`.
  13. `cq_advanced_every_for` writes three points to the `average_passengers` measurement:
  14. >
  15.     name: average_passengers
  16.     ------------------------
  17.     time                   mean
  18.     2016-08-28T07:30:00Z   7.5
  19.     2016-08-28T08:00:00Z   11.5
  20.     2016-08-28T08:30:00Z   16

Notice that cq_advanced_every_for will calculate the result for every timeinterval twice.The CQ calculates the average for the 7:30 interval at 8:00 and 9:00.

Here are the results:

  1. > SELECT * FROM "average_passengers"
  2. name: average_passengers
  3. ------------------------
  4. time                   mean
  5. 2016-08-28T06:30:00Z   3
  6. 2016-08-28T07:00:00Z   6.5
  7. 2016-08-28T07:30:00Z   7.5
  8. 2016-08-28T08:00:00Z   11.5
  9. 2016-08-28T08:30:00Z   16
Configuring CQ time ranges and filling empty results

Use a FOR interval and fill() to change the value reported for timeintervals with no data.Note that at least one data point must fall within the FOR interval for fill()to operate.If no data fall within the FOR interval the CQ writes no points to thedestination measurement.

  1. CREATE CONTINUOUS QUERY "cq_advanced_for_fill" ON "transportation"
  2. RESAMPLE FOR 2h
  3. BEGIN
  4.   SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h) fill(1000)
  5. END

cq_advanced_for_fill calculates the one-hour average of passengers from thebus_data measurement and stores the results in the average_passengersmeasurement in the transportation database.Where possible, it writes the value 1000 for time intervals with no results.

cq_advanced_for_fill executes at one-hour intervals, the same interval as theGROUP BY time() interval.Every hour, cq_advanced_for_fill runs a single query that covers the timerange between now() and now() minus the FOR interval, that is, the timerange between now() and two hours prior to now().

Annotated log output on the morning of August 28, 2016:

  1. >
  2. At **6:00**, `cq_advanced_for_fill` executes a query with the time range `WHERE time >= '4:00' AND time < '6:00'`.
  3. `cq_advanced_for_fill` writes nothing to `average_passengers`; `bus_data` has no data
  4. that fall within that time range.
  5. >
  6. At **7:00**, `cq_advanced_for_fill` executes a query with the time range `WHERE time >= '5:00' AND time < '7:00'`.
  7. `cq_advanced_for_fill` writes two points to `average_passengers`:
  8. >
  9.     name: average_passengers
  10.     ------------------------
  11.     time                   mean
  12.     2016-08-28T05:00:00Z   1000          <------ fill(1000)
  13.     2016-08-28T06:00:00Z   3             <------ average of 2 and 4
  14. >
  15. [...]
  16. >
  17. At **11:00**, `cq_advanced_for_fill` executes a query with the time range `WHERE time >= '9:00' AND time < '11:00'`.
  18. `cq_advanced_for_fill` writes two points to `average_passengers`:
  19. >
  20.     name: average_passengers
  21.     ------------------------
  22.     2016-08-28T09:00:00Z   20            <------ average of 20
  23.     2016-08-28T10:00:00Z   1000          <------ fill(1000)
  24. >

At 12:00, cq_advanced_for_fill executes a query with the time range WHERE time >= '10:00' AND time < '12:00'.cq_advanced_for_fill writes nothing to average_passengers; bus_data has no datathat fall within that time range.

Here are the results:

  1. > SELECT * FROM "average_passengers"
  2. name: average_passengers
  3. ------------------------
  4. time                   mean
  5. 2016-08-28T05:00:00Z   1000
  6. 2016-08-28T06:00:00Z   3
  7. 2016-08-28T07:00:00Z   7
  8. 2016-08-28T08:00:00Z   13.75
  9. 2016-08-28T09:00:00Z   20
  10. 2016-08-28T10:00:00Z   1000

Note: fill(previous) doesn’t fill the result for a time interval if theprevious value is outside the query’s time range.See Frequently Asked Questionsfor more information.

Common issues with advanced syntax

If the EVERY interval is greater than the GROUP BY time() interval

If the EVERY interval is greater than the GROUP BY time() interval, the CQexecutes at the same interval as the EVERY interval and runs a single querythat covers the time range between now() and now() minus the EVERYinterval (not between now() and now() minus the GROUP BY time() interval).

For example, if the GROUP BY time() interval is 5m and the EVERY intervalis 10m, the CQ executes every ten minutes.Every ten minutes, the CQ runs a single query that covers the time rangebetween now() and now() minus the EVERY interval, that is, the timerange between now() and ten minutes prior to now().

This behavior is intentional and prevents the CQ from missing data betweenexecution times.

If the FOR interval is less than the execution interval

If the FOR interval is less than the GROUP BY time() interval or, ifspecified, the EVERY interval, InfluxDB returns the following error:

  1. error parsing query: FOR duration must be >= GROUP BY time duration: must be a minimum of <minimum-allowable-interval> got <user-specified-interval>

To avoid missing data between execution times, the FOR interval must be equalto or greater than the GROUP BY time() interval or, if specified, the EVERYinterval.

Currently, this is the intended behavior.GitHub Issue #6963outlines a feature request for CQs to support gaps in data coverage.

Continuous query management

Only admin users are allowed to work with CQs. For more on user privileges, see Authentication and Authorization.

Listing continuous queries

List every CQ on an InfluxDB instance with:

  1. SHOW CONTINUOUS QUERIES

SHOW CONTINUOUS QUERIES groups results by database.

Examples

The output shows that the telegraf and mydb databases have CQs:

  1. > SHOW CONTINUOUS QUERIES
  2. name: _internal
  3. ---------------
  4. name   query
  7. name: telegraf
  8. --------------
  9. name           query
  10. idle_hands     CREATE CONTINUOUS QUERY idle_hands ON telegraf BEGIN SELECT min(usage_idle) INTO telegraf.autogen.min_hourly_cpu FROM telegraf.autogen.cpu GROUP BY time(1h) END
  11. feeling_used   CREATE CONTINUOUS QUERY feeling_used ON telegraf BEGIN SELECT mean(used) INTO downsampled_telegraf.autogen.:MEASUREMENT FROM telegraf.autogen./.*/ GROUP BY time(1h) END
  14. name: downsampled_telegraf
  15. --------------------------
  16. name   query
  19. name: mydb
  20. ----------
  21. name      query
  22. vampire   CREATE CONTINUOUS QUERY vampire ON mydb BEGIN SELECT count(dracula) INTO mydb.autogen.all_of_them FROM mydb.autogen.one GROUP BY time(5m) END

Deleting continuous queries

Delete a CQ from a specific database with:

  1. DROP CONTINUOUS QUERY <cq_name> ON <database_name>

DROP CONTINUOUS QUERY returns an empty result.

Examples

Drop the idle_hands CQ from the telegraf database:

  1. > DROP CONTINUOUS QUERY "idle_hands" ON "telegraf"`
  2. >

Altering continuous queries

CQs cannot be altered once they’re created.To change a CQ, you must DROP and reCREATE it with the updated settings.

Continuous query statistics

If query-stats-enabled is set to true in your influxdb.conf or using the INFLUXDB_CONTINUOUS_QUERIES_QUERY_STATS_ENABLED environment variable, data will be written to _internal with information about when continuous queries ran and their duration.Information about CQ configuration settings is available in the Configuration documentation.

Note: _internal houses internal system data and is meant for internal use.The structure of and data stored in _internal can change at any time.Use of this data falls outside the scope of official InfluxData support.

Continuous query use cases

Downsampling and Data Retention

Use CQs with InfluxDB databaseretention policies(RPs) to mitigate storage concerns.Combine CQs and RPs to automatically downsample high precision data to a lowerprecision and remove the dispensable, high precision data from the database.

See theDownsampling and data retentionguide for a detailed walkthrough of this common use case.

Precalculating expensive queries

Shorten query runtimes by pre-calculating expensive queries with CQs.Use a CQ to automatically downsample commonly-queried, high precision data to alower precision.Queries on lower precision data require fewer resources and return faster.

Tip: Pre-calculate queries for your preferred graphing tool to acceleratethe population of graphs and dashboards.

Substituting for a HAVING clause

InfluxQL does not support HAVING clauses.Get the same functionality by creating a CQ to aggregate the data and queryingthe CQ results to apply the HAVING clause.

Note: InfluxQL supports subqueries which also offer similar functionality to HAVING clauses.See Data Exploration for more information.

Example

InfluxDB does not accept the following query with a HAVING clause.The query calculates the average number of bees at 30 minute intervals andrequests averages that are greater than 20.

  1. SELECT mean("bees") FROM "farm" GROUP BY time(30m) HAVING mean("bees") > 20

To get the same results:

1. Create a CQ

This step performs the mean("bees") part of the query above.Because this step creates CQ you only need to execute it once.

The following CQ automatically calculates the average number of bees at30 minutes intervals and writes those averages to the mean_bees field in theaggregate_bees measurement.

  1. CREATE CONTINUOUS QUERY "bee_cq" ON "mydb" BEGIN SELECT mean("bees") AS "mean_bees" INTO "aggregate_bees" FROM "farm" GROUP BY time(30m) END

2. Query the CQ results

This step performs the HAVING mean("bees") > 20 part of the query above.

Query the data in the measurement aggregate_bees and request values of the mean_bees field that are greater than 20 in the WHERE clause:

  1. SELECT "mean_bees" FROM "aggregate_bees" WHERE "mean_bees" > 20

Substituting for nested functions

Some InfluxQL functionssupport nestingof other functions.Most do not.If your function does not support nesting, you can get the same functionality using a CQ to calculatethe inner-most function.Then simply query the CQ results to calculate the outer-most function.

Note: InfluxQL supports subqueries which also offer the same functionality as nested functions.See Data Exploration for more information.

Example

InfluxDB does not accept the following query with a nested function.The query calculates the number of non-null valuesof bees at 30 minute intervals and the average of those counts:

  1. SELECT mean(count("bees")) FROM "farm" GROUP BY time(30m)

To get the same results:

1. Create a CQ

This step performs the count("bees") part of the nested function above.Because this step creates a CQ you only need to execute it once.

The following CQ automatically calculates the number of non-null values of bees at 30 minute intervalsand writes those counts to the count_bees field in the aggregate_bees measurement.

  1. CREATE CONTINUOUS QUERY "bee_cq" ON "mydb" BEGIN SELECT count("bees") AS "count_bees" INTO "aggregate_bees" FROM "farm" GROUP BY time(30m) END

2. Query the CQ results

This step performs the mean([…]) part of the nested function above.

Query the data in the measurement aggregate_bees to calculate the average of thecount_bees field:

  1. SELECT mean("count_bees") FROM "aggregate_bees" WHERE time >= <start_time> AND time <= <end_time>

Further information

To see how to combine two InfluxDB features, CQs, and retention policies,to periodically downsample data and automatically expire the dispensable highprecision data, see Downsampling and data retention.

Kapacitor, InfluxData’s data processing engine, can do the same work ascontinuous queries in InfluxDB databases.

To learn when to use Kapacitor instead of InfluxDB and how to perform the same CQfunctionality with a TICKscript, see examples of continuous queries in Kapacitor.