Query examples

Example data

The data we use in the following examples are listed here with their type, so this can be easily converted to your own context.

Example name	Type
DocsDemo_Influx	Database
docsdemo_timescale_wide	Database
docsdemo_timescale_narrow	Database
oneWeek	Retention
Line1	Measurement
InputBuffer1_PowerKw	Field
InputBuffer2_PowerKw	Field
OutputBuffer1_PowerKw	Field
Temperature_Celsius	Field
Pressure_Bar	Field

To find the Database, Retention and Measurement you need, follow these instructions.

To know your database fields and tags, follow these instructions.

Example: Single field query

SELECT "InputBuffer1_PowerKw"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()

SELECT "inputbuffer1_powerkw"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

SELECT value
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw' AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    GROUP BY time, tags
)

SELECT "inputbuffer1_powerkw"
FROM wide_data
ORDER BY time

Example: Single field query vs single value query

Get the average power consumption over the last 15 minutes as a single value. This uses an aggregation function (AVG/MEAN) to ensure only one value is returned instead of multiple data points.

-- Select range of values
SELECT "InputBuffer1_PowerKw" AS "avg_power"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()

-- Select single value using average
SELECT MEAN("InputBuffer1_PowerKw") AS "avg_power"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()

-- Select range of values
SELECT "inputbuffer1_powerkw" AS "avg_power"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

-- Select single value using average
SELECT AVG("inputbuffer1_powerkw") AS "avg_power"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

-- Select range of values
SELECT value AS "avg_power"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw' AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

-- Select single value using average
SELECT AVG(value) AS "avg_power"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw' AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

-- CTE query stays the same
WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)

-- Select range of values
SELECT "inputbuffer1_powerkw" AS "avg_power"
FROM wide_data

-- Select single value using average
SELECT AVG("inputbuffer1_powerkw") AS "avg_power"
FROM wide_data

Example: Convert units (kW to MW)

Transform power values from kilowatts to megawatts by dividing by 1000.

SELECT "InputBuffer1_PowerKw" / 1000 AS "Power_MW"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()

SELECT "inputbuffer1_powerkw" / 1000 AS "power_mw"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

SELECT value / 1000 AS "power_mw"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw' AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT "inputbuffer1_powerkw" / 1000 AS "power_mw"
FROM wide_data

Example: Sum multiple fields

Calculate total power consumption by adding input buffer values.

SELECT ("InputBuffer1_PowerKw" + "InputBuffer2_PowerKw") AS "Total_Input_Power"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()

SELECT ("inputbuffer1_powerkw" + "inputbuffer2_powerkw") AS "total_input_power"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

SELECT
    time,
    SUM(value) AS "total_input_power"
FROM "line1"
WHERE name IN ('InputBuffer1_PowerKw', 'InputBuffer2_PowerKw')
    AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
GROUP BY time
ORDER BY time

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw",
        MAX(CASE WHEN name = 'InputBuffer2_PowerKw' THEN value END) AS "inputbuffer2_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT ("inputbuffer1_powerkw" + "inputbuffer2_powerkw") AS "total_input_power"
FROM wide_data

Example: Filter by value range

Retrieve only values within a specific range (e.g., power between 50 and 150 kW).

SELECT "InputBuffer1_PowerKw"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()
    AND "InputBuffer1_PowerKw" >= 50 AND "InputBuffer1_PowerKw" <= 150

SELECT "inputbuffer1_powerkw"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    AND "inputbuffer1_powerkw" >= 50 AND "inputbuffer1_powerkw" <= 150

SELECT value
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw'
    AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    AND value >= 50 AND value <= 150

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT "inputbuffer1_powerkw"
FROM wide_data
WHERE "inputbuffer1_powerkw" >= 50 AND "inputbuffer1_powerkw" <= 150

Example: Round values to precision

Round temperature values to 1 decimal place for cleaner display.

SELECT ROUND("Temperature_Celsius", 1) AS "Temperature_Rounded"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()

SELECT ROUND("temperature_celsius"::numeric, 1) AS "temperature_rounded"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

