Query differences

Key differences between timescale and influx queries

Understanding the syntax differences between Influx and Timescale is essential for writing correct queries. Here are the main differences:

Quoting Rules

Influx:

• Use double quotes for field names and measurements: "InputBuffer1_PowerKw"
• Field names are case-sensitive as originally defined

Timescale (Wide Table):

• Use double quotes for field names: "inputbuffer1_powerkw"
• Field names are typically lowercase (converted during storage)

Timescale (Narrow Table):

• Use single quotes in the WHERE clause when filtering by field name: WHERE name = 'InputBuffer1_PowerKw'
• Field names in the name column maintain their original casing
• Use double quotes for column aliases in SELECT

Table Structure

Wide Format (Influx & Timescale-Wide):

• Each field is a separate column
• Query fields directly by column name
• Best for queries involving specific known fields

Narrow Format (Timescale-Narrow):

• All fields stored in a single table with name and value columns
• Query by filtering on name column
• More flexible but requires additional WHERE conditions
• Can be converted to wide format using CTEs

There is a more detailed explanation for the table structure here

Database Path Format

FROM "Retention"."Measurement"

• Requires a retention policy (e.g., "oneWeek", "autogen")
• Format: "RetentionPolicy"."Measurement"

FROM "Database"."Measurement"
-- OR
FROM "Measurement"

• Uses database name instead of retention policy
• Format: "Database"."Measurement" or just "Measurement"
• Database name is optional when the database connection is already selected
• Include database name when:
  • Querying across multiple databases in a single query
  • Connecting to a database server without selecting a specific database first
• Most common case: use just FROM "Measurement" when connected to your database

Time Filtering

WHERE time >= NOW() - 15m AND time <= NOW()

• Uses shorthand: 15m, 1h, 7d, 1w
• Simple subtraction syntax

WHERE time >= NOW() - INTERVAL '15 minutes' AND time <= NOW()

• Uses INTERVAL keyword
• Requires quotes around the interval
• Full words: '15 minutes', '1 hour', '7 days'

Fixed time filtering

-- Specific date and time (UTC)
WHERE time >= '2024-01-15T10:00:00Z' AND time < '2024-01-15T11:00:00Z'

-- With timezone offset (+2 hours UTC)
WHERE time >= '2024-01-15T10:00:00+02:00' AND time < '2024-01-15T11:00:00+02:00'

-- Date range
WHERE time >= '2024-01-01T00:00:00Z' AND time < '2024-02-01T00:00:00Z'

• Use ISO 8601 format with timezone: 'YYYY-MM-DDTHH:MM:SSZ'
• Z indicates UTC timezone
• For other timezones, use offset format: +02:00 for UTC+2, -05:00 for UTC-5

-- Specific date and time
WHERE time >= '2024-01-15 10:00:00' AND time < '2024-01-15 11:00:00'

-- With timezone (UTC)
WHERE time >= '2024-01-15 10:00:00+00' AND time < '2024-01-15 11:00:00+00'

-- With timezone (+2 hours UTC)
WHERE time >= '2024-01-15 10:00:00+02' AND time < '2024-01-15 11:00:00+02'

-- Using TIMESTAMP
WHERE time >= TIMESTAMP '2024-01-15 10:00:00' AND time < TIMESTAMP '2024-01-15 11:00:00'

-- Date only (entire day)
WHERE time >= '2024-01-15' AND time < '2024-01-16'

Time Grouping

GROUP BY TIME(5m)

• Uses TIME() function
• Shorthand notation: 5m, 1h, 1d

GROUP BY time_bucket('5 minutes', time)

• Uses time_bucket() function
• Requires quotes around the interval
• Full words: '5 minutes', '1 hour', '1 day'

Aggregation Functions

Most aggregation functions are similar, but there are key differences (see Aggregations section for full details):

Function	Influx	Timescale	Notes
Average	MEAN()	AVG()	Different function name
Median	MEDIAN()	PERCENTILE_CONT(0.5)	Timescale requires percentile function
First/Last	FIRST(field)	FIRST(field, time)	Timescale requires time parameter

Common functions that are the same: SUM(), COUNT(), MIN(), MAX(), STDDEV()

Type Casting

Timescale only:

ROUND(value::numeric, 1)

• Timescale requires ::numeric casting for certain operations like ROUND()
• Influx doesn't require explicit casting

WHERE Clause

Filter data based on conditions. Both systems support multiple conditions, but with different syntax for time filtering and field checking.

WHERE time >= NOW() - 1h
  AND "InputBuffer1_PowerKw" > 50
  AND "Status" = 'active'
  AND "Description" != ''

Key points:

• Multiple conditions combined with AND / OR
• Field names in double quotes
• String values in single quotes
• Check if field is not empty: "FieldName" != ''
• Time uses shorthand notation: 1h, 15m, 7d

WHERE time >= NOW() - INTERVAL '1 hour'
  AND "inputbuffer1_powerkw" > 50
  AND "status" = 'active'
  AND "description" IS NOT NULL
  AND "description" != ''

Key points:

• Standard SQL syntax with AND / OR / NOT
• Field names in double quotes (usually lowercase)
• String values in single quotes
• Check if field is not NULL: "fieldname" IS NOT NULL
• Check if field is not empty: "fieldname" != '' or "fieldname" <> ''
• Combine both for non-empty check: "fieldname" IS NOT NULL AND "fieldname" != ''
• Time uses INTERVAL keyword
• Supports complex expressions and subqueries

Fixed date filtering:

-- Specific date and time
WHERE time >= '2024-01-15 10:00:00' AND time < '2024-01-15 11:00:00'

-- With timezone
WHERE time >= '2024-01-15 10:00:00+00' AND time < '2024-01-15 11:00:00+00'

