Query data with SQL

Learn to query data in InfluxDB 3 Cloud using SQL.

Explore your schema with SQL

Use SQL to explore your data schema in your InfluxDB 3 Cloud database.

List tables
SHOW TABLES
List columns in a table
SHOW COLUMNS IN table

Read more

Perform a basic SQL query

A basic SQL query in InfluxDB 3 Cloud most commonly includes SELECT, FROM, and WHERE clauses.

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

Read more

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

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 1, 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

Was this page helpful?

Thank you for your feedback!