SELECT ROUND(value::numeric, 1) AS "temperature_rounded"
FROM "line1"
WHERE name = 'Temperature_Celsius'
    AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'Temperature_Celsius' THEN value END) AS "temperature_celsius"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT ROUND("temperature_celsius"::numeric, 1) AS "temperature_rounded"
FROM wide_data

Example: Convert Celsius to Fahrenheit

Transform temperature readings from Celsius to Fahrenheit using the formula: (C × 9/5) + 32.

SELECT ("Temperature_Celsius" * 9 / 5) + 32 AS "Temperature_Fahrenheit"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()

SELECT ("temperature_celsius" * 9.0 / 5.0) + 32 AS "temperature_fahrenheit"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

SELECT (value * 9.0 / 5.0) + 32 AS "temperature_fahrenheit"
FROM "line1"
WHERE name = 'Temperature_Celsius'
    AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'Temperature_Celsius' THEN value END) AS "temperature_celsius"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT ("temperature_celsius" * 9.0 / 5.0) + 32 AS "temperature_fahrenheit"
FROM wide_data

Example: Calculate efficiency percentage

Calculate efficiency as the ratio of output to input power, expressed as a percentage.

SELECT ("OutputBuffer1_PowerKw" / "InputBuffer1_PowerKw") * 100 AS "Efficiency_Percent"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()
    AND "InputBuffer1_PowerKw" > 0

SELECT ("outputbuffer1_powerkw" / "inputbuffer1_powerkw") * 100 AS "efficiency_percent"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    AND "inputbuffer1_powerkw" > 0

SELECT
    time,
    (MAX(CASE WHEN name = 'OutputBuffer1_PowerKw' THEN value END) /
     MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END)) * 100 AS "efficiency_percent"
FROM "line1"
WHERE name IN ('OutputBuffer1_PowerKw', 'InputBuffer1_PowerKw')
    AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
GROUP BY time
HAVING MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) > 0
ORDER BY time

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw",
        MAX(CASE WHEN name = 'OutputBuffer1_PowerKw' THEN value END) AS "outputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT ("outputbuffer1_powerkw" / "inputbuffer1_powerkw") * 100 AS "efficiency_percent"
FROM wide_data
WHERE "inputbuffer1_powerkw" > 0

Example: Categorize values by threshold

Categorize power consumption into severity levels: Low (<20 kW), Medium (20-50 kW), High (50-80 kW), Critical (80+ kW).

SELECT
    "InputBuffer1_PowerKw",
    CASE
        WHEN "InputBuffer1_PowerKw" < 20 THEN 'Low'
        WHEN "InputBuffer1_PowerKw" >= 20 AND "InputBuffer1_PowerKw" < 50 THEN 'Medium'
        WHEN "InputBuffer1_PowerKw" >= 50 AND "InputBuffer1_PowerKw" < 80 THEN 'High'
        ELSE 'Critical'
    END AS "Power_Level"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 15m AND time <= NOW()

SELECT
    "inputbuffer1_powerkw",
    CASE
        WHEN "inputbuffer1_powerkw" < 20 THEN 'Low'
        WHEN "inputbuffer1_powerkw" >= 20 AND "inputbuffer1_powerkw" < 50 THEN 'Medium'
        WHEN "inputbuffer1_powerkw" >= 50 AND "inputbuffer1_powerkw" < 80 THEN 'High'
        ELSE 'Critical'
    END AS "power_level"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

SELECT
    value,
    CASE
        WHEN value < 20 THEN 'Low'
        WHEN value >= 20 AND value < 50 THEN 'Medium'
        WHEN value >= 50 AND value < 80 THEN 'High'
        ELSE 'Critical'
    END AS "power_level"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw'
    AND time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT
    "inputbuffer1_powerkw",
    CASE
        WHEN "inputbuffer1_powerkw" < 20 THEN 'Low'
        WHEN "inputbuffer1_powerkw" >= 20 AND "inputbuffer1_powerkw" < 50 THEN 'Medium'
        WHEN "inputbuffer1_powerkw" >= 50 AND "inputbuffer1_powerkw" < 80 THEN 'High'
        ELSE 'Critical'
    END AS "power_level"
FROM wide_data

Example: Group by 5 minutes (average values)

