Query data with SQL

Learn to query data stored in InfluxDB using SQL.

Explore your schema with SQL
Perform a basic SQL query
Aggregate data with SQL
Cast values to different types
Compare values in SQL queries
Fill gaps in data
Use parameterized queries with SQL

Explore your schema with SQL

When working with InfluxDB’s implementation of SQL, a measurement is structured as a table, and time, fields, and tags are structured as columns.

List measurements

SHOW TABLES

List columns in a measurement

SHOW COLUMNS IN measurement

Perform a basic SQL query

A basic SQL query that queries data from InfluxDB most commonly includes SELECT, FROM, and WHERE clauses.

SELECT temp, room FROM home WHERE time >= now() - INTERVAL '1 day'

Aggregate data with SQL

Use aggregate and selector functions to perform aggregate operations on your time series data.

Aggregate fields by groups

SELECT
  mean(field1) AS mean,
  selector_first(field2)['value'] as first,
  tag1
FROM home
GROUP BY tag

Aggregate by time-based intervals

SELECT
  DATE_BIN(INTERVAL '1 hour', time, '2022-01-01T00:00:00Z'::TIMESTAMP) AS time,
  mean(field1),
  sum(field2),
  tag1
FROM home
GROUP BY 1, tag1

Cast values to different types

Use the CAST function or double-colon :: casting shorthand syntax to cast a value to a specific type.

-- CAST clause
SELECT CAST(1234.5 AS BIGINT)

-- Double-colon casting shorthand
SELECT 1234.5::BIGINT

Compare values in SQL queries

Use SQL window functions to compare values across different rows in your time series data. Learn how to calculate differences, percentage changes, and compare values at specific time intervals.

Calculate difference from previous value

SELECT
  time,
  room,
  temp,
  temp - LAG(temp) OVER (
    PARTITION BY room
    ORDER BY time
  ) AS temp_change
FROM home
ORDER BY room, time

Fill gaps in data

Use date_bin_gapfill with interpolate or locf to fill gaps of time where no data is returned.

SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time