# Timescaledb Hyperfunctions

Use this skill when writing analytical SQL over time-series data with the TimescaleDB...

- Repository: https://github.com/timescale/pg-aiguide (1877 stars)
- Category: Data and ML
- License: Apache-2.0
- Last updated: 2026-10-28
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add timescale/pg-aiguide --skill timescaledb-hyperfunctions -g -a claude-code -y`
Codex: `npx skills add timescale/pg-aiguide --skill timescaledb-hyperfunctions -g -a codex -y`
Cursor: `npx skills add timescale/pg-aiguide --skill timescaledb-hyperfunctions -g -a cursor -y`
Gemini CLI: `npx skills add timescale/pg-aiguide --skill timescaledb-hyperfunctions -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add timescale/pg-aiguide`

## Description

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_percentile, stats_agg, time_weight, counter_agg, gauge_agg, heartbeat_agg, state_agg, candlestick_agg, hyperloglog, approx_count_distinct, min_n, max_n, mcv_agg, lttb, asap_smooth, rollup, two-step aggregation

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# 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 exists so summaries can be **stored and re-aggregated**:

The rest: https://github.com/timescale/pg-aiguide/blob/HEAD/skills/timescaledb-hyperfunctions/SKILL.md