Query data with SQL

Explore your schema with SQL

When working with InfluxDB’s implementation of SQL, a bucket is equivalent to a database, 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

Read more

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'

Read more

Aggregate or apply selector functions to data

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') AS time,
  mean(field1),
  sum(field2),
  tag1
FROM home
GROUP BY 1, tag1

Read more

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

Read more

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

Read more

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) as _time,
  room,
  interpolate(avg(temp))
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY _time, room

Read more

Use parameterized queries with SQL

Use parameterized queries to prevent injection attacks and make queries more reusable.

SELECT * FROM home
WHERE time >= $min_time
  AND temp >= $min_temp
  AND room = $room

Read more