Versions:

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.

Hypertables and chunks

add_dimension
Add a space-partitioning dimension to a hypertable
add_reorder_policy
Add a policy to reorder rows in hypertable chunks
attach_tablespace
Attach a tablespace to a hypertable
chunks_detailed_size
Get detailed information about disk space used by chunks
create_hypertable
Create a hypertable
CREATE INDEX (Transaction Per Chunk)
Create a hypertable index using a separate transaction for each chunk
detach_tablespace
Detach a tablespace from a hypertable
detach_tablespaces
Detach all tablespaces from a hypertable
hypertable_detailed_size
Get detailed information about disk space used by a hypertable
hypertable_index_size
Get the disk space used by a hypertable index
hypertable_size
Get the total disk space used by a hypertable
move_chunk
Move a chunk and its indexes to a different tablespace
remove_reorder_policy
Remove a reorder policy from a hypertable
reorder_chunk
Reorder rows in a chunk
set_chunk_time_interval
Change the chunk time interval of a hypertable
set_integer_now_fun
Define the relationship between integer time values and actual time
show_chunks
Show the chunks belonging to a hypertable
show_tablespaces
Show the tablespaces attached to a hypertable

Continuous aggregates

add_policies
Add refresh, compression, and data retention policies on a continuous aggregate
add_continuous_aggregate_policy
Add policy to schedule automatic refresh of a continuous aggregate
ALTER MATERLIALIZED VIEW (Continuous Aggregate)
Change an existing continuous aggregate
alter_policies
Alter refresh, compression, or data retention policies on a continuous aggregate
cagg_migrate
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
refresh_continuous_aggregate
Manually refresh a continuous aggregate
remove_all_policies
Remove all policies from a continuous aggregate
remove_continuous_aggregate_policy
Remove a refresh policy from a continuous aggregate
remove_policies
Remove refresh, compression, or data retention policies from a continuous aggregate
show_policies
Show all policies that are currently set on a continuous aggregate

Compression

add_compression_policy
Add policy to schedule automatic compression of chunks
ALTER TABLE (Compression)
Change compression settings on a compressed hypertable
chunk_compression_stats
Get compression-related statistics for chunks
compress_chunk
Manually compress a given chunk
decompress_chunk
Decompress a compressed chunk
hypertable_compression_stats
Get hypertable statistics related to compression
recompress_chunk
Recompress a chunk that had new data inserted after compression
remove_compression_policy
Remove a compression policy from a hypertable

Data retention

add_retention_policy
Add a policy to drop older chunks
remove_retention_policy
Remove a retention policy from a hypertable
drop_chunks
Delete chunks by time range

Hyperfunctions: financial analysis

candlestick
Transform pre-aggregated candlestick data into the correct form to use with candlestick_agg functions
candlestick_agg
Aggregate tick data into an intermediate form for further calculation
close
Get the closing price from a candlestick aggregate
close_time
Get the timestamp corresponding to the closing time from a candlestick aggregate
high
Get the high price from a candlestick aggregate
high_time
Get the timestamp corresponding to the high time from a candlestick aggregate
low
Get the low price from a candlestick aggregate
low_time
Get the timestamp corresponding to the low time from a candlestick aggregate
open
Get the opening price from a candlestick aggregate
open_time
Get the timestamp corresponding to the open time from a candlestick aggregate
rollup
Roll up multiple Candlestick aggregates
volume
Get the total volume from a candlestick aggregate
vwap
Get the Volume Weighted Average Price from a candlestick aggregate
close
Get the closing price from an OHLC aggregate
close_time
Get the timestamp corresponding to the closing time from an OHLC aggregate
high
Get the high price from an OHLC aggregate
high_time
Get the timestamp corresponding to the high time from an OHLC aggregate
low
Get the low price from an OHLC aggregate
low_time
Get the timestamp corresponding to the low time from an OHLC aggregate
ohlc
Aggregate financial asset data into an intermediate form for further calculation
open
Get the opening price from an OHLC aggregate
open_time
Get the timestamp corresponding to the opening time from an OHLC aggregate
rollup
Roll up multiple OHLC aggregates

Hyperfunctions: approximate count distinct

approx_count_distinct
Aggregate data into a hyperloglog for approximate counting without specifying the number of buckets
distinct_count
Estimate the number of distinct values from a hyperloglog
hyperloglog
Aggregate data into a hyperloglog for approximate counting
rollup
Roll up multiple hyperloglogs
stderror
Estimate the relative standard error of a hyperloglog

Hyperfunctions: minimum and maximum

