### Create a distributed hypertable with SQL

Source: https://www.tigerdata.com/docs/api/latest/distributed-hypertables/create_distributed_hypertable

Examples showing how to initialize a distributed hypertable using the create_distributed_hypertable function. The first example uses default data nodes, while the second specifies a custom array of data nodes.

```sql
SELECT create_distributed_hypertable('conditions', 'time', 'location');
```

```sql
SELECT create_distributed_hypertable('conditions', 'time', 'location',
    data_nodes => '{ "data_node_1", "data_node_2", "data_node_4", "data_node_7" }');
```

--------------------------------

### Continuous Aggregate Examples

Source: https://www.tigerdata.com/docs/api/latest/continuous-aggregates

Practical examples of creating daily, thirty-day, and hourly continuous aggregates on a conditions hypertable. These demonstrate the use of time_bucket for grouping time-series data.

```sql
-- Daily aggregate
CREATE MATERIALIZED VIEW continuous_aggregate_daily( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
  SELECT time_bucket('1day', timec), min(location), sum(temperature), sum(humidity)
    FROM conditions
    GROUP BY time_bucket('1day', timec);

-- Thirty day aggregate
CREATE MATERIALIZED VIEW continuous_aggregate_thirty_day( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
  SELECT time_bucket('30day', timec), min(location), sum(temperature), sum(humidity)
    FROM conditions
    GROUP BY time_bucket('30day', timec);

-- Hourly aggregate
CREATE MATERIALIZED VIEW continuous_aggregate_hourly( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
  SELECT time_bucket('1h', timec), min(location), sum(temperature), sum(humidity)
    FROM conditions
    GROUP BY time_bucket('1h', timec);
```

--------------------------------

### Register and Schedule Jobs with add_job()

Source: https://www.tigerdata.com/docs/api/latest/jobs-automation/add_job

Demonstrates how to define a procedure and register it as a scheduled job using add_job(). Examples include simple interval scheduling and complex configurations with specific start times and time zones.

```SQL
CREATE OR REPLACE PROCEDURE user_defined_action(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
BEGIN
  RAISE NOTICE 'Executing action % with config %', job_id, config;
END
$$;

SELECT add_job('user_defined_action','1h');
SELECT add_job('user_defined_action','1h', fixed_schedule => false);

-- December 4, 2022 is a Sunday
SELECT add_job('user_defined_action','1 week', initial_start => '2022-12-04 00:00:00+00'::timestamptz);
-- if subject to DST
SELECT add_job('user_defined_action','1 week', initial_start => '2022-12-04 00:00:00+00'::timestamptz, timezone => 'Europe/Berlin');
```

--------------------------------

### Example Continuous Aggregate Definitions

Source: https://www.tigerdata.com/docs/api/latest/continuous-aggregates/create_materialized_view

Practical examples of creating continuous aggregates for different time intervals (daily, thirty-day, and hourly) based on a 'conditions' hypertable.

```sql
-- Daily aggregate
CREATE MATERIALIZED VIEW continuous_aggregate_daily( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
  SELECT time_bucket('1day', timec), min(location), sum(temperature), sum(humidity)
    FROM conditions
    GROUP BY time_bucket('1day', timec);

-- Thirty-day aggregate
CREATE MATERIALIZED VIEW continuous_aggregate_thirty_day( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
  SELECT time_bucket('30day', timec), min(location), sum(temperature), sum(humidity)
    FROM conditions
    GROUP BY time_bucket('30day', timec);

-- Hourly aggregate
CREATE MATERIALIZED VIEW continuous_aggregate_hourly( timec, minl, sumt, sumh )
WITH (timescaledb.continuous) AS
  SELECT time_bucket('1h', timec), min(location), sum(temperature), sum(humidity)
    FROM conditions
    GROUP BY time_bucket('1h', timec);
```

--------------------------------

### Extended Example: Daily Statistical Summary and Calculations

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/statistical-and-regression-analysis/stats_agg-two-variables

Demonstrates creating a daily statistical aggregate and then calculating the average of the dependent variable and the slope of the linear regression fit using the aggregate.

```SQL
WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        stats_agg(val2, val1) AS stats2D,
    FROM foo
    WHERE id = 'bar'
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    average_x(stats2D),
    slope(stats2D)
FROM t;
```

--------------------------------

### Calculate Deltas and Normalized Rates

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/counters-and-gauges/counter_agg

This example demonstrates how to use counter aggregates to calculate the delta between values and normalize those deltas against a total aggregate.

```SQL
WITH t as (
    SELECT
        date_trunc('day', ts) as dt,
        counter_agg(ts, val) AS counter_summary
    FROM foo
    WHERE id = 'bar'
    GROUP BY date_trunc('day')
), q as (
    SELECT rollup(counter_summary) AS full_cs
    FROM t
)
SELECT
    dt,
    delta(counter_summary),
    delta(counter_summary) / (SELECT delta(full_cs) FROM q LIMIT 1) as normalized
FROM t;
```

--------------------------------

### Convert PostgreSQL table to hypertable

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_hypertable_old