Calculate the average power consumption for every 5-minute interval. This is useful for reducing data density and smoothing out fluctuations.

SELECT MEAN("InputBuffer1_PowerKw") AS "avg_power"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 1h AND time <= NOW()
GROUP BY TIME(5m)

SELECT
    time_bucket('5 minutes', time) AS bucket,
    AVG("inputbuffer1_powerkw") AS "avg_power"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
GROUP BY bucket
ORDER BY bucket

SELECT
    time_bucket('5 minutes', time) AS bucket,
    AVG(value) AS "avg_power"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw'
    AND time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
GROUP BY bucket
ORDER BY bucket

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT
    time_bucket('5 minutes', time) AS bucket,
    AVG("inputbuffer1_powerkw") AS "avg_power"
FROM wide_data
GROUP BY bucket
ORDER BY bucket

Example: Maximum value in time period

Find the peak power consumption over the last hour.

SELECT MAX("InputBuffer1_PowerKw") AS "peak_power"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 1h AND time <= NOW()

SELECT MAX("inputbuffer1_powerkw") AS "peak_power"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

SELECT MAX(value) AS "peak_power"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw'
    AND time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT MAX("inputbuffer1_powerkw") AS "peak_power"
FROM wide_data

Example: Minimum value in time period

Find the lowest temperature recorded in the last 24 hours.

SELECT MIN("Temperature_Celsius") AS "min_temperature"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 24h AND time <= NOW()

SELECT MIN("temperature_celsius") AS "min_temperature"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '24 hours' AND time <= NOW()

SELECT MIN(value) AS "min_temperature"
FROM "line1"
WHERE name = 'Temperature_Celsius'
    AND time >= NOW() - INTERVAL '24 hours' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'Temperature_Celsius' THEN value END) AS "temperature_celsius"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '24 hours' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT MIN("temperature_celsius") AS "min_temperature"
FROM wide_data

Example: Total sum over time period

Calculate total energy consumption (sum of all power readings) over the last hour.

SELECT SUM("InputBuffer1_PowerKw") AS "total_energy"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 1h AND time <= NOW()

SELECT SUM("inputbuffer1_powerkw") AS "total_energy"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

SELECT SUM(value) AS "total_energy"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw'
    AND time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT SUM("inputbuffer1_powerkw") AS "total_energy"
FROM wide_data

Example: Sum of average values per minute

Calculate energy consumption by first averaging values per minute (to normalize irregular timestamps), then summing those averages over the hour. This is more accurate than a direct sum when data points are inconsistently spaced.

SELECT SUM("avg_power") AS "total_normalized_energy"
FROM (
    SELECT MEAN("InputBuffer1_PowerKw") AS "avg_power"
    FROM "oneWeek"."Line1"
    WHERE time >= NOW() - 1h AND time <= NOW()
    GROUP BY TIME(1m)
)

SELECT SUM("avg_power") AS "total_normalized_energy"
FROM (
    SELECT
        time_bucket('1 minute', time) AS bucket,
        AVG("inputbuffer1_powerkw") AS "avg_power"
    FROM "docsdemo_timescale_wide"."line1"
    WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY bucket
) AS minute_averages

SELECT SUM("avg_power") AS "total_normalized_energy"
FROM (
    SELECT
        time_bucket('1 minute', time) AS bucket,
        AVG(value) AS "avg_power"
    FROM "line1"
    WHERE name = 'InputBuffer1_PowerKw'
        AND time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY bucket
) AS minute_averages

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
),
minute_averages AS (
    SELECT
        time_bucket('1 minute', time) AS bucket,
        AVG("inputbuffer1_powerkw") AS "avg_power"
    FROM wide_data
    GROUP BY bucket
)
SELECT SUM("avg_power") AS "total_normalized_energy"
FROM minute_averages

Example: Count data points

Count how many data points were recorded in the last hour.

SELECT COUNT("InputBuffer1_PowerKw") AS "data_point_count"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 1h AND time <= NOW()

SELECT COUNT("inputbuffer1_powerkw") AS "data_point_count"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

SELECT COUNT(value) AS "data_point_count"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw'
    AND time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT COUNT("inputbuffer1_powerkw") AS "data_point_count"
