About Timescale hyperfunctions

Hyperfunctions allow you to perform critical time-series queries quickly, analyze time-series data, and extract meaningful information. They aim to identify, build, and combine all of the functionality SQL needs to perform time-series analysis into a single extension.

Some hyperfunctions are included in the default TimescaleDB product. For additional hyperfunctions, you need to install the PostgreSQL extension.

Here is a list of all the hyperfunctions provided by Timescale. Hyperfunctions marked ‘Toolkit’ require an installation of Timescale Toolkit. Hyperfunctions marked ‘experimental’ are still under development.

warning

Downsample functions

Hyperfunction type	Hyperfunction name	Toolkit	Experimental
One step aggregate	asap_smooth()	✅
		✅	✅
	lttb()	✅

Financial analysis functions

Hyperfunction type	Hyperfunction name	Toolkit	Experimental
candlestick_agg() functions
Aggregate	candlestick_agg()	✅	✅
Pseudo aggregate		✅	✅
Accessor	close()	✅	✅
		✅	✅
	high()	✅	✅
		✅	✅
	low()	✅	✅
		✅	✅
	open()	✅	✅
		✅	✅
	volume()	✅	✅
		✅	✅
Rollup	rollup()	✅	✅
ohlc() functions
Aggregate		✅	✅
Accessor	close()	✅	✅
		✅	✅
	high()	✅	✅
		✅	✅
	low()	✅	✅
		✅	✅
	open()	✅	✅
		✅	✅
Rollup	rollup()	✅	✅

Frequency analysis functions

Hyperfunction type	Hyperfunction name	Toolkit	Experimental
count_min_sketch() functions
Aggregate	count_min_sketch()	✅	✅
Accessor		✅	✅
freq_agg() functions
Aggregate	freq_agg()	✅	✅
Accessor		✅	✅
Accessor	min_frequency(),	✅	✅
topn_agg() functions
Aggregate	topn_agg()	✅	✅
Accessor		✅	✅
	min_frequency(),	✅	✅
	topn()	✅	✅

Metric aggregation functions

Hyperfunction type	Hyperfunction name	Toolkit	Experimental
counter_agg() functions
Aggregate		✅
Accessor	corr()	✅
		✅
	delta()	✅
		✅
	extrapolated_rate()	✅
	, last_time()	✅
	, last_val()	✅
	, idelta_right()	✅
		✅
	interpolated_delta()	✅	✅
		✅	✅
	irate_left(),	✅
	num_changes()	✅
		✅
	num_resets()	✅
		✅
	slope()	✅
		✅
Rollup	rollup()	✅
Mutator		✅
gauge_agg() functions
Aggregate	gauge_agg()	✅	✅
Accessor		✅
	delta()	✅
		✅
	extrapolated_rate()	✅
	, idelta_right()	✅
		✅
	interpolated_delta()	✅	✅
		✅	✅
	irate_left(),	✅
	num_changes()	✅
		✅
	rate()	✅
		✅
	time_delta()	✅
Rollup		✅
Mutator	with_bounds()	✅

Minimum and maximum functions

Hyperfunction type	Hyperfunction name	Toolkit	Experimental
max_n() functions
Aggregate	max_n()	✅	✅
Accessor		✅	✅
Accessor	into_values()	✅	✅
Rollup		✅	✅
max_n_by() functions
Aggregate	max_n_by()	✅	✅
Accessor		✅	✅
Rollup	rollup()	✅	✅
min_n() functions
Aggregate		✅	✅
Accessor	into_array()	✅	✅
Accessor		✅	✅
Rollup	rollup()	✅	✅
min_n_by() functions
Aggregate		✅	✅
Accessor	into_values()	✅	✅
Rollup		✅	✅

Percentile approximation functions

Hyperfunction type	Hyperfunction name	Toolkit	Experimental
tdigest() functions
Aggregate		✅
Accessor	approx_percentile()	✅
		✅
	mean()	✅
		✅
Rollup	rollup()	✅
uddsketch() functions
Aggregate		✅
Accessor	approx_percentile()	✅
		✅	✅
	approx_percentile_rank()	✅
		✅
	mean()	✅
		✅
Alternate aggregate	percentile_agg()	✅
Rollup		✅

State aggregates functions

Hyperfunction type	Hyperfunction name	Toolkit	Experimental
heartbeat_agg() functions
Aggregate	heartbeat_agg()	✅	✅
Accessor		✅	✅
	downtime()	✅	✅
		✅	✅
	interpolated_downtime()	✅	✅
		✅	✅
	live_at()	✅	✅
		✅	✅
	uptime()	✅	✅
Rollup		✅	✅
state_agg() \| timeline_agg() functions
Aggregate	state_agg(),	✅	✅
Accessor	duration_in()	✅	✅
		✅	✅
	interpolated_state_periods()	✅	✅
		✅	✅
	into_values()	✅	✅
		✅	✅
	state_periods()	✅	✅
		✅	✅

Statistical and regression analysis functions

Hyperfunction type	Hyperfunction name	Toolkit
stats_agg() (one variable) functions
Aggregate		✅
Accessor	kurtosis()	✅
		✅
	skewness()	✅
		✅
	sum()	✅
		✅
	average()	✅
Rollup		✅
Rollup	rollup()	✅
stats_agg() (two variables) functions
Aggregate		✅
Accessor	average_y(),	✅
	corr()	✅
		✅
	determination_coeff()	✅
		✅
	kurtosis_y(),	✅
	num_vals()	✅
	, skewness_x()	✅
		✅
	stddev_y(),	✅
	sum_y(),	✅
	variance_y(),	✅
	x_intercept()	✅
Rollup		✅
Rollup	rollup()	✅

Time weighted calculations functions

Hyperfunction type	Hyperfunction name	Toolkit	Experimental
time_weight() functions
Aggregate	time_weight()	✅
Accessor		✅
	first_time()	✅
		✅
	integral()	✅	✅
		✅	✅
	interpolated_integral()	✅	✅
		✅
	last_val()	✅
Rollup		✅

For more information about each of the API calls listed in this table, see our .

Function pipelines are an experimental feature, designed to radically improve the developer ergonomics of analyzing data in PostgreSQL and SQL, by applying principles from functional programming and popular tools like Python’s Pandas, and PromQL.

SQL is the best language for data analysis, but it is not perfect, and at times can get quite unwieldy. For example, this query gets data from the last day from the measurements table, sorts the data by the time column, calculates the delta between the values, takes the absolute value of the delta, and then takes the sum of the result of the previous steps:


SELECT device_id,
FROM measurements
WHERE ts >= now()-'1 day'::interval

Function pipelines are completely SQL compliant, meaning that any tool that speaks SQL is able to support data analysis using function pipelines.

For more information about how function pipelines work, read our .

Timescale Toolkit features are developed in the open. As features are developed they are categorized as experimental, beta, stable, or deprecated. This documentation covers the stable features, but more information on our experimental features in development can be found in the .

Join the .
Check out the proposed features.
Explore the current .
Add your own feature request.

About hyperfunctions