- TimescaleDB API reference
- Hypertables and chunks
- Continuous aggregates
- Compression
- Data retention
- Hyperfunctions: financial analysis
- Hyperfunctions: state aggregates
- Hyperfunctions: approximate count distinct
- Hyperfunctions: minimum and maximum
- Hyperfunctions: statistical and regression analysis
- Hyperfunctions: percentile approximation
- Hyperfunctions: time weighted calculations
- Actions and automation
- Administration
- Distributed hypertables
- Hyperfunctions: general
- Informational views
- Hyperfunctions: metric aggregation
- Hyperfunctions: downsampling
- Hyperfunctions: frequency analysis
- Hyperfunctions: gapfilling and interpolation
- Hyperfunctions: saturating math
TimescaleDB API reference
TimescaleDB provides many SQL functions and views to help you interact with and manage your data. See a full list below or search by keyword to find reference documentation for a specific API.
Suggested filters
- hypertable
- continuous aggregate
- compression
- hyperfunction
- data retention
- distributed hypertable
Hypertables and chunks
Add a space-partitioning dimension to a hypertable
Add a policy to reorder rows in hypertable chunks
Attach a tablespace to a hypertable
Get detailed information about disk space used by chunks
Create a hypertable
CREATE INDEX (Transaction Per Chunk)
Create a hypertable index using a separate transaction for each chunk
Detach a tablespace from a hypertable
Detach all tablespaces from a hypertable
Get detailed information about disk space used by a hypertable
Get the disk space used by a hypertable index
Get the total disk space used by a hypertable
Move a chunk and its indexes to a different tablespace
Remove a reorder policy from a hypertable
Reorder rows in a chunk
Change the chunk time interval of a hypertable
Define the relationship between integer time values and actual time
Show the chunks belonging to a hypertable
Show the tablespaces attached to a hypertable
Continuous aggregates
add_continuous_aggregate_policy
Add policy to schedule automatic refresh of a continuous aggregate
Add refresh, compression, and data retention policies on a continuous aggregate
ALTER MATERLIALIZED VIEW (Continuous Aggregate)
Change an existing continuous aggregate
Alter refresh, compression, or data retention policies on a continuous aggregate
Migrate a continuous aggregate from the old format to the new format introduced in TimescaleDB 2.7
CREATE MATERIALIZED VIEW (Continuous Aggregate)
Create a continuous aggregate on a hypertable
DROP MATERIALIZED VIEW (Continuous Aggregate)
Drop a continuous aggregate view
Manually refresh a continuous aggregate
Remove all policies from a continuous aggregate
Remove refresh, compression, or data retention policies from a continuous aggregate
remove_continuous_aggregate_policy
Remove a refresh policy from a continuous aggregate
Show all policies that are currently set on a continuous aggregate
Compression
Add policy to schedule automatic compression of chunks
Change compression settings on a compressed hypertable
Get compression-related statistics for chunks
Manually compress a given chunk
Decompress a compressed chunk
Get hypertable statistics related to compression
Recompress a chunk that had new data inserted after compression
Remove a compression policy from a hypertable
Data retention
Add a policy to drop older chunks
Remove a retention policy from a hypertable
Delete chunks by time range
Hyperfunctions: financial analysis
Transform pre-aggregated candlestick data into the correct form to use with candlestick_agg
functions
Aggregate tick data into an intermediate form for further calculation
Get the closing price from a candlestick aggregate
Get the timestamp corresponding to the closing time from a candlestick aggregate
Get the high price from a candlestick aggregate
Get the timestamp corresponding to the high time from a candlestick aggregate
Get the low price from a candlestick aggregate
Get the timestamp corresponding to the low time from a candlestick aggregate
Get the opening price from a candlestick aggregate
Get the timestamp corresponding to the open time from a candlestick aggregate
Roll up multiple Candlestick aggregates
Get the total volume from a candlestick aggregate
Get the Volume Weighted Average Price from a candlestick aggregate
Get the closing price from an OHLC aggregate
Get the timestamp corresponding to the closing time from an OHLC aggregate
Get the high price from an OHLC aggregate
Get the timestamp corresponding to the high time from an OHLC aggregate
Get the low price from an OHLC aggregate
Get the timestamp corresponding to the low time from an OHLC aggregate
Aggregate financial asset data into an intermediate form for further calculation
Get the opening price from an OHLC aggregate
Roll up multiple OHLC aggregates
Get the timestamp corresponding to the opening time from an OHLC aggregate
Hyperfunctions: state aggregates
Get the down intervals from a heartbeat_agg
Get the total time dead during a heartbeat aggregate
Create a liveness aggregate from a set of heartbeats
Adjust a heartbeat aggregate with predecessor information
Get the total time dead from a heartbeat aggregate and predecessor
Get the total time live from a heartbeat aggregate and predecessor
Test if the aggregate has a heartbeat covering a given time
Get the live intervals from a heartbeat_agg
Combine multiple heartbeat aggregates
Get the total time live during a heartbeat aggregate
Calculate the total time spent in a given state from values in a state aggregate
Calculate the total time spent in a given state, interpolating values at interval boundaries if they don’t exist
Get time periods for a state from a timeline aggregate
Get time periods for a state from a timeline aggregate
Calculate all state durations from a state aggregate
Rollup multiple StateAgg
aggregates
Aggregate state data into a state aggregate for further analysis
Get time periods for a state from a timeline aggregate
Get all state periods from a timeline aggregate
Hyperfunctions: approximate count distinct
Aggregate data into a hyperloglog for approximate counting without specifying the number of buckets
Estimate the number of distinct values from a hyperloglog
Aggregate data into a hyperloglog for approximate counting
Roll up multiple hyperloglogs
Estimate the relative standard error of a hyperloglog
Hyperfunctions: minimum and maximum
Returns an array of the highest values from a MaxN aggregate
Returns the highest values from a MaxN aggregate
Find the largest values in a set of data
Combine multiple MaxN aggregates
Returns the highest values and associated data from a MaxNBy aggregate
Track the largest values and associated data in a set of values
Combine multiple MaxNBy aggregates
Returns an array of the lowest values from a MinN aggregate
Returns the lowest values from a MinN aggregate
Find the smallest values in a set of data
Combine multiple MinN aggregates
Returns the lowest values and associated data from a MinNBy aggregate
Track the smallest values and associated data in a set of values
Combine multiple MinNBy aggregates
Hyperfunctions: statistical and regression analysis
Calculate the kurtosis from a one-dimensional statistical aggregate
Calculate the number of values in a one-dimensional statistical aggregate
Combine multiple one-dimensional statistical aggregates to calculate rolling window aggregates
Combine multiple one-dimensional statistical aggregates
Calculate the skewness from a one-dimensional statistical aggregate
Aggregate data into an intermediate statistical aggregate form for further calculation
Calculate the standard deviation from a one-dimensional statistical aggregate
Calculate the sum from a one-dimensional statistical aggregate
Calculate the variance from a one-dimensional statistical aggregate
Calculate the average from a two-dimensional statistical aggregate for the dimension specified
Calculate the correlation coefficient from a two-dimensional statistical aggregate
Calculate the covariance from a two-dimensional statistical aggregate
Calculate the determination coefficient from a two-dimensional statistical aggregate
Calculate the intercept from a two-dimensional statistical aggregate
Calculate the kurtosis from a two-dimensional statistical aggregate for the dimension specified
Calculate the number of values in a two-dimensional statistical aggregate
Combine multiple two-dimensional statistical aggregates to calculate rolling window aggregates
Combine multiple two-dimensional statistical aggregates
Calculate the skewness from a two-dimensional statistical aggregate for the dimension specified
Calculate the slope from a two-dimensional statistical aggregate
Aggregate data into an intermediate statistical aggregate form for further calculation
Calculate the standard deviation from a two-dimensional statistical aggregate for the dimension specified
Calculate the sum from a two-dimensional statistical aggregate for the dimension specified
Calculate the variance from a two-dimensional statistical aggregate for the dimension specified
Calculate the x-intercept from a two-dimensional statistical aggregate
Calculate the average from a one-dimensional statistical aggregate
Hyperfunctions: percentile approximation
Estimate the value at a given percentile from a tdigest
Estimate the percentile of a given value from a tdigest
Calculate the exact mean from values in a tdigest
Get the number of values contained in a tdigest
Roll up multiple tdigest
s
Aggregate data in a tdigest
for further calculation of percentile estimates
Estimate the value at a given percentile from a uddsketch
Estimate the values for an array of given percentiles from a uddsketch
Estimate the percentile of a given value from a uddsketch
Get the maximum relative error for a uddsketch
Calculate the exact mean from values in a uddsketch
Get the number of values contained in a uddsketch
Aggregate data in a uddsketch, using some reasonable default values, for further calculation of percentile estimates
Roll up multiple uddsketch
es
Aggregate data in a uddsketch
for further calculation of percentile estimates
Hyperfunctions: time weighted calculations
Calculate the time-weighted average of values in a TimeWeightSummary
Get the first timestamp from a TimeWeightSummary
aggregate
Get the first value from a TimeWeightSummary
aggregate
Calculate the integral from a TimeWeightSummary
Calculate the time-weighted average over an interval, while interpolating the interval bounds
Calculate the integral over an interval, while interpolating the interval bounds
Get the last timestamp from a TimeWeightSummary
aggregate
Get the last value from a TimeWeightSummary
aggregate
Combine multiple TimeWeightSummaries
Aggregate data into an intermediate time-weighted aggregate form for further calculation
Actions and automation
Add a job to run a user-defined action automatically
Alter a job that is scheduled to run automatically
Delete a job from the automatic scheduler
Manually run a job
Administration
Output metadata for support requests and bug reports
Get the telemetry string that is sent to Timescale servers
Resume normal operations after restoring a database
Prepare a database for data restoration
Distributed hypertables
Add a new data node to a multi-node cluster
Attach a data node to a distributed hypertable
Clean up after a failed chunk move or chunk copy operation
Copy a chunk between data nodes in a distributed hypertable
Create a distributed hypertable in a multi-node cluster
create_distributed_restore_point
Create a consistent restore point for all nodes in a multi-node cluster
Remove a data node from a database and detach it from all hypertables
Detach a data node from one or all hypertables
Execute a procedure across all the data nodes of a multi-node cluster
Move a chunk to a different data node in a multi-node cluster
Set the number of space partitions for a hypertable
Set the replication factor for a distributed hypertable
Hyperfunctions: general
Estimate the number of rows in a table
Calculates days in month given a timestamptz
Get the first value in one column when rows are ordered by another column
Partition the dataset into buckets and get the number of counts in each bucket
Get the last value in one column when rows are ordered by another column
Normalize a monthly metric based on number of days in month
Bucket rows by time interval to calculate aggregates
timescaledb_experimental.time_bucket_ng
Bucket rows by time interval with support for time zones, months, and years
Informational views
timescaledb_information.chunks
Get metadata about hypertable chunks
timescaledb_information.compression_settings
Get information about compression settings for hypertables
timescaledb_information.continuous_aggregates
Get metadata and settings information for continuous aggregates
timescaledb_information.data_nodes
Get information on data nodes in a multi-node cluster
timescaledb_information.dimensions
Get information on the dimensions of hypertables
timescaledb_information.hypertables
Get metadata about hypertables
timescaledb_information.job_stats
Get information and statistics about automatically run jobs
Get information about all jobs registered with the automatic scheduler
timescaledb_experimental.policies
Get information about all policies set on continuous aggregates
Hyperfunctions: metric aggregation
Calculate the correlation coefficient from values in a CounterSummary
Aggregate counter data into a CounterSummary
for further analysis
Predict the time when a counter was at zero
Calculate the change in a counter from values in a CounterSummary
Calculate the extrapolated change in a counter from values in a CounterSummary
Calculate the extrapolated rate of change from values in a CounterSummary
Get the first and last timestamps seen by CounterSummary
aggregates
Get the first and last values seen by CounterSummary
aggregates
Aggregate gauge data into a GaugeSummary
for further analysis
Calculate the instantaneous change from values in a CounterSummary
Calculate the intercept from values in a CounterSummary
Calculate the change in a counter, interpolated over some time period
Calculate the rate of change in a counter, interpolated over some time period
Calculate the instantaneous rate of change from values in a CounterSummary
Calculate the number of times a value changed within the time period of a CounterSummary
Calculate the number of points with distinct times from values in a CounterSummary
Calculate the total number of times a counter is reset
Calculate the rate of change from values in a CounterSummary
Roll up multiple CounterSummary
aggregates
Calculate the slope from values in a CounterSummary
Calculate the difference between the start and end times from data in a CounterSummary
Add bounds to a CounterSummary
Hyperfunctions: downsampling
Downsample a time series using the ASAP smoothing algorithm
An implementation of our lttb algorithm that will preserve gaps in the original data
Downsample a time series using the Largest Triangle Three Buckets method
Hyperfunctions: frequency analysis
Estimate an item’s frequency from a count_min_sketch
Aggregate data in a count_min_sketch
for calculation of estimates
Aggregate frequency data into a frequency aggregate for further analysis
Calculate all frequency estimates from a frequency aggregate or top N aggregate
Calculate the minimum or maximum estimated frequencies of a value from a frequency aggregate
Calculate the top N most common values from data in a frequency or top N aggregate
Aggregate data in a top N aggregate for further calculation of most frequent values
Hyperfunctions: gapfilling and interpolation
Linearly interpolate missing values when gapfilling
Carry the last-seen value forward when gapfilling
Bucket rows by time interval while filling gaps in data
Hyperfunctions: saturating math
Adds two numbers, saturating at the numeric bounds instead of overflowing
Adds two numbers, saturating at 0 for the minimum bound
Multiples two numbers, saturating at the numeric bounds instead of overflowing
Subtracts one number from another, saturating at the numeric bounds instead of overflowing
Subtracts one number from another, saturating at 0 for the minimum bound