FROM wide_data

Example: Standard deviation (volatility)

Calculate how volatile the power consumption has been over the last hour. Lower values indicate stable consumption, higher values indicate fluctuating consumption.

SELECT STDDEV("InputBuffer1_PowerKw") AS "power_volatility"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 1h AND time <= NOW()

SELECT STDDEV("inputbuffer1_powerkw") AS "power_volatility"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

SELECT STDDEV(value) AS "power_volatility"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw'
    AND time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT STDDEV("inputbuffer1_powerkw") AS "power_volatility"
FROM wide_data

Example: First and last values

Get the first and last recorded values in the time period to see the change over time.

SELECT
    FIRST("InputBuffer1_PowerKw") AS "initial_power",
    LAST("InputBuffer1_PowerKw") AS "final_power",
    LAST("InputBuffer1_PowerKw") - FIRST("InputBuffer1_PowerKw") AS "power_change"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 1h AND time <= NOW()

SELECT
    FIRST("inputbuffer1_powerkw", time) AS "initial_power",
    LAST("inputbuffer1_powerkw", time) AS "final_power",
    LAST("inputbuffer1_powerkw", time) - FIRST("inputbuffer1_powerkw", time) AS "power_change"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

SELECT
    FIRST(value, time) AS "initial_power",
    LAST(value, time) AS "final_power",
    LAST(value, time) - FIRST(value, time) AS "power_change"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw'
    AND time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT
    FIRST("inputbuffer1_powerkw", time) AS "initial_power",
    LAST("inputbuffer1_powerkw", time) AS "final_power",
    LAST("inputbuffer1_powerkw", time) - FIRST("inputbuffer1_powerkw", time) AS "power_change"
FROM wide_data

Example: Multiple aggregations in one query

Get comprehensive statistics (average, min, max, count) in a single query.

SELECT
    MEAN("InputBuffer1_PowerKw") AS "avg_power",
    MIN("InputBuffer1_PowerKw") AS "min_power",
    MAX("InputBuffer1_PowerKw") AS "max_power",
    COUNT("InputBuffer1_PowerKw") AS "data_points"
FROM "oneWeek"."Line1"
WHERE time >= NOW() - 1h AND time <= NOW()

SELECT
    AVG("inputbuffer1_powerkw") AS "avg_power",
    MIN("inputbuffer1_powerkw") AS "min_power",
    MAX("inputbuffer1_powerkw") AS "max_power",
    COUNT("inputbuffer1_powerkw") AS "data_points"
FROM "docsdemo_timescale_wide"."line1"
WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

SELECT
    AVG(value) AS "avg_power",
    MIN(value) AS "min_power",
    MAX(value) AS "max_power",
    COUNT(value) AS "data_points"
FROM "line1"
WHERE name = 'InputBuffer1_PowerKw'
    AND time >= NOW() - INTERVAL '1 hour' AND time <= NOW()

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = 'InputBuffer1_PowerKw' THEN value END) AS "inputbuffer1_powerkw"
    FROM "line1"
    WHERE time >= NOW() - INTERVAL '1 hour' AND time <= NOW()
    GROUP BY time, tags
    ORDER BY time
)
SELECT
    AVG("inputbuffer1_powerkw") AS "avg_power",
    MIN("inputbuffer1_powerkw") AS "min_power",
    MAX("inputbuffer1_powerkw") AS "max_power",
    COUNT("inputbuffer1_powerkw") AS "data_points"
FROM wide_data

Example data​

Example: Single field query​

Example: Single field query vs single value query​

Example: Convert units (kW to MW)​

Example: Sum multiple fields​

Example: Filter by value range​

Example: Round values to precision​

Example: Convert Celsius to Fahrenheit​

Example: Calculate efficiency percentage​

Example: Categorize values by threshold​

Example: Group by 5 minutes (average values)​

Example: Maximum value in time period​

Example: Minimum value in time period​

Example: Total sum over time period​

Example: Sum of average values per minute​

Example: Count data points​

Example: Standard deviation (volatility)​

Example: First and last values​

Example: Multiple aggregations in one query​