-- Using TIMESTAMP
WHERE time >= TIMESTAMP '2024-01-15 10:00:00' AND time < TIMESTAMP '2024-01-15 11:00:00'

-- Date only (entire day)
WHERE time >= '2024-01-15' AND time < '2024-01-16'

• Use standard SQL date/time format: 'YYYY-MM-DD HH:MM:SS'
• Can include timezone offset: +00 for UTC, +01 for CET, etc.
• Use TIMESTAMP keyword for explicit timestamp casting
• Date-only format for entire day queries

WHERE name = 'InputBuffer1_PowerKw'
  AND time >= NOW() - INTERVAL '1 hour'
  AND value > 50
  AND value IS NOT NULL

Key points:

• Must filter by name = 'FieldName' to select specific fields
• Filter value column for numeric/string conditions
• Check if value is not NULL: value IS NOT NULL
• Check if value is not empty (for string fields): value != ''
• Field name in single quotes, column names in double quotes

Fixed date filtering:

WHERE name = 'InputBuffer1_PowerKw'
  AND time >= '2024-01-15 10:00:00'
  AND time < '2024-01-15 11:00:00'

• Same date/time syntax as Timescale-wide
• Can use TIMESTAMP keyword and timezone offsets

ORDER BY

warning

If your query depends on result order, add an explicit ORDER BY clause. Without the order by clause, the result order is not guaranteed, but Influx will likely be ordered by newest first, while Timescale will order by oldest first

Sort query results by one or more columns.

ORDER BY time DESC

Key points:

• Available but less commonly used (time-series data is usually chronological)
• Can order by time or field values
• Use DESC for descending (newest first), omit or use ASC for ascending

ORDER BY time DESC
-- Or order by multiple columns
ORDER BY time DESC, "inputbuffer1_powerkw" ASC

Key points:

• Standard SQL ORDER BY with ASC / DESC
• Can order by multiple columns (comma-separated)
• NULLS FIRST / NULLS LAST options available for handling NULL values
• Example: ORDER BY time DESC NULLS LAST

Summary

The main philosophy difference:

• Influx uses a more compact syntax with shorthand notation

• Timescale uses standard SQL syntax with explicit keywords and full words

• Timescale-narrow adds flexibility but requires understanding of pivoting data

When migrating queries between systems, pay special attention to:

1. Aggregation function names (MEAN vs AVG)
2. Time interval syntax (15m vs INTERVAL '15 minutes')
3. Quoting rules (SELECT "inputbuffer1_powerkw" vs WHERE name = 'InputBuffer1_PowerKw')
4. Database path format (retention vs database)

Single field queries

A single field query is a query that fetches datapoints for a single field, each with a unique timestamp. These types of queries are used for Grids and Graphs in reporter.

How to create single field queries

The approach differs depending on your database type:

Influx

Influx uses a wide table format where each field is stored as a separate column.

Query structure:

SELECT "<Field>"
FROM "<Retention>"."<Measurement>"
WHERE time >= NOW() - <duration> AND time <= NOW()

Key points:

• Use double quotes for field names
• Format: "Retention"."Measurement"
• Time duration: 15m, 1h, 7d, etc.
• Field names are case-sensitive as defined

Timescale - Wide Table

Timescale wide tables store data similarly to InfluxDB, with fields as columns. Field names are typically lowercase.

Query structure:

SELECT "<field>"
FROM "<database>"."<measurement>"
WHERE time >= NOW() - INTERVAL '<duration>'

Key points:

• Field names are usually lowercase (e.g., inputbuffer1_powerkw)
• Format: "Database"."Measurement"
• Time interval: INTERVAL '15 minutes', INTERVAL '1 hour', etc.
• No retention policy in path (unlike InfluxDB)

Timescale - Narrow Table

Narrow tables store all fields in a single table with a name column identifying the field and a value column containing the data.

Query structure:

SELECT value
FROM "<measurement>"
WHERE name = '<Field>' AND time >= NOW() - INTERVAL '<duration>'

Key points:

• Select value column instead of field name
• Filter by name = '<Field>' to specify which field
• Field names in the name filter match original casing
• More flexible but requires additional WHERE condition

Timescale - Narrow CTE (Converting to Wide Format)

When working with narrow tables, you can convert them to wide format using a CTE (Common Table Expression) for easier manipulation.

Query structure:

WITH wide_data AS (
    SELECT
        time,
        MAX(CASE WHEN name = '<Field>' THEN value END) AS "<Field_Alias>"
    FROM "<measurement>"
    WHERE time >= NOW() - INTERVAL '<duration>'
    GROUP BY time, tags
    ORDER BY time
)
SELECT "<Field_Alias>"
FROM wide_data

Key points:

• Use CASE WHEN to pivot field names into columns
• MAX() aggregation handles the grouping (only one value per field per time)
• GROUP BY time, tags to combine rows with same timestamp
• More complex but allows working with multiple fields like wide tables
• Useful when you need to perform calculations across multiple fields

Single value queries

A single value query is a query that only returns a single value. This can be the same as the Single field queries with a aggregation applied.

Aggregations

Name	Influx	Timescale	Description
Average	MEAN()	AVG()	Take the average value
Sum	SUM()	SUM()	The sum of all the values
Count	COUNT()	COUNT()	Count the number of values, ignoring their value
Minimum	MIN()	MIN()	Take the lowest value
Maximum	MAX()	MAX()	Take the highest value
First value	FIRST()	FIRST(field, time)	Take the first value
Last value	LAST()	LAST(field, time)	Take the last value
Standard Dev	STDDEV()	STDDEV()	Calculate how volatile this value is. lower number means results are close together, higher value means they are more spread out
Median	MEDIAN()	PERCENTILE_CONT(0.5)	Sort the values and takes the middle value