Examples demonstrating how to convert a standard table into a hypertable using the create_hypertable function with various parameters such as chunk intervals and conditional checks.

```SQL
SELECT create_hypertable('conditions', 'time');

SELECT create_hypertable('conditions', 'time', chunk_time_interval => 86400000000);
SELECT create_hypertable('conditions', 'time', chunk_time_interval => INTERVAL '1 day');

SELECT create_hypertable('conditions', 'time', if_not_exists => TRUE);
```

--------------------------------

### Enable Compression with SegmentBy and OrderBy

Source: https://www.tigerdata.com/docs/api/latest/compression/alter_table_compression

Example of enabling compression on a 'metrics' table, optimized for queries filtering by 'device_id' and ordered by time.

```sql
ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_orderby = 'time DESC', timescaledb.compress_segmentby = 'device_id');
```

--------------------------------

### Get First Timestamp with first_time()

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/time-weighted-calculations/time_weight

The first_time() function retrieves the timestamp of the very first data point within a TimeWeightSummary aggregate. It takes a TimeWeightSummary object as input and returns a TIMESTAMPTZ value representing the earliest timestamp.

```SQL
WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        time_weight('Linear', ts, val) AS tw
    FROM table
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    first_time(tw)
FROM t;
```

--------------------------------

### Modify continuous aggregate configuration using SQL

Source: https://www.tigerdata.com/docs/api/latest/continuous-aggregates/alter_materialized_view

Examples demonstrating how to toggle real-time aggregates, enable columnstore functionality, and rename columns within a continuous aggregate view.

```SQL
ALTER MATERIALIZED VIEW contagg_view SET (timescaledb.materialized_only = false);
```

```SQL
ALTER MATERIALIZED VIEW contagg_view SET (
  timescaledb.enable_columnstore = true,
  timescaledb.segmentby = 'symbol' );
```

```SQL
ALTER MATERIALIZED VIEW contagg_view RENAME COLUMN old_name TO new_name;
```

--------------------------------

### Ingest data into hypertable using COPY

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_table

Demonstrates creating a basic hypertable and performing a high-performance binary COPY ingestion.

```sql
CREATE TABLE t(time timestamptz, device text, value float) WITH (tsdb.hypertable);
COPY t FROM '/tmp/t.binary' WITH (format binary);
```

--------------------------------

### Example: Aggregate System Liveness (SQL)

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/state-tracking/heartbeat_agg

This example demonstrates how to use the heartbeat_agg function to calculate system liveness over a 10-day period starting from Jan 1, 2022. It assumes a system is considered unhealthy if no heartbeat is received within a 5-minute window.

```sql
SELECT heartbeat_agg(
  ping_time,
  '01-01-2022 UTC',
  '10 days',
  '5 min'
)
FROM system_health;
```

--------------------------------

### Shift bucket origin and time zones

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/time_bucket

Examples of using the origin parameter to define custom start points for buckets and applying time zone offsets for regional alignment.

```sql
SELECT time_bucket('1 week', timetz, TIMESTAMPTZ '2017-12-31')
  AS one_week, avg(cpu)
FROM metrics
GROUP BY one_week
WHERE time > TIMESTAMPTZ '2017-12-01'  AND time < TIMESTAMPTZ '2018-01-03'
ORDER BY one_week DESC LIMIT 10;

SELECT time_bucket(INTERVAL '2 hours', timetz::TIMESTAMP)
  AS five_min, avg(cpu)
FROM metrics
GROUP BY five_min
ORDER BY five_min DESC LIMIT 10;

SELECT time_bucket('1 month', ts, 'Europe/Berlin') AS month_bucket,
  avg(temperature) AS avg_temp
FROM weather
GROUP BY month_bucket
ORDER BY month_bucket DESC LIMIT 10;
```

--------------------------------

### Create HyperLogLog Aggregate with hyperloglog()

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/approximate-count-distinct/hyperloglog

Initializes a HyperLogLog object from a data column. Users specify the number of buckets to balance memory usage and accuracy, returning an intermediate aggregate for further processing.

```SQL
SELECT hyperloglog(32768, weights) FROM samples;

CREATE VIEW hll AS SELECT hyperloglog(32768, data) FROM samples;
```

--------------------------------

### Get First and Last Temperature with Aggregate Filter (SQL)

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/last

This SQL example shows the use of `first()` and `last()` aggregate functions with a `FILTER` clause to exclude null values. It calculates the average, beginning, and ending temperature within time buckets.

```sql
SELECT
   TIME_BUCKET('5 MIN', time_column) AS interv,
   AVG(temperature) as avg_temp,
   first(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS beg_temp,
   last(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS end_temp
FROM sensors
GROUP BY interv
```

--------------------------------

### Get Hypertable Dimension Info for Two Time Dimensions (SQL)

Source: https://www.tigerdata.com/docs/api/latest/informational-views/dimensions

Fetches dimension information for a hypertable specifically configured with two time-based dimensions. This example demonstrates how to query dimensions when a hypertable is partitioned using multiple time intervals. It returns metadata for each time dimension, including the interval and data types.

