Timescale hyperfunctions are a specialized set of functions that allow you to analyze time-series data. You can use hyperfunctions to analyze anything you have stored as time-series data, including IoT devices, IT systems, marketing analytics, user behavior, financial metrics, and cryptocurrency.

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 Timescale Toolkit 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

Experimental features could have bugs. They might not be backwards compatible, and could be removed in future releases. Use these features at your own risk, and do not use any experimental features in production.

###### Important

When you upgrade the `timescaledb`

extension, the experimental schema is removed by default. To use experimental features after an upgrade, you need to add the experimental schema again.

### Approximate count distinct functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental | |
---|---|---|---|---|

hyperloglog() functions | ||||

Aggregate | `hyperloglog()` | ✅ | ||

Alternate aggregate | `approx_count_distinct()` | ✅ | ||

Accessor | `distinct_count()` | ✅ | ||

`stderror()` | ✅ | |||

Rollup | `rollup()` | ✅ |

### Counters and gauges functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental | |
---|---|---|---|---|

gauge_agg() functions | ||||

Aggregate | `gauge_agg()` | ✅ | ✅ | |

Accessor | `corr()` | ✅ | ✅ | |

`delta()` | ✅ | ✅ | ||

`extrapolated_delta()` | ✅ | ✅ | ||

`extrapolated_rate()` | ✅ | ✅ | ||

`gauge_zero_time()` | ✅ | ✅ | ||

`idelta_left()` | ✅ | ✅ | ||

`idelta_right()` | ✅ | ✅ | ||

`intercept()` | ✅ | ✅ | ||

`interpolated_delta()` | ✅ | ✅ | ||

`interpolated_rate()` | ✅ | ✅ | ||

`irate_left()` | ✅ | ✅ | ||

`irate_right()` | ✅ | ✅ | ||

`num_changes()` | ✅ | ✅ | ||

`num_elements()` | ✅ | ✅ | ||

`rate()` | ✅ | ✅ | ||

`slope()` | ✅ | ✅ | ||

`time_delta()` | ✅ | ✅ | ||

Rollup | `rollup()` | ✅ | ✅ | |

Mutator | `with_bounds()` | ✅ | ✅ | |

counter_agg() functions | ||||

Aggregate | `counter_agg()` | ✅ | ||

Accessor | `counter_zero_time()` | ✅ | ||

`delta()` | ✅ | |||

`extrapolated_delta()` | ✅ | |||

`extrapolated_rate()` | ✅ | |||

`first_time()` | ✅ | |||

`first_val()` | ✅ | |||

`idelta_left()` | ✅ | |||

`idelta_right()` | ✅ | |||

`intercept()` | ✅ | |||

`interpolated_delta()` | ✅ | ✅ | ||

`interpolated_rate()` | ✅ | ✅ | ||

`irate_left()` | ✅ | |||

`irate_right()` | ✅ | |||

`last_time()` | ✅ | |||

`last_val()` | ✅ | |||

`num_changes()` | ✅ | |||

`num_elements()` | ✅ | |||

`num_resets()` | ✅ | |||

`rate()` | ✅ | |||

`slope()` | ✅ | |||

`time_delta()` | ✅ | |||

`corr()` | ✅ | |||

Rollup | `rollup()` | ✅ | ||

Mutator | `with_bounds()` | ✅ |

### Downsampling functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental |
---|---|---|---|

Function | `asap_smooth()` | ✅ | |

`gp_lttb()` | ✅ | ✅ | |

`lttb()` | ✅ |

### Financial analysis functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental | |
---|---|---|---|---|

candlestick_agg() functions | ||||

Aggregate | `candlestick_agg()` | ✅ | ||

Pseudo aggregate | `candlestick()` | ✅ | ||

Accessor | `close()` | ✅ | ||

`close_time()` | ✅ | |||

`high()` | ✅ | |||

`high_time()` | ✅ | |||

`low()` | ✅ | |||

`low_time()` | ✅ | |||

`open()` | ✅ | |||

`open_time()` | ✅ | |||

`volume()` | ✅ | |||

`vwap()` | ✅ | |||

Rollup | `rollup()` | ✅ |

### Frequency analysis functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental | |
---|---|---|---|---|

count_min_sketch() functions | ||||

Aggregate | `count_min_sketch()` | ✅ | ✅ | |

Accessor | `approx_count()` | ✅ | ✅ | |

freq_agg() functions | ||||

Aggregate | `freq_agg()` | ✅ | ✅ | |

Accessor | `into_values()` | ✅ | ||

`max_frequency()` | ✅ | |||

`min_frequency()` | ✅ | |||

`topn()` | ✅ | |||

Alternate aggregate | `mcv_agg()` | ✅ | ||

Rollup | `rollup()` | ✅ |

### Gapfilling functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental |
---|---|---|---|

Bucket | `time_bucket_gapfill()` | ||

Interpolator | `interpolate()` | ||

`locf()` |

### Minimum and maximum functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental | |
---|---|---|---|---|

max_n() functions | ||||

Aggregate | `max_n()` | ✅ | ||

Accessor | `into_array()` | ✅ | ||

`into_values()` | ✅ | |||

Rollup | `rollup()` | ✅ | ||

max_n_by() functions | ||||

Aggregate | `max_n_by()` | ✅ | ||

Accessor | `into_values()` | ✅ | ||

Rollup | `rollup()` | ✅ | ||

min_n() functions | ||||

Aggregate | `min_n()` | ✅ | ||

Accessor | `into_array()` | ✅ | ||

`into_values()` | ✅ | |||

Rollup | `rollup()` | ✅ | ||