into_array
Returns an array of the highest values from a MaxN aggregate
into_values
Returns the highest values from a MaxN aggregate
max_n
Find the largest values in a set of data
rollup
Combine multiple MaxN aggregates
into_values
Returns the highest values and associated data from a MaxNBy aggregate
max_n_by
Track the largest values and associated data in a set of values
rollup
Combine multiple MaxNBy aggregates
into_array
Returns an array of the lowest values from a MinN aggregate
into_values
Returns the lowest values from a MinN aggregate
min_n
Find the smallest values in a set of data
rollup
Combine multiple MinN aggregates
into_values
Returns the lowest values and associated data from a MinNBy aggregate
min_n_by
Track the smallest values and associated data in a set of values
rollup
Combine multiple MinNBy aggregates

Hyperfunctions: statistical and regression analysis

average
Calculate the average from a one-dimensional statistical aggregate
kurtosis
Calculate the kurtosis from a one-dimensional statistical aggregate
num_vals
Calculate the number of values in a one-dimensional statistical aggregate
rolling
Combine multiple one-dimensional statistical aggregates to calculate rolling window aggregates
rollup
Combine multiple one-dimensional statistical aggregates
sum
Calculate the sum from a one-dimensional statistical aggregate
variance
Calculate the variance from a one-dimensional statistical aggregate
stddev
Calculate the standard deviation from a one-dimensional statistical aggregate
average_y, average_x
Calculate the average from a two-dimensional statistical aggregate for the dimension specified
corr
Calculate the correlation coefficient from a two-dimensional statistical aggregate
covariance
Calculate the covariance from a two-dimensional statistical aggregate
determination_coeff
Calculate the determination coefficient from a two-dimensional statistical aggregate
intercept
Calculate the intercept from a two-dimensional statistical aggregate
kurtosis_y, kurtosis_x
Calculate the kurtosis from a two-dimensional statistical aggregate for the dimension specified
num_vals
Calculate the number of values in a two-dimensional statistical aggregate
rolling
Combine multiple two-dimensional statistical aggregates to calculate rolling window aggregates
rollup
Combine multiple two-dimensional statistical aggregates
skewness_y, skewness_x
Calculate the skewness from a two-dimensional statistical aggregate for the dimension specified
slope
Calculate the slope from a two-dimensional statistical aggregate
stats_agg (two variables)
Aggregate data into an intermediate statistical aggregate form for further calculation
stddev_y, stddev_x
Calculate the standard deviation from a two-dimensional statistical aggregate for the dimension specified
variance_y, variance_x
Calculate the variance from a two-dimensional statistical aggregate for the dimension specified
sum_y, sum_x
Calculate the sum from a two-dimensional statistical aggregate for the dimension specified
x_intercept
Calculate the x-intercept from a two-dimensional statistical aggregate
stats_agg (one variable)
Aggregate data into an intermediate statistical aggregate form for further calculation
skewness
Calculate the skewness from a one-dimensional statistical aggregate

Hyperfunctions: percentile approximation

approx_percentile
Estimate the value at a given percentile from a tdigest
approx_percentile_rank
Estimate the percentile of a given value from a tdigest
mean
Calculate the exact mean from values in a tdigest
num_vals
Get the number of values contained in a tdigest
rollup
Roll up multiple tdigests
tdigest
Aggregate data in a tdigest for further calculation of percentile estimates
approx_percentile
Estimate the value at a given percentile from a uddsketch
approx_percentile_rank
Estimate the percentile of a given value from a uddsketch
error
Get the maximum relative error for a uddsketch
mean
Calculate the exact mean from values in a uddsketch
num_vals
Get the number of values contained in a uddsketch
percentile_agg
Aggregate data in a uddsketch, using some reasonable default values, for further calculation of percentile estimates
rollup
Roll up multiple uddsketches
uddsketch
Aggregate data in a uddsketch for further calculation of percentile estimates

Actions and automation

add_job
Add a job to run a user-defined action automatically
delete_job
Delete a job from the automatic scheduler
alter_job
Alter a job that is scheduled to run automatically
run_job
Manually run a job

Administration

dump_meta_data.sql
Output metadata for support requests and bug reports
get_telemetry_report
Get the telemetry string that is sent to Timescale servers
timescaledb_post_restore
Resume normal operations after restoring a database
timescaledb_pre_restore
Prepare a database for data restoration

Distributed hypertables

add_data_node
Add a new data node to a multi-node cluster
attach_data_node
Attach a data node to a distributed hypertable
cleanup_copy_chunk_operation
Clean up after a failed chunk move or chunk copy operation
copy_chunk
Copy a chunk between data nodes in a distributed hypertable
create_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
delete_data_node
Remove a data node from a database and detach it from all hypertables
detach_data_node
Detach a data node from one or all hypertables
distributed_exec
Execute a procedure across all the data nodes of a multi-node cluster
move_chunk
Move a chunk to a different data node in a multi-node cluster
set_number_partitions
Set the number of space partitions for a hypertable
set_replication_factor
Set the replication factor for a distributed hypertable