```sql
CREATE TABLE hyper_2dim (a_col date, b_col timestamp, c_col integer);
SELECT table_name from create_hypertable('hyper_2dim', by_range('a_col'));
SELECT add_dimension('hyper_2dim', by_range('b_col', INTERVAL '7 days'));


SELECT * FROM timescaledb_information.dimensions WHERE hypertable_name = 'hyper_2dim';
```

--------------------------------

### Create Hypertable with Columnstore

Source: https://www.tigerdata.com/docs/api/latest/hypercore/add_columnstore_policy

Initializes a new hypertable with columnstore capabilities enabled. The configuration includes segmentby and orderby parameters to optimize query performance.

```SQL
CREATE TABLE crypto_ticks (
   "time" TIMESTAMPTZ,
   symbol TEXT,
   price DOUBLE PRECISION,
   day_volume NUMERIC
) WITH (
  tsdb.hypertable,
  tsdb.segmentby='symbol',
  tsdb.orderby='time DESC'
);
```

--------------------------------

### Aggregate Data into TimeWeightSummary

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/time-weighted-calculations/time_weight

Shows the initial aggregation of raw data into a TimeWeightSummary object using the time_bucket and time_weight functions.

```SQL
WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        time_weight('Linear', ts, val) AS tw
    FROM foo
    WHERE measure_id = 10
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    average(tw)
FROM t;
```

--------------------------------

### Configure and Query Compression Settings in SQL

Source: https://www.tigerdata.com/docs/api/latest/informational-views/compression_settings

This SQL snippet demonstrates how to create a hypertable, set compression with segmentby and orderby clauses, and then query the 'compression_settings' view to verify these settings. It highlights the importance of 'segmentby' for compression effectiveness.

```sql
CREATE TABLE hypertab (a_col integer, b_col integer, c_col integer, d_col integer, e_col integer);
SELECT table_name FROM create_hypertable('hypertab', by_range('a_col', 864000000));


ALTER TABLE hypertab SET (timescaledb.compress, timescaledb.compress_segmentby = 'a_col,b_col',
  timescaledb.compress_orderby = 'c_col desc, d_col asc nulls last');


SELECT * FROM timescaledb_information.compression_settings WHERE hypertable_name = 'hypertab';
```

--------------------------------

### Reschedule Job to a Specific Next Start Time

Source: https://www.tigerdata.com/docs/api/latest/jobs-automation/alter_job

This SQL snippet illustrates how to set a precise next start time for a job using the alter_job function. It takes a job ID and a timestamp string as input for the next_start parameter.

```sql
SELECT alter_job(1000, next_start => '2020-03-15 09:00:00.0+00');
```

--------------------------------

### Configure Hash Partitioning for Hypertables

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_hypertable_old

This snippet demonstrates hash partitioning configuration for hypertables. It explains the use of `number_partitions` and the option to specify a custom partitioning function for types without a native Postgres hash function. The function should accept `anyelement` and return a positive integer hash value.

```sql
-- Example with default hash partitioning
-- CREATE HYPERTABLE my_table (id INT, ...)
-- DISTRIBUTE BY HASH (id, number_partitions => 4);

-- Example with a custom partitioning function
-- CREATE HYPERTABLE my_table (data TEXT, ...)
-- DISTRIBUTE BY HASH (data, number_partitions => 8, partitioning_func => my_custom_hash_func);
```

--------------------------------

### Configure Time Partitioning for Hypertables

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_hypertable_old

This snippet illustrates how to configure time partitioning for hypertables. It highlights the flexibility of the 'time' column, the use of `time_partitioning_func` for incompatible types, and the correct units for `chunk_time_interval` based on column type (timestamp, DATE, or integer).

```sql
-- Example for timestamp/DATE columns with interval
-- CREATE HYPERTABLE my_table (time TIMESTAMPTZ, ...)
-- DISTRIBUTE BY TIME (time, chunk_time_interval => INTERVAL '1 day');

-- Example for timestamp/DATE columns with microseconds
-- CREATE HYPERTABLE my_table (time TIMESTAMPTZ, ...)
-- DISTRIBUTE BY TIME (time, chunk_time_interval => 86400000000); -- 1 day in microseconds

-- Example for integer columns (milliseconds since epoch)
-- CREATE HYPERTABLE my_table (time BIGINT, ...)
-- DISTRIBUTE BY TIME (time, chunk_time_interval => 86400000); -- 1 day in milliseconds

-- Example with a function for incompatible types (e.g., jsonb)
-- CREATE HYPERTABLE my_table (time JSONB, ...)
-- DISTRIBUTE BY TIME (time, time_partitioning_func => my_jsonb_time_extractor_func, chunk_time_interval => 86400000);
```

--------------------------------

### Example Warning for Under-replicated Chunks (SQL)

Source: https://www.tigerdata.com/docs/api/latest/distributed-hypertables/set_replication_factor