min_n_by() functions | ||||

Aggregate | `min_n_by()` | ✅ | ||

Accessor | `into_values()` | ✅ | ||

Rollup | `rollup()` | ✅ |

### Percentile approximation functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental | |
---|---|---|---|---|

tdigest() functions | ||||

Aggregate | `tdigest()` | ✅ | ||

Accessor | `approx_percentile()` | ✅ | ||

`approx_percentile_rank()` | ✅ | |||

`mean()` | ✅ | |||

`num_vals()` | ✅ | |||

Rollup | `rollup()` | ✅ | ||

uddsketch() functions | ||||

Aggregate | `uddsketch()` | ✅ | ||

Accessor | `approx_percentile()` | ✅ | ||

`approx_percentile_array()` | ✅ | |||

`approx_percentile_rank()` | ✅ | |||

`error()` | ✅ | |||

`mean()` | ✅ | |||

`num_vals()` | ✅ | |||

Alternate aggregate | `percentile_agg()` | ✅ | ||

Rollup | `rollup()` | ✅ |

### State tracking functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental | |
---|---|---|---|---|

compact_state_agg() functions | ||||

Aggregate | `compact_state_agg()` | ✅ | ✅ | |

Accessor | `duration_in()` | ✅ | ✅ | |

`interpolated_duration_in()` | ✅ | ✅ | ||

`into_values()` | ✅ | ✅ | ||

Rollup | `rollup()` | ✅ | ✅ | |

heartbeat_agg() functions | ||||

Aggregate | `heartbeat_agg()` | ✅ | ||

Accessor | `dead_ranges()` | ✅ | ||

`downtime()` | ✅ | |||

`interpolate()` | ✅ | |||

`interpolated_downtime()` | ✅ | |||

`interpolated_uptime()` | ✅ | |||

`live_at()` | ✅ | |||

`live_ranges()` | ✅ | |||

`uptime()` | ✅ | |||

Rollup | `rollup()` | ✅ | ||

state_agg() functions | ||||

Aggregate | `state_agg()` | ✅ | ||

Accessor | `duration_in()` | ✅ | ||

`interpolated_duration_in()` | ✅ | |||

`interpolated_state_periods()` | ✅ | |||

`interpolated_state_timeline()` | ✅ | |||

`into_values()` | ✅ | |||

`state_at()` | ✅ | |||

`state_periods()` | ✅ | |||

`state_timeline()` | ✅ | |||

Rollup | `rollup()` | ✅ |

### Statistical and regression analysis functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental | |
---|---|---|---|---|

stats_agg() (one variable) functions | ||||

Aggregate | `stats_agg() (one variable)` | ✅ | ||

Accessor | `average()` | ✅ | ||

`kurtosis()` | ✅ | |||

`num_vals()` | ✅ | |||

`skewness()` | ✅ | |||

`stddev()` | ✅ | |||

`sum()` | ✅ | |||

`variance()` | ✅ | |||

Rollup | `rolling()` | ✅ | ||

`rollup()` | ✅ | |||

stats_agg() (two variables) functions | ||||

Aggregate | `stats_agg() (two variables)` | ✅ | ||

Accessor | `average_y()` , `average_x()` | ✅ | ||

`corr()` | ✅ | |||

`covariance()` | ✅ | |||

`determination_coeff()` | ✅ | |||

`intercept()` | ✅ | |||

`kurtosis_y()` , `kurtosis_x()` | ✅ | |||

`num_vals()` | ✅ | |||

`skewness_y()` , `skewness_x()` | ✅ | |||

`slope()` | ✅ | |||

`stddev_y()` , `stddev_x()` | ✅ | |||

`sum_y()` , `sum_x()` | ✅ | |||

`variance_y()` , `variance_x()` | ✅ | |||

`x_intercept()` | ✅ | |||

Rollup | `rolling()` | ✅ | ||

`rollup()` | ✅ |

### Time weighted calculations functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental | |
---|---|---|---|---|

time_weight() functions | ||||

Aggregate | `time_weight()` | ✅ | ||

Accessor | `average()` | ✅ | ||

`first_time()` | ✅ | |||

`first_val()` | ✅ | |||

`integral()` | ✅ | |||

`interpolated_average()` | ✅ | |||

`interpolated_integral()` | ✅ | |||

`last_time()` | ✅ | |||

`last_val()` | ✅ | |||

Rollup | `rollup()` | ✅ |

### General functions

Hyperfunction type | Hyperfunction name | Toolkit | Experimental |
---|---|---|---|

Bucket | `time_bucket()` | ||

`timescaledb_experimental.time_bucket_ng()` | ✅ | ||

One step aggregate | `approximate_row_count()` | ||

`first()` | |||

`histogram()` | |||

`last()` | |||

One step operation | `days_in_month()` | ✅ | |

`month_normalize()` | ✅ |

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

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,sum(abs_delta) as volatilityFROM (SELECT device_id,abs(val - lag(val) OVER last_day) as abs_deltaFROM measurementsWHERE ts >= now()-'1 day'::interval) calc_deltaGROUP BY device_id;

You can express the same query with a function pipeline like this:

SELECT device_id,timevector(ts, val) -> sort() -> delta() -> abs() -> sum() as volatilityFROM measurementsWHERE ts >= now()-'1 day'::intervalGROUP BY device_id;

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 blog post.

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 Toolkit repository.

We want and need your feedback! What are the frustrating parts of analyzing time-series data? What takes far more code than you feel it should? What runs slowly, or only runs quickly after many rewrites? We want to solve community-wide problems and incorporate as much feedback as possible.

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

Keywords

Found an issue on this page?

Report an issue!