Hyperfunctions: general

approximate_row_count
Estimate the number of rows in a table
days_in_month
Calculates days in month given a timestamptz
first
Get the first value in one column when rows are ordered by another column
histogram
Partition the dataset into buckets and get the number of counts in each bucket
last
Get the last value in one column when rows are ordered by another column
month_normalize
Normalize a monthly metric based on number of days in month
time_bucket
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
timescaledb_information.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

corr
Calculate the correlation coefficient from values in a CounterSummary
counter_agg
Aggregate counter data into a CounterSummary for further analysis
counter_zero_time
Predict the time when a counter was at zero
delta
Calculate the change in a counter from values in a CounterSummary
extrapolated_delta
Calculate the extrapolated change in a counter from values in a CounterSummary
extrapolated_rate
Calculate the extrapolated rate of change from values in a CounterSummary
first_time, last_time
Get the first and last timestamps seen by CounterSummary aggregates
first_val, last_val
Get the first and last values seen by CounterSummary aggregates
gauge_agg
Aggregate gauge data into a GaugeSummary for further analysis
idelta_left, idelta_right
Calculate the instantaneous change from values in a CounterSummary
intercept
Calculate the intercept from values in a CounterSummary
interpolated_delta
Calculate the change in a counter, interpolated over some time period
interpolated_rate
Calculate the rate of change in a counter, interpolated over some time period
irate_left, irate_right
Calculate the instantaneous rate of change from values in a CounterSummary
rate
Calculate the rate of change from values in a CounterSummary
rollup
Roll up multiple CounterSummary aggregates
slope
Calculate the slope from values in a CounterSummary
time_delta
Calculate the difference between the start and end times from data in a CounterSummary
with_bounds
Add bounds to a CounterSummary
num_resets
Calculate the total number of times a counter is reset
num_elements
Calculate the number of points with distinct times from values in a CounterSummary
num_changes
Calculate the number of times a value changed within the time period of a CounterSummary

Hyperfunctions: downsampling

asap_smooth
Downsample a time series using the ASAP smoothing algorithm
gp_lttb
An implementation of our lttb algorithm that will preserve gaps in the original data
lttb
Downsample a time series using the Largest Triangle Three Buckets method

Hyperfunctions: frequency analysis

approx_count
Estimate an item's frequency from a count_min_sketch
count_min_sketch
Aggregate data in a count_min_sketch for calculation of estimates
duration_in
Calculate the total time spent in a given state from values in a state aggregate
freq_agg
Aggregate frequency data into a frequency aggregate for further analysis
interpolated_duration_in
Calculate the total time spent in a given state, interpolating values at interval boundaries if they don't exist
into_values
Calculate all frequency estimates from a frequency aggregate or top N aggregate
into_values
Calculate all state durations from a state aggregate
min_frequency, max_frequency
Calculate the minimum or maximum estimated frequencies of a value from a frequency aggregate
state_agg
Aggregate state data into a state aggregate for further analysis
topn
Calculate the top N most common values from data in a frequency or top N aggregate
topn_agg
Aggregate data in a top N aggregate for further calculation of most frequent values

Hyperfunctions: gapfilling and interpolation

interpolate
Linearly interpolate missing values when gapfilling
locf
Carry the last-seen value forward when gapfilling
time_bucket_gapfill
Bucket rows by time interval while filling gaps in data

Hyperfunctions: saturating math

saturating_add
Adds two numbers, saturating at the numeric bounds instead of overflowing
saturating_add_pos
Adds two numbers, saturating at 0 for the minimum bound
saturating_mul
Multiples two numbers, saturating at the numeric bounds instead of overflowing
saturating_sub
Subtracts one number from another, saturating at the numeric bounds instead of overflowing
saturating_sub_pos
Subtracts one number from another, saturating at 0 for the minimum bound

Hyperfunctions: time weighted averages

average
Calculate the time-weighted average of values in a TimeWeightSummary
first_time, last_time
Get the first and last timestamps seen by TimeWeightSummary aggregates
first_val, last_val
Get the first and last values seen by TimeWeightSummary aggregates
integral
Calculate the time-weighted integral of values in a TimeWeightSummary
interpolated_average
Calculate the time-weighted average of values within an interval, interpolating the interval bounds
interpolated_integral
Calculate the time-weighted integral of values within an interval, interpolating the interval bounds
rollup
Roll up multiple TimeWeightSummaries
time_weight
Aggregate data in a TimeWeightSummary for further time-weighted analysis

Found an issue on this page?

Report an issue!

Keywords

Related Content