Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 39 ยท Querying: Flux and SQL

SQL Time-series Queries in InfluxDB 3

Query InfluxDB 3 with SQL: time filters, date_bin windows, gap filling, window functions, joins between measurements and selector functions, with runnable SQLite stand-ins.

InfluxDB 3 stores each measurement as a table and answers queries with standard SQL powered by Apache DataFusion. Compared with Flux and InfluxQL this is a big step: you get joins, subqueries, common table expressions, window functions and the whole ecosystem of SQL tools and BI products, plus a few time-series functions on top.

The InfluxDB SQL statements are read-only here (they need a 3.x server). Every example is paired with a runnable SQLite version that uses the portable parts of SQL (GROUP BY on a time bucket, window functions, joins, CTEs). SQLite has no date_bin, so the stand-ins compute buckets with arithmetic. The result is identical in spirit, not in syntax.

Tables, tags and fields in SQL

A measurement cpu with tags host, region and fields usage_user, usage_system is the table:

time host region usage_user usage_system
2026-03-01T10:00:00Z web-01 eu 41.0 7.5

Every table has a time column of type Timestamp(Nanosecond). Always filter on time: it is the partition key of the storage, so a query without a time predicate scans every Parquet file, which is the SQL equivalent of a Flux query with no range.

TEXT
SELECT time, host, usage_user
FROM cpu
WHERE time >= now() - INTERVAL '1 hour'
  AND host = 'web-01'
ORDER BY time DESC
LIMIT 20

Useful time expressions: now(), INTERVAL '10 minutes', '2026-03-01T00:00:00Z'::TIMESTAMP, date_trunc('hour', time), to_timestamp(...).

Windows with date_bin

date_bin(interval, time [, origin]) rounds each timestamp down to the start of its bin, which is the SQL way to write GROUP BY time(5m):

TEXT
SELECT date_bin(INTERVAL '5 minutes', time) AS window_start,
       host,
       avg(usage_user)  AS usage_mean,
       max(usage_user)  AS usage_max,
       count(*)         AS samples
FROM cpu
WHERE time >= now() - INTERVAL '6 hours'
GROUP BY window_start, host
ORDER BY window_start, host

The optional origin changes where bins are aligned, for example to start days at local midnight. Bins that contain no rows are simply absent from the result unless you use gap filling.

Gap filling

InfluxDB 3 adds a gap-filling aggregate window function:

TEXT
SELECT date_bin_gapfill(INTERVAL '10 minutes', time) AS window_start,
       host,
       interpolate(avg(usage_user)) AS usage_filled
FROM cpu
WHERE time >= '2026-03-01T10:00:00Z' AND time < '2026-03-01T12:00:00Z' AND host = 'web-01'
GROUP BY window_start, host

date_bin_gapfill produces a row for every bin; locf() (last observation carried forward) and interpolate() fill the empty ones. Gap filling needs both bounds of the time range in the WHERE clause. This is the equivalent of fill(previous) or fill(linear) in InfluxQL.

Stand-in: bucket, aggregate, moving average

Here is bucketing plus a moving average, written with ordinary SQL window functions that InfluxDB 3's engine supports as well. SQLite runs it in your browser.

SQL Buckets and a moving average (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Window functions for rates and changes

InfluxDB 3's SQL has the standard window functions: lag, lead, row_number, rank, first_value, sum() OVER, and more. Computing the change of a counter between samples is a lag away:

TEXT
SELECT time, host, bytes_sent,
       bytes_sent - lag(bytes_sent) OVER (PARTITION BY host ORDER BY time) AS delta
FROM net
WHERE time >= now() - INTERVAL '1 hour'
SQL Rate of a counter with lag (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

The first row of each host is NULL (nothing to compare with), and the reset row is NULL too, mirroring nonNegative in Flux.

Latest and extremes: selector functions

"The latest value per host" is the most common dashboard query. Portable SQL needs a subquery or ROW_NUMBER. InfluxDB 3 offers selector functions that do it in one aggregate:

TEXT
SELECT host,
       selector_last(usage_user, time)['value'] AS latest_usage,
       selector_last(usage_user, time)['time']  AS at_time
FROM cpu
WHERE time >= now() - INTERVAL '10 minutes'
GROUP BY host

selector_first, selector_last, selector_min and selector_max return a struct with the value and its timestamp. The ANSI-style alternative works everywhere:

SQL Latest row per host with ROW_NUMBER (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Joins and CTEs

Unlike Flux (awkward joins) and InfluxQL (none), SQL joins are natural. Combine two measurements on time and tags:

TEXT
WITH c AS (
  SELECT date_bin(INTERVAL '1 minute', time) AS t, host, avg(usage_user) AS cpu
  FROM cpu WHERE time >= now() - INTERVAL '1 hour' GROUP BY t, host
),
m AS (
  SELECT date_bin(INTERVAL '1 minute', time) AS t, host, avg(used_percent) AS mem
  FROM mem WHERE time >= now() - INTERVAL '1 hour' GROUP BY t, host
)
SELECT c.t, c.host, c.cpu, m.mem
FROM c JOIN m ON c.t = m.t AND c.host = m.host
ORDER BY c.t

Align both sides to the same bins first; joining raw points on exact timestamps rarely matches because collectors do not sample at the same instant. The portable equivalent:

SQL Join two measurements on aligned buckets (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Running SQL against InfluxDB 3

Terminal
influxdb3 query --database metrics "SELECT host, avg(usage_user) FROM cpu WHERE time >= now() - INTERVAL '1 hour' GROUP BY host"

curl --get http://localhost:8181/api/v3/query_sql \
  --header "Authorization: Bearer $INFLUXDB3_AUTH_TOKEN" \
  --data-urlencode "db=metrics" \
  --data-urlencode "q=SELECT count(*) FROM cpu WHERE time >= now() - INTERVAL '1 hour'" \
  --data-urlencode "format=json"

Formats include json, jsonl, csv, pretty and parquet. Client libraries and Grafana's FlightSQL data source use Arrow Flight, which is faster for large results.

Performance habits

  1. Always bound time on both sides when you can.
  2. Filter on tag columns (they are sorted and dictionary encoded, so pruning works well) before field conditions.
  3. Select only the columns you need; Parquet is columnar, so unread columns cost nothing.
  4. Aggregate in SQL; do not download raw points to aggregate in code.
  5. Use EXPLAIN or EXPLAIN ANALYZE to see how much data a query touches.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function groups timestamps into fixed windows in InfluxDB 3 SQL?

    date_bin(INTERVAL '5 minutes', time) rounds each timestamp to the start of its 5-minute bin.
  2. 2. What is the main reason to filter on time in every query?

    A time predicate lets the engine skip files outside the range.
  3. 3. Which capability does InfluxDB 3 SQL have that InfluxQL lacks?

    InfluxQL cannot join measurements; SQL can.
  4. 4. Why align both sides to the same bins before joining two measurements?

    Binning to a common interval makes timestamps comparable.

Summary

  • In InfluxDB 3 every measurement is a SQL table with a time column; bound it in every query.
  • date_bin creates windows, date_bin_gapfill with locf or interpolate fills empty ones.
  • Window functions give rates and moving averages; selector functions return the latest or extreme row.
  • SQL adds joins, CTEs and subqueries; run it from the CLI, HTTP, Flight clients or Grafana.

Next: Windows, Gaps and Pivot