This example shows a potential warning message from TimescaleDB when the set_replication_factor() function is called, and some existing chunks of the specified hypertable have fewer replicas than the new target replication factor. It indicates that the hypertable 'conditions' is under-replicated.

```sql
WARNING:  hypertable "conditions" is under-replicated
DETAIL:  Some chunks have less than 2 replicas.
```

--------------------------------

### GET /hypertable_size

Source: https://www.tigerdata.com/docs/api/latest

Retrieves the total disk space used by a specified hypertable.

```APIDOC
## GET /hypertable_size

### Description
Returns the total disk space usage for a given hypertable, including all its chunks.

### Method
GET

### Endpoint
/hypertable_size(hypertable_name regclass)

### Parameters
#### Query Parameters
- **hypertable_name** (regclass) - Required - The name of the hypertable to query.

### Request Example
SELECT * FROM hypertable_size('conditions');

### Response
#### Success Response (200)
- **table_size** (bigint) - Total size of the hypertable in bytes.

#### Response Example
{
  "table_size": 1048576
}
```

--------------------------------

### Example Error for Too Large Replication Factor (SQL)

Source: https://www.tigerdata.com/docs/api/latest/distributed-hypertables/set_replication_factor

This SQL example illustrates an error scenario where set_replication_factor() is called with a replication factor (3) that exceeds the number of attached data nodes (2) for the 'conditions' hypertable. The error message provides details on the discrepancy and suggests solutions.

```sql
SELECT set_replication_factor('conditions', 3);
ERROR:  too big replication factor for hypertable "conditions"
DETAIL:  The hypertable has 2 data nodes attached, while the replication factor is 3.
HINT:  Decrease the replication factor or attach more data nodes to the hypertable.
```

--------------------------------

### GET /intercept

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/statistical-and-regression-analysis/stats_agg-two-variables

Calculates the y-intercept of the least-squares fit line from a 2D statistical aggregate.

```APIDOC
## POST intercept

### Description
Calculate the y intercept from a two-dimensional statistical aggregate using standard least-squares fitting for linear regression.

### Method
POST

### Endpoint
intercept(summary StatsSummary2D)

### Parameters
#### Request Body
- **summary** (StatsSummary2D) - Required - The statistical aggregate produced by a stats_agg call.

### Request Example
SELECT intercept(stats_agg(y, x)) FROM foo;

### Response
#### Success Response (200)
- **intercept** (DOUBLE PRECISION) - The y intercept of the least-squares fit line.

#### Response Example
1.25
```

--------------------------------

### Create Hypertable with TimescaleDB Options (SQL)

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_table

This SQL syntax demonstrates how to create a new hypertable for time-series data using TimescaleDB options. It allows customization of partitioning, chunking, indexing, and data segmentation.

```sql
CREATE TABLE <table_name> (
   -- Standard Postgres syntax for CREATE TABLE  
) 
WITH (
   tsdb.hypertable = true | false,
   tsdb.partition_column = '<column_name>',
   tsdb.chunk_interval = '<interval>',
   tsdb.create_default_indexes =  true | false,
   tsdb.associated_schema = '<schema_name>',
   tsdb.associated_table_prefix = '<prefix>',
   tsdb.orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
   tsdb.segmentby = '<column_name> [, ...]',
   tsdb.sparse_index = '<index>(<column_name>), index(<column_name>)'
);
```

--------------------------------

### Create a hypertable with custom chunk interval

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_table

Initializes a hypertable with a specific chunk interval defined in the table options to control data partitioning granularity.

```sql
CREATE TABLE IF NOT EXISTS hypertable_control_chunk_interval(
 time int4 NOT NULL, 
 device text, 
 value float
) WITH (
 tsdb.hypertable,
 tsdb.chunk_interval=3453
);
```

--------------------------------

### GET /approx_percentile

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/percentile-approximation/uddsketch

Calculates the approximate value at a specific percentile from an existing UddSketch object.

```APIDOC
## GET /approx_percentile

### Description
Computes the estimated value at a given percentile from a previously generated UddSketch aggregate.

### Method
GET

### Endpoint
approx_percentile(percentile, uddsketch)

### Parameters
#### Query Parameters
- **percentile** (DOUBLE PRECISION) - Required - The percentile to compute (0.0 to 1.0).
- **uddsketch** (UddSketch) - Required - The aggregate object.

### Response
#### Success Response (200)
- **approx_percentile** (DOUBLE PRECISION) - The estimated value.

### Request Example
SELECT approx_percentile(0.01, uddsketch(data)) FROM generate_series(0, 100) data;
```

--------------------------------

### GET /num_vals

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/statistical-and-regression-analysis/stats_agg-two-variables

Calculates the total number of values contained within a 2D statistical aggregate.

```APIDOC
## POST num_vals

### Description
Calculate the number of values contained in a two-dimensional statistical aggregate.

### Method
POST

### Endpoint
num_vals(summary StatsSummary2D)

### Parameters
#### Request Body
- **summary** (StatsSummary2D) - Required - The statistical aggregate produced by a stats_agg call.

### Request Example
SELECT num_vals(stats_agg(y, x)) FROM foo;

### Response
#### Success Response (200)
- **num_vals** (BIGINT) - The number of values in the statistical aggregate.

#### Response Example
505
```

