Downsampling Query

A downsampling query reduces the density of time-series data by dividing a time range into equal-sized buckets and applying an aggregation function to each bucket. This is essential for visualizing large datasets on dashboards and charts — instead of transferring thousands of raw data points, you get a manageable number of summarized values.

How Downsampling Works

Downsampling uses CrateDB's DATE_BIN function to divide the time range into equally spaced intervals. For each interval, the specified aggregation function (AVG, MIN, or MAX) is applied to all data points within that bucket.

Bucket size calculation:

bucket_interval = (to - from) / limit

For example, querying a 24-hour range with limit: 24 produces 24 buckets of 1 hour each. Querying 7 days with limit: 168 produces 168 buckets of 1 hour each.

Time Range	Limit	Bucket Size
1 hour	60	1 minute
24 hours	24	1 hour
24 hours	288	5 minutes
7 days	168	1 hour
30 days	720	1 hour
30 days	30	1 day

---

Transient Downsampling Query

Use the transientStreamDataDownsamplingQuery field. Unlike other stream data queries, the from, to, and limit parameters are required — they define the time range and the number of buckets to produce.

Basic Usage

query {
  streamData {
    transientStreamDataDownsamplingQuery(
      ckId: "Industry.Energy/EnergyMeter"
      columnPaths: [
        { attributePath: "voltage", aggregationType: AVG }
      ]
      from: "2024-03-21T00:00:00Z"
      to: "2024-03-22T00:00:00Z"
      limit: 24
    ) {
      totalCount
      items {
        timestamp
        cells {
          items {
            attributePath
            value
          }
        }
      }
    }
  }
}

Response:

{
  "data": {
    "streamData": {
      "transientStreamDataDownsamplingQuery": {
        "totalCount": 24,
        "items": [
          {
            "timestamp": "2024-03-21T00:00:00.000Z",
            "cells": {
              "items": [
                { "attributePath": "Avg_voltage", "value": 229.8 }
              ]
            }
          },
          {
            "timestamp": "2024-03-21T01:00:00.000Z",
            "cells": {
              "items": [
                { "attributePath": "Avg_voltage", "value": 230.1 }
              ]
            }
          },
          {
            "timestamp": "2024-03-21T02:00:00.000Z",
            "cells": {
              "items": [
                { "attributePath": "Avg_voltage", "value": 228.5 }
              ]
            }
          }
        ]
      }
    }
  }
}

Each result row represents one time bucket. The timestamp is the start of the bucket interval, and the cell values are the aggregated values for that bucket.

Parameters

Parameter	Type	Required	Description
ckId	String!	Yes	The CK type to query
columnPaths	[StreamDataQueryColumnInput!]!	Yes	Columns with aggregation type (AVG, MIN, MAX)
from	DateTime!	Yes	Start of the time range
to	DateTime!	Yes	End of the time range
limit	Int!	Yes	Number of time buckets to produce
fieldFilter	[FieldFilter]	No	Field-level comparison filters
rtIds	[OctoObjectId]	No	Scope to specific runtime entity IDs
first	Int	No	Maximum number of items to return (pagination)
after	String	No	Pagination cursor

Note that from, to, and limit are passed as direct parameters on the downsampling query — not inside StreamDataArguments. This differs from other stream data query types.

---

Multiple Columns

You can downsample multiple attributes in a single query, each with its own aggregation function:

query {
  streamData {
    transientStreamDataDownsamplingQuery(
      ckId: "Industry.Energy/EnergyMeter"
      columnPaths: [
        { attributePath: "voltage", aggregationType: AVG },
        { attributePath: "voltage", aggregationType: MIN },
        { attributePath: "voltage", aggregationType: MAX },
        { attributePath: "power", aggregationType: AVG }
      ]
      from: "2024-03-21T00:00:00Z"
      to: "2024-03-22T00:00:00Z"
      limit: 48
    ) {
      items {
        timestamp
        cells {
          items {
            attributePath
            value
          }
        }
      }
    }
  }
}

Response (one bucket):

{
  "timestamp": "2024-03-21T00:00:00.000Z",
  "cells": {
    "items": [
      { "attributePath": "Avg_voltage", "value": 229.8 },
      { "attributePath": "Min_voltage", "value": 225.3 },
      { "attributePath": "Max_voltage", "value": 234.1 },
      { "attributePath": "Avg_power", "value": 1420.5 }
    ]
  }
}

This is useful for chart visualizations that show a band (min/max range) with an average line.

---

Scoping to Specific Entities

Use rtIds to downsample data for specific runtime entities only:

query {
  streamData {
    transientStreamDataDownsamplingQuery(
      ckId: "Industry.Energy/EnergyMeter"
      columnPaths: [
        { attributePath: "voltage", aggregationType: AVG }
      ]
      from: "2024-03-14T00:00:00Z"
      to: "2024-03-21T00:00:00Z"
      limit: 168
      rtIds: ["65dc6d24cc529cdc46c84fcc"]
    ) {
      totalCount
      items {
        timestamp
        cells {
          items {
            attributePath
            value
          }
        }
      }
    }
  }
}

---

Use Cases

Scenario	from/to	limit	Bucket Size	Purpose
Real-time dashboard (last hour)	1 hour window	60	1 minute	Live monitoring with 1-minute resolution
Daily overview chart	24 hours	288	5 minutes	Detailed daily trend analysis
Weekly trend report	7 days	168	1 hour	Hourly trends over a week
Monthly summary	30 days	30	1 day	Daily aggregates for monthly view
Yearly overview	365 days	365	1 day	Long-term trend visualization

Choosing the Right Limit

• Dashboard charts: Match the limit to the pixel width of your chart area. A chart that is 600 pixels wide needs at most 600 data points.
• Reports: Choose a limit that gives meaningful intervals for the time range (e.g., hourly for daily reports, daily for monthly reports).
• Performance: Downsampling reduces data transfer significantly. A sensor recording every second generates 86,400 points per day — downsampling to 288 points (5-minute intervals) reduces the payload by 99.7%.