timescaledb-hyperfunctions

Featured

Use this skill when writing analytical SQL over time-series data with the TimescaleDB Toolkit (timescaledb_toolkit extension) hyperfunctions: approximate percentiles, statistical summaries, time-weighted averages, counter/gauge rates, uptime/heartbeat tracking, state durations, OHLC candlesticks, approximate distinct counts, top-N, and downsampling. **Trigger when user asks to:** - Compute percentiles/medians/p95/p99 over large or rolled-up time-series data - Compute rates or deltas from monotonic counters (Prometheus-style) or gauges - Compute time-weighted averages or integrals over irregularly sampled data - Track uptime/downtime from heartbeats, or time spent in each state - Build OHLC/candlestick or VWAP data for financial ticks - Store re-aggregatable summaries in continuous aggregates (two-step aggregation, rollup) - Approximate COUNT DISTINCT, find top-N / most frequent values, or downsample for charts **Keywords:** timescaledb_toolkit, hyperfunctions, percentile_agg, uddsketch, tdigest, approx_percen

AI & Automation 1,886 stars 120 forks Updated today Apache-2.0

Install

View on GitHub

Quality Score: 99/100

Stars 20%
100
Recency 20%
100
Frontmatter 20%
70
Documentation 15%
100
Issue Health 10%
50
License 10%
100
Description 5%
100

Skill Content

# TimescaleDB Toolkit Hyperfunctions Hyperfunctions are SQL aggregates and accessors from the `timescaledb_toolkit` extension for analysis that plain PostgreSQL aggregates handle badly: percentiles that can be re-aggregated, rates over resetting counters, averages over irregular samples, uptime, state durations, and more. ## Setup ```sql CREATE EXTENSION IF NOT EXISTS timescaledb_toolkit; -- preinstalled on Tiger Cloud SELECT extversion FROM pg_extension WHERE extname = 'timescaledb_toolkit'; ALTER EXTENSION timescaledb_toolkit UPDATE; -- after upgrading the package ``` **Only use functions from the default schema in persistent objects.** Anything in the `toolkit_experimental` schema can change between releases, and `ALTER EXTENSION ... UPDATE` **drops** views, continuous aggregates, and functions that depend on it. All functions in this skill are stable. ## The Two-Step Pattern (read this first) Nearly every hyperfunction works in two steps: 1. An **aggregate** builds a compact summary (`percentile_agg(val)` → `uddsketch`, `time_weight(...)` → `timeweightsummary`, ...). 2. An **accessor** extracts a result from the summary (`approx_percentile(0.95, summary)`, `average(summary)`, ...). ```sql -- Function-call style SELECT approx_percentile(0.95, percentile_agg(latency_ms)) FROM requests; -- Arrow style (same result, reads left to right, chains multiple accessors) SELECT percentile_agg(latency_ms) -> approx_percentile(0.95) FROM requests; ``` The pattern ...

Details

Author
timescale
Repository
timescale/pg-aiguide
Created
1 years ago
Last Updated
today
Language
TypeScript
License
Apache-2.0

Integrates with

Bundled in these plugins

Similar Skills

Semantically similar based on skill content — not just same category