--------------------------------

### Create a hypertable partitioned by UUIDv7

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_table

Defines a hypertable using a UUIDv7 column as the partition key, which enables default compression settings.

```sql
CREATE TABLE events (
    id  uuid PRIMARY KEY DEFAULT generate_uuidv7(),
    payload jsonb
 ) WITH (tsdb.hypertable, tsdb.partition_column = 'id');
```

--------------------------------

### Interpolate Time in State with interpolated_duration_in() - SQL

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/state-tracking/compact_state_agg

The interpolated_duration_in function calculates the total duration in a given state, interpolating values across time bucket boundaries. It requires a StateAgg, the state, a start timestamp, and an interval. An optional previous StateAgg can be provided for interpolation at the start. The result is a DOUBLE PRECISION representing the duration.

```sql
SELECT 
  time,
  toolkit_experimental.interpolated_duration_in(
    agg,
    'running',
    time,
    '1 day',
    LAG(agg) OVER (ORDER BY time)
) FROM (
  SELECT
    time_bucket('1 day', time) as time,
    toolkit_experimental.compact_state_agg(time, state) as agg
  FROM
    states
  GROUP BY time_bucket('1 day', time)
) s;
```

--------------------------------

### Create a Hypertable with Columnstore

Source: https://www.tigerdata.com/docs/api/latest/hypercore

Defines a hypertable with columnstore enabled, specifying segmentation and ordering columns for optimized analytical queries. This configuration automatically triggers compression based on chunk intervals.

```sql
CREATE TABLE crypto_ticks (
  "time" TIMESTAMPTZ,
  symbol TEXT,
  price DOUBLE PRECISION,
  day_volume NUMERIC
) WITH (
  timescaledb.hypertable,
  timescaledb.segmentby='symbol', 
  timescaledb.orderby='time DESC'
);
```

--------------------------------

### GET /kurtosis

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/statistical-and-regression-analysis/stats_agg-two-variables

Calculates the kurtosis (fourth statistical moment) for either the x or y dimension of a statistical aggregate.

```APIDOC
## POST kurtosis_x / kurtosis_y

### Description
Calculate the kurtosis from a two-dimensional statistical aggregate for the given dimension. Measures the 'tailedness' of a data distribution.

### Method
POST

### Endpoint
kurtosis_y(summary StatsSummary2D, [method TEXT])

### Parameters
#### Request Body
- **summary** (StatsSummary2D) - Required - The statistical aggregate produced by a stats_agg call.
- **method** (TEXT) - Optional - 'population' or 'sample' (defaults to 'sample').

### Request Example
SELECT kurtosis_y(stats_agg(data, data)) FROM generate_series(0, 100) data;

### Response
#### Success Response (200)
- **kurtosis** (DOUBLE PRECISION) - The kurtosis of the values.

#### Response Example
1.78195
```

--------------------------------

### GET /determination_coeff

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/statistical-and-regression-analysis/stats_agg-two-variables

Calculates the determination coefficient (R-squared) from a 2D statistical aggregate using least-squares fitting.

```APIDOC
## POST determination_coeff

### Description
Calculates the determination coefficient from a two-dimensional statistical aggregate using standard least-squares fitting for linear regression.

### Method
POST

### Endpoint
determination_coeff(summary StatsSummary2D)

### Parameters
#### Request Body
- **summary** (StatsSummary2D) - Required - The statistical aggregate produced by a stats_agg call.

### Request Example
SELECT determination_coeff(stats_agg(y, x)) FROM foo;

### Response
#### Success Response (200)
- **determination_coeff** (DOUBLE PRECISION) - The determination coefficient of the least-squares fit line.

#### Response Example
0.985
```

--------------------------------

### Create Chunk with Space Partitioning - SQL

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_chunk

This SQL example demonstrates creating a chunk with space partitioning for hypertables that have additional space dimensions. It shows how to specify constraints for multiple dimensions, including 'time' and 'device', within the `slices` JSONB argument.

```sql
SELECT * FROM _timescaledb_functions.create_chunk(
    'conditions',
    '{"time": ["2018-01-22 00:00:00", "2018-01-29 00:00:00"], "device": [-9223372036854775808, 1073741823]}'
);
```

--------------------------------

### Retrieve approximate size of hypertables and aggregates using SQL

Source: https://www.tigerdata.com/docs/api/latest/hypertable/hypertable_approximate_size

Demonstrates how to query the approximate disk size for a specific hypertable, all hypertables in the database, and continuous aggregates using the hypertable_approximate_size function.

```sql
-- Get the approximate size information for a hypertable.
SELECT * FROM hypertable_approximate_size('devices');

-- Get the approximate size information for all hypertables.
SELECT hypertable_name, hypertable_approximate_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)
  FROM timescaledb_information.hypertables;

-- Get the approximate size information for a continuous aggregate.
SELECT hypertable_approximate_size('device_stats_15m');
```

