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
Hypertables and chunks
- hypertable_detailed_size
- Get detailed information about disk space used by a hypertable
- hypertable_size
- Get the total disk space used by a hypertable
- add_dimension
- Add a space-partitioning dimension to a hypertable
- 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_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
- disable_chunk_skipping
- Disable range tracking for columns of chunks from a hypertable
- enable_chunk_skipping
- Enable range tracking for columns of chunks from a hypertable
- hypertable_approximate_detailed_size
- Get detailed information about approximate disk space used by a hypertable
- hypertable_approximate_size
- Get the approximate total disk space used by 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_continuous_aggregate_policy
- Add policy to schedule automatic refresh of a continuous aggregate
- add_policies
- Add refresh, compression, and data retention policies on a continuous aggregate
- ALTER MATERIALIZED 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 or another continuous aggregate
- 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: downsampling
- asap_smooth
- Downsample a time series using the ASAP smoothing algorithm
- gp_lttb
- Downsample a time series using the Largest Triangle Three Buckets method, while preserving gaps in original data
- lttb
- Downsample a time series using the Largest Triangle Three Buckets method
Hyperfunctions: saturating math
- saturating_add
- Adds two numbers, saturating at the 32-bit integer 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 32-bit integer bounds instead of overflowing
- saturating_sub
- Subtracts one number from another, saturating at the 32-bit integer bounds instead of overflowing
- saturating_sub_pos
- Subtracts one number from another, saturating at 0 for the minimum bound
Hyperfunctions: financial analysis: candlestick_agg()
- candlestick
- Transform pre-aggregated candlestick data into the correct form to use with
candlestick_aggfunctions - 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
Hyperfunctions: state tracking: compact_state_agg()
- compact_state_agg
- Aggregate state data into a state aggregate for further analysis
- duration_in
- Calculate the total time spent in a given state from a state aggregate
- interpolated_duration_in
- Calculate the total time spent in a given state from a state aggregate, interpolating values at time bucket boundaries
- into_values
- Expand a state aggregate into a set of rows displaying the duration of each state
- rollup
- Combine multiple state aggregates
Hyperfunctions: frequency analysis: count_min_sketch()
- approx_count
- Estimate the number of times a value appears from a
CountMinSketch - count_min_sketch
- Aggregate data into a
CountMinSketchfor approximate counting
Hyperfunctions: counters and gauges: counter_agg()
- corr
- Calculate the correlation coefficient from a counter aggregate
- counter_agg
- Aggregate counter data into an intermediate form for further analysis
- counter_zero_time
- Calculate the time when the counter value is predicted to have been zero
- delta
- Calculate the change in a counter from a counter aggregate
- extrapolated_delta
- Calculate the extrapolated change from a counter aggregate
- extrapolated_rate
- Calculate the extrapolated rate of change from a counter aggregate
- first_time
- Get the first timestamp from a counter aggregate
- first_val
- Get the first value from a counter aggregate
- idelta_left
- Calculate the instantaneous change at the left, or earliest, edge of a counter aggregate
- idelta_right
- Calculate the instantaneous change at the right, or latest, edge of a counter aggregate
- intercept
- Calculate the y-intercept from a counter aggregate
- interpolated_delta
- Calculate the change in a counter, interpolating values at boundaries as needed
- interpolated_rate
- Calculate the rate of change in a counter, interpolating values at boundaries as needed
- irate_left
- Calculate the instantaneous rate of change at the left, or earliest, edge of a counter aggregate
- irate_right
- Calculate the instantaneous rate of change at the right, or latest, edge of a counter aggregate
- last_time
- Get the last timestamp from a counter aggregate
- last_val
- Get the last value from a counter aggregate
- num_changes
- Get the number of times a counter changed from a counter aggregate
- num_elements
- Get the number of points with distinct timestamps from a counter aggregate
- num_resets
- Get the number of counter resets from a counter aggregate
- rate
- Calculate the rate of change from a counter aggregate
- rollup
- Combine multiple counter aggregates
- slope
- Calculate the slope from a counter aggregate
- time_delta
- Calculate the difference between the first and last times from a counter aggregate
- with_bounds
- Add bounds to a counter aggregate
Hyperfunctions: frequency analysis: freq_agg()
- freq_agg
- Aggregate data into a space-saving aggregate for further frequency analysis
- into_values
- Get a table of all frequency estimates from a space-saving aggregate
- max_frequency
- Get the maximum bound of the estimated frequency for a given value in a space-saving aggregate
- mcv_agg
- Aggregate data into a space-saving aggregate for further calculation of most-frequent values
- min_frequency
- Get the minimum bound of the estimated frequency for a given value in a space-saving aggregate
- rollup
- Combine multiple frequency aggregates
- topn
- Get the top N most common values from a space-saving aggregate
Hyperfunctions: counters and gauges: gauge_agg()
- corr
- Calculate the correlation coefficient from a gauge aggregate
- delta
- Calculate the change in a gauge from a gauge aggregate
- extrapolated_delta
- Calculate the extrapolated change from a gauge aggregate
- extrapolated_rate
- Calculate the extrapolated rate of change from a gauge aggregate
- gauge_agg
- Aggregate gauge data into an intermediate form for further analysis
- gauge_zero_time
- Calculate the time when the gauge value is predicted to have been zero
- idelta_left
- Calculate the instantaneous change at the left, or earliest, edge of a gauge aggregate
- idelta_right
- Calculate the instantaneous change at the right, or latest, edge of a gauge aggregate
- intercept
- Calculate the y-intercept from a gauge aggregate
- interpolated_delta
- Calculate the change in a gauge, interpolating values at boundaries as needed
- interpolated_rate
- Calculate the rate of change in a gauge, interpolating values at boundaries as needed
- irate_left
- Calculate the instantaneous rate of change at the left, or earliest, edge of a gauge aggregate
- irate_right
- Calculate the instantaneous rate of change at the right, or latest, edge of a gauge aggregate
- num_changes
- Get the number of times a gauge changed from a gauge aggregate
- num_elements
- Get the number of points with distinct timestamps from a gauge aggregate
- rate
- Calculate the rate of change from a gauge aggregate
- rollup
- Combine multiple gauge aggregates
- slope
- Calculate the slope from a gauge aggregate
- time_delta
- Calculate the difference between the first and last times from a gauge aggregate
- with_bounds
- Add bounds to a gauge aggregate
Hyperfunctions: state tracking: heartbeat_agg()
- dead_ranges
- Get the down intervals from a heartbeat_agg
- downtime
- Get the total time dead during a heartbeat aggregate
- heartbeat_agg
- Create a liveness aggregate from a set of heartbeats
- interpolate
- Adjust a heartbeat aggregate with predecessor information
- interpolated_downtime
- Get the total time dead from a heartbeat aggregate and predecessor
- interpolated_uptime
- Get the total time live from a heartbeat aggregate and predecessor
- live_at
- Test if the aggregate has a heartbeat covering a given time
- live_ranges
- Get the live intervals from a heartbeat_agg
- num_gaps
- Count the number of gaps between live ranges
- num_live_ranges
- Count the number of live ranges
- rollup
- Combine multiple heartbeat aggregates
- trim_to
- Reduce the covered interval of a heartbeat aggregate
- uptime
- Get the total time live during a heartbeat aggregate
Hyperfunctions: approximate count distinct: hyperloglog()
- 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: max_n()
- 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
Hyperfunctions: minimum and maximum: max_n_by()
- 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
Hyperfunctions: minimum and maximum: min_n()
- 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
Hyperfunctions: minimum and maximum: min_n_by()
- 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: state tracking: state_agg()
- duration_in
- Calculate the total time spent in a given state from a state aggregate
- interpolated_duration_in
- Calculate the total time spent in a given state from a state aggregate, interpolating values at time bucket boundaries
- interpolated_state_periods
- Get the time periods corresponding to a given state from a state aggregate, interpolating values at time bucket boundaries
- interpolated_state_timeline
- Get a state of all states from a state aggregate, interpolating values at time bucket boundaries
- into_values
- Expand the state aggregate into a set of rows, displaying the duration of each state
- rollup
- Combine multiple state aggregates
- state_at
- Deterimine the state at a given time
- state_periods
- Get the time periods corresponding to a given state from a state aggregate
- state_timeline
- Get a state of all states from a state aggregate
- state_agg
- Aggregate state data into a state aggregate for further analysis
Hyperfunctions: statistical and regression analysis: stats_agg() (one variable)
- 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
- skewness
- Calculate the skewness from a one-dimensional statistical aggregate
- stats_agg (one variable)
- Aggregate data into an intermediate statistical aggregate form for further calculation
- stddev
- Calculate the standard deviation from a one-dimensional statistical aggregate
- sum
- Calculate the sum from a one-dimensional statistical aggregate
- variance
- Calculate the variance from a one-dimensional statistical aggregate
Hyperfunctions: statistical and regression analysis: stats_agg() (two variables)
- 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
- sum_y, sum_x
- Calculate the sum 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
- x_intercept
- Calculate the x-intercept from a two-dimensional statistical aggregate
Hyperfunctions: percentile approximation: tdigest()
- 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
tdigestfor further calculation of percentile estimates
Hyperfunctions: gapfilling
- time_bucket_gapfill
- Bucket rows by time interval while filling gaps in data
- locf
- Fill in missing values by carrying the last observed value forward
- interpolate
- Fill in missing values by linear interpolation
Hyperfunctions: time weighted calculations: time_weight()
- average
- Calculate the time-weighted average of values in a
TimeWeightSummary - first_time
- Get the first timestamp from a
TimeWeightSummaryaggregate - first_val
- Get the first value from a
TimeWeightSummaryaggregate - integral
- Calculate the integral from a
TimeWeightSummary - interpolated_average
- Calculate the time-weighted average over an interval, while interpolating the interval bounds
- interpolated_integral
- Calculate the integral over an interval, while interpolating the interval bounds
- last_time
- Get the last timestamp from a
TimeWeightSummaryaggregate - last_val
- Get the last value from a
TimeWeightSummaryaggregate - rollup
- Combine multiple
TimeWeightSummaries - time_weight
- Aggregate data into an intermediate time-weighted aggregate form for further calculation
Hyperfunctions: percentile approximation: uddsketch()
- approx_percentile
- Estimate the value at a given percentile from a
uddsketch - approx_percentile_array
- Estimate the values for an array of given percentiles 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
uddsketchfor further calculation of percentile estimates
Actions and automation
- add_job
- Add a job to run a user-defined action automatically
- alter_job
- Alter a job that is scheduled to run automatically
- delete_job
- Delete a job from the automatic scheduler
- run_job
- Manually run a job
Distributed hypertables
- add_data_node
- Add a new data node to a multi-node cluster
- alter_data_node
- Change the configuration of a data node
- 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 hash 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.chunk_compression_settings
- Get information about compression settings for all chunks
- 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.hypertable_compression_settings
- Get information about compression settings for all 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
Other
- timescaledb_information.job_errors
- Get information about background job errors
- timescaledb_information.history
- Get information about background job execution
Keywords
Found an issue on this page?Report an issue or Edit this page in GitHub.