--------------------------------

### GET /show_policies

Source: https://www.tigerdata.com/docs/api/latest/continuous-aggregates/add_continuous_aggregate_policy

Retrieves information about all policies currently set on a continuous aggregate.

```APIDOC
## GET /show_policies

### Description
Show all policies that are currently set on a continuous aggregate.

### Method
GET

### Endpoint
/show_policies

### Parameters
#### Query Parameters
- **continuous_aggregate** (string) - Required - The name of the continuous aggregate to query.

### Request Example
GET /show_policies?continuous_aggregate=cpu_summary

### Response
#### Success Response (200)
- **policies** (array) - List of active policies associated with the aggregate.

#### Response Example
{
  "policies": [
    {"type": "refresh", "job_id": 1001},
    {"type": "compression", "job_id": 1002}
  ]
}
```

--------------------------------

### into_array() Function Signature and Example - TimescaleDB

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/minimum-and-maximum/max_n

Provides the signature for the into_array() accessor function, which retrieves the N largest values from a MaxN aggregate as a sorted array. It takes a MaxN aggregate as input and returns an array of BIGINT, DOUBLE PRECISION, or TIMESTAMPTZ. An example demonstrates its use in finding the top 5 values.

```sql
SELECT into_array(
    max_n(sub.val, 5))
FROM (
  SELECT (i * 13) % 10007 AS val 
  FROM generate_series(1,10000) as i
) sub;
```

--------------------------------

### Configure Distributed Hypertables with Space Partitioning (SQL)

Source: https://www.tigerdata.com/docs/api/latest/hypertable/add_dimension_old

Illustrates the setup for distributed hypertables in a multi-node environment. It shows how to add data nodes, create a distributed hypertable partitioned by time, and then add a space partitioning dimension to enable parallelization across nodes.

```sql
SELECT add_data_node('dn1', host => 'dn1.example.com');
SELECT add_data_node('dn2', host => 'dn2.example.com');
SELECT create_distributed_hypertable('conditions', 'time');
SELECT add_dimension('conditions', 'location', number_partitions => 2);
```

--------------------------------

### POST /projects/{project_id}/services/{service_id}/start

Source: https://www.tigerdata.com/docs/api/latest/api-reference

Start a stopped service. This is an asynchronous operation.

```APIDOC
## POST /projects/{project_id}/services/{service_id}/start

### Description
Start a stopped service. This is an asynchronous operation.

### Method
POST

### Endpoint
/projects/{project_id}/services/{service_id}/start

### Parameters
#### Path Parameters
- **project_id** (string) - Required - The ID of the project.
- **service_id** (string) - Required - The ID of the service to start.

### Response
#### Success Response (202 Accepted)
Indicates that the start request has been accepted and the operation is in progress.

#### Response Example
```json
{
  "service_id": "p7zm9wqqii",
  "project_id": "jz22xtzemv",
  "name": "test-2",
  "region_code": "eu-central-1",
  "service_type": "TIMESCALEDB",
  "created": "2025-09-04T20:46:46.26568Z",
  "paused": false,
  "status": "RESUMING",
  "resources": [
      {
          "id": "100927",
          "spec": {
              "cpu_millis": 1000,
              "memory_gbs": 4,
              "volume_type": ""
          }
      }
  ],
  "endpoint": {
      "host": "p7zm8wqqii.jz4qxtzemv.tsdb.cloud.timescale.com",
      "port": 35482
  }
}
```
```

--------------------------------

### GET /projects/{project_id}/services/{service_id}

Source: https://www.tigerdata.com/docs/api/latest/api-reference

Retrieve details of a specific service within a project.

```APIDOC
## GET /projects/{project_id}/services/{service_id}

### Description
Retrieve details of a specific service.

### Method
GET

### Endpoint
/projects/{project_id}/services/{service_id}

### Parameters
#### Path Parameters
- **project_id** (string) - Required - The ID of the project.
- **service_id** (string) - Required - The ID of the service.

### Response
#### Success Response (200 OK)
- **service_id** (string) - The unique identifier for the service.
- **project_id** (string) - The ID of the project the service belongs to.
- **name** (string) - The name of the service.
- **region_code** (string) - The AWS region code where the service is deployed.
- **service_type** (string) - The type of the service (e.g., TIMESCALEDB).
- **created** (string) - The timestamp when the service was created.
- **paused** (boolean) - Indicates if the service is paused.
- **status** (string) - The current status of the service (e.g., READY, QUEUED, DELETING).
- **resources** (array) - A list of resources associated with the service.
  - **id** (string) - The ID of the resource.
  - **spec** (object) - The specifications of the resource.
    - **cpu_millis** (integer) - CPU allocation in milliseconds.
    - **memory_gbs** (integer) - Memory allocation in gigabytes.
    - **volume_type** (string) - The type of storage volume.
- **metadata** (object) - Additional metadata for the service.
  - **environment** (string) - The environment type (e.g., DEV, PROD).
- **endpoint** (object) - Connection endpoint details.
  - **host** (string) - The hostname of the service endpoint.
  - **port** (integer) - The port number of the service endpoint.

#### Response Example
```json
{
  "service_id": "p7zm9wqqii",
  "project_id": "jz22xtzemv",
  "name": "test-2",
  "region_code": "eu-central-1",
  "service_type": "TIMESCALEDB",
  "created": "2025-09-04T20:46:46.26568Z",
  "paused": false,
  "status": "READY",
  "resources": [
      {
          "id": "100927",
          "spec": {
              "cpu_millis": 1000,
              "memory_gbs": 4,
              "volume_type": ""
          }
      }
  ],
  "metadata": {
      "environment": "DEV"
  },
  "endpoint": {
      "host": "p7zm8wqqii.jz4qxtzemv.tsdb.cloud.timescale.com",
      "port": 35482
  }
}
```
```

--------------------------------

### Create a standard PostgreSQL relational table

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_table

Creates a standard relational table without TimescaleDB hypertable extensions.

```sql
CREATE TABLE IF NOT EXISTS relational_table(
 device text, 
 value float
);
```

--------------------------------

### Create HyperLogLog Aggregate with approx_count_distinct()

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/approximate-count-distinct/hyperloglog

A simplified alternative to hyperloglog() that uses default bucket parameters to create an intermediate aggregate. It is ideal for users who prefer sensible defaults over manual bucket configuration.

```SQL
SELECT toolkit_experimental.approx_count_distinct(weights) FROM samples;

CREATE VIEW hll AS SELECT toolkit_experimental.approx_count_distinct(data) FROM samples;
```

--------------------------------

### GET /projects/{project_id}/services

Source: https://www.tigerdata.com/docs/api/latest/api-reference

Retrieves a list of all services associated with a specific project ID.

```APIDOC
## GET /projects/{project_id}/services

### Description
Retrieve all services within a project.

### Method
GET

### Endpoint
/projects/{project_id}/services

### Parameters
#### Path Parameters
- **project_id** (string) - Required - The unique identifier of the project.

### Response
#### Success Response (200)
- **service_id** (string) - Unique service identifier
- **project_id** (string) - Associated project identifier
- **name** (string) - Service name
- **status** (string) - Current service status

#### Response Example
[
  {
    "service_id": "p7zm9wqqii",
    "project_id": "jz22xtzemv",
    "name": "my-production-db",
    "status": "READY"
  }
]
```

--------------------------------

### Convert table to hypertable with range partitioning

Source: https://www.tigerdata.com/docs/api/latest/hypertable/create_hypertable

Demonstrates various ways to convert a standard table into a hypertable using the by_range function, including setting chunk intervals and conditional execution.

```SQL
SELECT create_hypertable('conditions', by_range('time'));

-- With 24-hour interval
SELECT create_hypertable('conditions', by_range('time', 86400000000));
SELECT create_hypertable('conditions', by_range('time', INTERVAL '1 day'));

-- With if_not_exists check
SELECT create_hypertable('conditions', by_range('time'), if_not_exists => TRUE);
```

--------------------------------

### Configure continuous aggregate policies using add_policies

Source: https://www.tigerdata.com/docs/api/latest/continuous-aggregates/add_policies

Demonstrates how to apply refresh, compression, and data retention policies to a continuous aggregate named 'example_continuous_aggregate' using the SQL interface.

```SQL
SELECT timescaledb_experimental.add_policies(
    'example_continuous_aggregate',
    refresh_start_offset => '1 day'::interval,
    refresh_end_offset => '2 day'::interval,
    compress_after => '20 days'::interval,
    drop_after => '1 year'::interval
);
```

--------------------------------

### Get Volume from Candlestick (SQL)

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/financial-analysis/candlestick_agg

Retrieves the total trading volume from a candlestick aggregate. This function requires a 'Candlestick' object and returns the volume as a DOUBLE PRECISION.

```sql
volume(
    candlestick Candlestick
) RETURNS DOUBLE PRECISION
```

--------------------------------

### Get Open Time from Candlestick (SQL)

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/financial-analysis/candlestick_agg

Returns the timestamp corresponding to the open time of a candlestick aggregate. The function accepts a 'Candlestick' object and outputs a TIMESTAMPTZ.

```sql
open_time(
    candlestick Candlestick
) RETURNS TIMESTAMPTZ
```

--------------------------------

### Get Low Time from Candlestick (SQL)

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/financial-analysis/candlestick_agg

Retrieves the timestamp when the low price occurred from a candlestick aggregate. It requires a 'Candlestick' object as input and returns a TIMESTAMPTZ.

```sql
low_time(
    candlestick Candlestick
) RETURNS TIMESTAMPTZ
```

--------------------------------

### Create UddSketch Aggregate

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/percentile-approximation/uddsketch

Initializes a UddSketch aggregate object from raw data. Users can specify bucket size and maximum error, or use the convenience function percentile_agg for default settings.

```SQL
SELECT uddsketch(100, 0.01, data) FROM samples;
```

```SQL
CREATE MATERIALIZED VIEW foo_hourly
WITH (timescaledb.continuous)
AS SELECT
    time_bucket('1 h'::interval, ts) as bucket,
    percentile_agg(value) as pct_agg
FROM foo
GROUP BY 1;
```

--------------------------------

### Query time-series data with gapfilling

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/gapfilling/time_bucket_gapfill

Examples demonstrating how to use time_bucket_gapfill with and without the locf algorithm to process daily average metrics.

```SQL
SELECT time_bucket_gapfill('1 day', time) AS day,
    locf(avg(value)) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;
```

--------------------------------

### Combine Time-Weighted Aggregates with rollup()

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/time-weighted-calculations/time_weight

Demonstrates how to use the rollup() function to aggregate time-weighted data into larger time buckets. This is useful for reducing memory usage and performing multi-level aggregation on time-series data.

```SQL
WITH t as (SELECT measure_id,
    time_bucket('1 day'::interval, ts),
    time_weight('LOCF', ts, val)
    FROM foo
    GROUP BY measure_id, time_bucket('1 day'::interval, ts)
    )
SELECT measure_id,
    average(
        rollup(time_weight)
    )
FROM t
GROUP BY measure_id;
```

--------------------------------

### Hyperfunctions API

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/percentile-approximation/tdigest

Lists all hyperfunctions available in TimescaleDB and TimescaleDB Toolkit, including required arguments, return types, and usage examples.

```APIDOC
## Hyperfunctions API

### Description
Provides a comprehensive list of hyperfunctions available in TimescaleDB and TimescaleDB Toolkit. Includes required arguments, return types, and complete use examples.

### Method
GET

### Endpoint
/websites/tigerdata_api/hyperfunctions

### Parameters
#### Query Parameters
- **function_name** (string) - Optional - Filter by a specific hyperfunction name.

### Response
#### Success Response (200)
- **hyperfunctions** (array) - A list of hyperfunction objects, each containing details like name, arguments, returns, and examples.

#### Response Example
```json
{
  "hyperfunctions": [
    {
      "name": "example_hyperfunction",
      "arguments": "(timestamp, value)",
      "returns": "numeric",
      "example": "SELECT example_hyperfunction(time, value) FROM measurements;"
    }
  ]
}
```
```

--------------------------------

### Show all chunk compression settings (SQL)

Source: https://www.tigerdata.com/docs/api/latest/informational-views/chunk_compression_settings

This SQL query retrieves all available compression settings for every chunk that has compression enabled. It is useful for a comprehensive overview of chunk compression configurations across the database.

```sql
SELECT * FROM timescaledb_information.chunk_compression_settings
```

--------------------------------

### Extract first timestamp and value from CounterSummary

Source: https://www.tigerdata.com/docs/api/latest/hyperfunctions/counters-and-gauges/counter_agg

Functions to retrieve the initial timestamp or the initial value from a CounterSummary aggregate. These are useful for identifying the start of time-bucketed data series.

```SQL
WITH t as (
  SELECT
      time_bucket('1 day'::interval, ts) as dt,
      counter_agg(ts, val) AS cs
  FROM table
  GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    first_time(cs),
    last_time(cs)
FROM t;
```

```SQL
WITH t as (
  SELECT
      time_bucket('1 day'::interval, ts) as dt,
      counter_agg(ts, val) AS cs
  FROM table
  GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    first_val(cs),
    last_val(cs)
FROM t;
```

--------------------------------

### Add Columnstore Migration Policies

Source: https://www.tigerdata.com/docs/api/latest/hypercore/add_columnstore_policy

Configures policies to automatically move data chunks to columnstore storage based on time intervals or age. Supports various interval formats including days, months, and bigint time columns.

```SQL
CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '60d');
CALL add_columnstore_policy('crypto_ticks', created_before => INTERVAL '3 months');
CALL add_columnstore_policy('table_with_bigint_time', BIGINT '600000');
CALL add_columnstore_policy('cpu_weekly', INTERVAL '8 weeks');
```

--------------------------------

### Partition Data on Time using CREATE TABLE

Source: https://www.tigerdata.com/docs/api/latest/hypertable/add_dimension

This snippet demonstrates the simplest way to partition data on a time column using the CREATE TABLE statement. TimescaleDB automatically selects the first timestamp column as the partitioning column and applies a columnstore policy for optimized analytical workloads and storage. The columnstore policy automatically converts data to columnstore after a specified interval, enhancing query performance and reducing storage.

```sql
CREATE TABLE conditions (
   time        TIMESTAMPTZ       NOT NULL,
   location    TEXT              NOT NULL,
   device      TEXT              NOT NULL,
   temperature DOUBLE PRECISION  NULL,
   humidity    DOUBLE PRECISION  NULL
) WITH (
   tsdb.hypertable
);
```

--------------------------------

### Start Service

Source: https://www.tigerdata.com/docs/api/latest/api-reference

Initiates a stopped service. This is an asynchronous operation and returns 'Accepted' upon initiation. The service status will change to 'RESUMING'.

```http
POST /projects/{project_id}/services/{service_id}/start
```