### Setup Test Data for Examples

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/template.md

This SQL snippet sets the time zone to UTC and creates a sample table named 'example' with 'time' and 'value' columns. It is used to test various use cases and functionalities described in the document.

```SQL
SET TIME ZONE 'UTC';
CREATE TABLE example(time TIMESTAMPTZ, value DOUBLE PRECISION);
```

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

### Create and Insert Sample Data

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/asap.md

Sets the time zone to UTC, creates a 'metrics' table, and inserts sample data for demonstration purposes. This setup is required before using the asap_smooth function in examples.

```SQL
SET TIME ZONE 'UTC';
CREATE TABLE metrics(date TIMESTAMPTZ, reading DOUBLE PRECISION);
INSERT INTO metrics
SELECT
    '2020-1-1 UTC'::timestamptz + make_interval(hours=>foo),
    (5 + 5 * sin(foo / 12.0 * PI()))
    FROM generate_series(1,168) foo;
```

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

### Build Install Command

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/release.md

A recommended command for installation, potentially simplifying the process by abstracting the underlying steps.

```bash
tools/build install
```

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

### Build and install TimescaleDB Toolkit extension

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Readme.md

Builds and installs the TimescaleDB Toolkit extension in release mode. The second command runs a post-installation script to finalize the setup.

```bash
cargo pgrx install --release && \
cargo run --manifest-path ../tools/post-install/Cargo.toml -- pg_config
```

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

### SQL Doctester Usage Example

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tools/sql-doctester/Readme.md

This is a command-line usage example for the sql-doctester. It shows the basic command and available options for specifying database connection details and test paths.

```bash
sql-doctester
```

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

### Cargo Installation Command

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/release.md

Installs the extension using Cargo, placing it in the directory specified by `pg_config`. This is the first step in the installation process.

```bash
cargo pgrx install --release
```

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

### Run SQL Doctester Installation

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tools/sql-doctester/Readme.md

Install the sql-doctester tool using cargo. Ensure you are on the main branch for the latest features.

```bash
cargo install --git https://github.com/timescale/timescaledb-toolkit.git --branch main sql-doctester
```

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

### Create and Populate Example Table

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/time_weighted_average.md

Sets the time zone and creates a sample table 'foo' with time-series data, then inserts records for demonstration.

```SQL
SET TIME ZONE 'UTC';
 CREATE TABLE foo (
    measure_id      BIGINT,
    ts              TIMESTAMPTZ ,
    val             DOUBLE PRECISION,
    PRIMARY KEY (measure_id, ts)
);
INSERT INTO foo VALUES
( 1, '2020-01-01 00:00:00+00', 10.0),
( 1, '2020-01-01 00:01:00+00', 20.0),
( 1, '2020-01-01 00:02:00+00',10.0),
( 1, '2020-01-01 00:03:00+00', 20.0),
( 1, '2020-01-01 00:04:00+00', 15.0),
( 2, '2020-01-01 00:00:00+00', 10.0),
( 2, '2020-01-01 00:01:00+00', 20.0),
( 2, '2020-01-01 00:02:00+00',10.0),
( 2, '2020-01-01 00:03:00+00', 20.0),
( 2, '2020-01-01 00:04:00+00', 10.0),
( 2, '2020-01-01 00:08:00+00', 10.0),
( 2, '2020-01-01 00:10:00+00', 30.0),
( 2, '2020-01-01 00:10:30+00',10.0),
( 2, '2020-01-01 00:16:30+00', 35.0),
( 2, '2020-01-01 00:30:00+00', 60.0);
```

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

### Create and Populate Liveness Table

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tests/update/heartbeat.md

Sets up a table to store heartbeat and start timestamps, then populates it with sample data. Requires min-toolkit-version 1.15.0.

```sql
CREATE TABLE liveness(heartbeat TIMESTAMPTZ, start TIMESTAMPTZ);
INSERT INTO liveness VALUES
    ('01-01-2020 0:2:20 UTC', '01-01-2020 0:0 UTC'),
    ('01-01-2020 0:10 UTC', '01-01-2020 0:0 UTC'),
    ('01-01-2020 0:17 UTC', '01-01-2020 0:0 UTC'),
    ('01-01-2020 0:30 UTC', '01-01-2020 0:30 UTC'),
    ('01-01-2020 0:35 UTC', '01-01-2020 0:30 UTC'),
    ('01-01-2020 0:40 UTC', '01-01-2020 0:30 UTC'),
    ('01-01-2020 0:35 UTC', '01-01-2020 0:30 UTC'),
    ('01-01-2020 0:40 UTC', '01-01-2020 0:30 UTC'),
    ('01-01-2020 0:40 UTC', '01-01-2020 0:30 UTC'),
    ('01-01-2020 0:50:30 UTC', '01-01-2020 0:30 UTC'),
    ('01-01-2020 1:00:30 UTC', '01-01-2020 1:00 UTC'),
    ('01-01-2020 1:08 UTC', '01-01-2020 1:00 UTC'),
    ('01-01-2020 1:18 UTC', '01-01-2020 1:00 UTC'),
    ('01-01-2020 1:28 UTC', '01-01-2020 1:00 UTC'),
    ('01-01-2020 1:38:01 UTC', '01-01-2020 1:30 UTC'),
    ('01-01-2020 1:40 UTC', '01-01-2020 1:30 UTC'),
    ('01-01-2020 1:40:01 UTC', '01-01-2020 1:30 UTC'),
    ('01-01-2020 1:50:01 UTC', '01-01-2020 1:30 UTC'),
    ('01-01-2020 1:57 UTC', '01-01-2020 1:30 UTC'),
    ('01-01-2020 1:59:50 UTC', '01-01-2020 1:30 UTC');
```

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

### Install PostgreSQL development headers and build tools on Ubuntu

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Readme.md

Installs necessary build tools, PostgreSQL development headers, and OpenSSL for compiling the TimescaleDB Toolkit extension on Ubuntu systems.

```bash
sudo apt-get install make gcc pkg-config clang postgresql-server-dev-18 libssl-dev
```

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

### Basic SQL Validation Example

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tools/sql-doctester/Readme.md

This example demonstrates a simple SQL query and its expected output. The tool validates that the received output matches the expected output exactly.

```sql
SELECT count(v), sum(v), avg(v) FROM generate_series(1, 10) v;
```

```output
 count | sum |                avg
 ------+-----+--------------------
    10 |  55 | 5.5000000000000000
```

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

### Setup Hypertable

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/test_caggs.md

Configures the time zone to UTC and creates a hypertable named 'test' partitioned by the 'time' column.

```SQL
SET TIME ZONE 'UTC';
CREATE TABLE test(time TIMESTAMPTZ, value1 DOUBLE PRECISION, value2 DOUBLE PRECISION);
SELECT create_hypertable('test', 'time');
```

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

### Install Docker

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docker/README.md

Installs the Docker package on Debian-based systems.

```bash
apt-get install docker.io
```

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

### Time Series Pipeline Example

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Changelog.md

Demonstrates a time series pipeline for calculating daily changes, interpolating gaps, and resampling data. This example showcases the experimental pipeline API with sort, resample_to_rate, fill_holes, and delta functions.

```sql
SELECT timeseries(time, val)
    |> sort()
    |> resample_to_rate('trailing_average', '24 hours', true)
    |> fill_holes('interpolate')
    |> delta()
FROM ...
```

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

### Get State at Various Timestamps

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

Demonstrates retrieving the state at different timestamps using the state_at function. Includes examples for states within, at the boundary, before the first state, and after the last state.

```SQL
SELECT state_at(
    (SELECT state_agg(ts, state) FROM states_test),
    '2020-01-01 00:01:00+00'
);
```

```SQL
SELECT state_at(
    (SELECT state_agg(ts, state) FROM states_test),
    '2020-01-01 00:00:05+00'
);
```

```SQL
SELECT state_at(
    (SELECT state_agg(ts, state) FROM states_test),
    '2020-01-01 00:00:00+00'
);
```

```SQL
SELECT state_at(
    (SELECT state_agg(ts, state) FROM states_test),
    '2019-12-31 23:59:59.999999+00'
);
```

```SQL
SELECT state_at(
    (SELECT state_agg(ts, state) FROM states_test),
    '2025-01-01 00:00:00+00'
);
```

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

### Hyperloglog Usage Example

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/hyperloglog.md

Demonstrates how to use the hyperloglog function to create a digest over a column of DOUBLE PRECISION values.

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

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

### Counter Rollup Sample Usage

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/counter_agg.md

This example shows a two-level aggregation: first using `counter_agg` to get daily summaries, then using `rollup` to combine them into a full `CounterSummary`. It then calculates the delta for each day and its fraction relative to the total delta.

```SQL
WITH t as (
    SELECT
        date_trunc('day', ts) as dt,
        counter_agg(ts, val) AS counter_summary -- get a time weight summary
    FROM foo
    WHERE id = 'bar'
    GROUP BY date_trunc('day')
), q as (
    SELECT rollup(counter_summary) AS full_cs -- do a second level of aggregation to get the full CounterSummary
    FROM t
)
SELECT
    dt,
    delta(counter_summary),  -- extract the delta from the  CounterSummary
    delta(counter_summary) / (SELECT delta(full_cs) FROM q LIMIT 1)  as normalized -- get the fraction of the delta that happened each day compared to the full change of the counter
FROM t;
```

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

### SQL - Sample Usage of Distinct Count

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/hyperloglog.md

Shows a basic example of creating a HyperLogLog using the `hyperloglog` function and then calculating its distinct count.

```SQL
SELECT distinct_count(hyperloglog(64, data))
FROM generate_series(1, 100) data
```

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

### Install Rust using rustup

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Readme.md

Installs the Rust programming language and its package manager, Cargo, using the official rustup script. This is a prerequisite for building the TimescaleDB Toolkit extension.

```bash
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
```

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

### Setup Stocks Table

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/test_candlestick_agg.md

Creates a table to store real-time stock data including time, symbol, price, and volume. It then enables hypertable functionality for efficient time-series data management.

```SQL
SET TIME ZONE 'UTC';
CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  price DOUBLE PRECISION NULL,
  day_volume INT NULL
);
SELECT create_hypertable('stocks_real_time','time');
```

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

### Create and Populate Test Table

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/gauge_agg.md

Sets up a test table with time-series data for gauge aggregate examples. Ensures the time zone is set to UTC.

```SQL
SET TIME ZONE 'UTC';
CREATE TABLE gauge_test (
    measure_id      BIGINT,
    ts              TIMESTAMPTZ ,
    val             DOUBLE PRECISION,
    PRIMARY KEY (measure_id, ts)
);
INSERT INTO gauge_test SELECT 1, '2020-01-03 UTC'::timestamptz + make_interval(days=>v), v + 1000 FROM generate_series(1,10) v;
INSERT INTO gauge_test SELECT 2, '2020-01-03 UTC'::timestamptz + make_interval(days=>v), v + 2000 FROM generate_series(1,10) v;
INSERT INTO gauge_test SELECT 3, '2020-01-03 UTC'::timestamptz + make_interval(days=>v), v + 3000 FROM generate_series(1,10) v;
```

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

### Sample UddSketch Creation

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/uddsketch.md

Creates a sketch over a column of DOUBLE PRECISION values. This example demonstrates direct selection of the sketch.

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

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

### Calculate Total Duration of 'START' States

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

Aggregates state data into minute-long buckets and then uses `rollup` to calculate the total duration for states marked as 'START'.

```SQL
WITH buckets AS (SELECT
    date_trunc('minute', ts) as dt,
    toolkit_experimental.compact_state_agg(ts, state) AS sa
FROM states_test
GROUP BY date_trunc('minute', ts))
SELECT toolkit_experimental.duration_in(
    toolkit_experimental.rollup(buckets.sa),
    'START'
)
FROM buckets;
```

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

### Interpolate State Periods for 'START' State

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

Find periods where the state was 'START' over a 5-day interval. This demonstrates interpolating for a different specific string state.

```SQL
SELECT start_time, end_time FROM interpolated_state_periods(
    (SELECT state_agg(ts, state) FROM states_test),
    'START',
    '2019-12-31', '5 days',
    (SELECT state_agg(ts, state) FROM states_test_3)
)
ORDER BY start_time;
```

```output
start_time             | end_time
-----------------------+-----------------------
2019-12-31 00:00:00+00 | 2020-01-01 00:00:11+00
```

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

### Two-Step Aggregate Examples

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/two-step_aggregation.md

Demonstrates the two-step aggregation pattern using inner aggregate functions like time_weight and percentile_agg, followed by outer accessor functions like average and approx_percentile.

```SQL
SELECT average(time_weight('LOCF', value)) as time_weighted_average FROM foo;
-- or
SELECT approx_percentile(0.5, percentile_agg(value)) as median FROM bar;
```

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

### Timevector Function Pipeline Example

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Changelog.md

Demonstrates a sequence of analytic operations using the timevector function pipeline syntax. This provides a compact and readable way to perform multiple analytic steps.

```sql
timevector(ts, val) -> sort() -> delta() -> abs() -> sum()
```

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

### Stackable Aggregate Example: `sum`

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/two-step_aggregation.md

Illustrates the concept of stackable aggregates using the `sum` function. It shows how summing a pre-aggregated sum yields the same result as a single sum over the original data.

```sql
SELECT sum(val) FROM foo;
-- is equivalent to:
SELECT sum(sum)
FROM
    (SELECT id, sum(val)
    FROM foo
    GROUP BY id) s
```

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

### Time Weighting with rollup() for Aggregation

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/time_weighted_average.md

This example demonstrates how to use `time_weight` to generate time weight summaries and then `rollup` to perform a second level of aggregation for calculating normalized averages.

```SQL
WITH t as (
    SELECT
        date_trunc('day', ts) as dt,
        time_weight('Linear', ts, val) AS tw -- get a time weight summary
    FROM foo
    WHERE measure_id = 10
    GROUP BY date_trunc('day', ts)
), q as (
    SELECT rollup(tw) AS full_tw -- do a second level of aggregation to get the full time weighted average
    FROM t
)
SELECT
    dt,
    average(tw),  -- extract the average from the time weight summary
    average(tw) / (SELECT average(full_tw) FROM q LIMIT 1)  as normalized -- get the normalized average
FROM t;
```

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

### SQL Example: Calculate stderror for HyperLogLog

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/hyperloglog.md

This SQL example demonstrates how to calculate the standard error of a HyperLogLog. It generates a series of numbers, creates a HyperLogLog from them with 64 registers, and then calculates its standard error.

```SQL
SELECT stderror(hyperloglog(64, data))
FROM generate_series(1, 100) data
```

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

### Example of Non-Working SQL

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/template.md

This SQL snippet represents a query that is not functional due to incomplete implementation within the toolkit. It serves as a placeholder for demonstrating limitations or future development areas.

```SQL
[SQL that doesn't work because we didn't implement it]
```

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

### Initialize cargo-pgrx development environment

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Readme.md

Sets up the cargo-pgrx development environment, linking it to a specific PostgreSQL configuration. Replace 'pg_config' with the appropriate path for your PostgreSQL installation if necessary.

```bash
cargo pgrx init --pg18 pg_config
```

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

### Setup Test Tables for State Aggregation

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

Sets up and populates several test tables with timestamped state data. These tables are used to demonstrate and test state aggregation functions.

```SQL
SET TIME ZONE 'UTC';
CREATE TABLE states_test(ts TIMESTAMPTZ, state TEXT);
INSERT INTO states_test VALUES
    ('2020-01-01 00:00:00+00', 'START'),
    ('2020-01-01 00:00:11+00', 'OK'),
    ('2020-01-01 00:01:00+00', 'ERROR'),
    ('2020-01-01 00:01:03+00', 'OK'),
    ('2020-01-01 00:02:00+00', 'STOP');
CREATE TABLE states_test_2(ts TIMESTAMPTZ, state TEXT);
INSERT INTO states_test_2 VALUES
    ('2019-12-31 00:00:00+00', 'START'),
    ('2019-12-31 00:00:11+00', 'OK'),
    ('2019-12-31 00:02:00+00', 'STOP'),
    ('2019-12-31 00:01:03+00', 'OK');
CREATE TABLE states_test_3(ts TIMESTAMPTZ, state TEXT);
INSERT INTO states_test_3 VALUES
    ('2019-12-31 00:00:11+00', 'UNUSED'),
    ('2019-12-31 00:01:00+00', 'START');
CREATE TABLE states_test_4(ts TIMESTAMPTZ, state BIGINT);
INSERT INTO states_test_4 VALUES
    ('2020-01-01 00:00:00+00', 4),
    ('2020-01-01 00:00:11+00', 51351),
    ('2020-01-01 00:01:00+00', 2),
    ('2020-01-01 00:01:03+00', 51351),
    ('2020-01-01 00:02:00+00', -9);
CREATE TABLE states_test_5(ts TIMESTAMPTZ, state BIGINT); -- states_test with integer states
INSERT INTO states_test_5 VALUES
    ('2020-01-01 00:00:00+00', 4),
    ('2020-01-01 00:00:11+00', 51351),
    ('2020-01-01 00:01:00+00', 2),
    ('2020-01-01 00:02:03+00', 51351),
    ('2020-01-01 00:02:05+00', -9);
CREATE TABLE states_test_6(ts TIMESTAMPTZ, state BIGINT); -- states_test_3 with integer states
INSERT INTO states_test_6 VALUES
    ('2019-12-31 00:00:11+00', 456789),
    ('2019-12-31 00:01:00+00', 4);
```

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

### Non-Stackable Aggregate Example: `avg`

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/two-step_aggregation.md

Demonstrates why `avg` is a non-stackable aggregate. It shows that averaging a pre-aggregated average does not produce the same result as averaging the original data.

```sql
SELECT avg(val) FROM foo;
-- is NOT equivalent to:
SELECT avg(avg)
FROM
    (SELECT id, avg(val)
    FROM foo
    GROUP BY id) s;
```

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

### Interpolate State Periods with Integer State

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

This example shows how to interpolate periods for an integer state (4) using aggregated data from two different sources.

```SQL
SELECT start_time, end_time FROM interpolated_state_periods(
    (SELECT state_agg(ts, state) FROM states_test_5),
    4,
    '2019-12-31', '5 days',
    (SELECT state_agg(ts, state) FROM states_test_6)
)
ORDER BY start_time;
```

```output
start_time             | end_time
-----------------------+-----------------------
2019-12-31 00:00:00+00 | 2020-01-01 00:00:11+00
```

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

### Create TimescaleDB Hypertable

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/tdigest.md

Sets the time zone and creates a hypertable named 'test' with 'time' as the partitioning column. This is the initial setup for storing time-series data.

```SQL
SET TIME ZONE 'UTC';
CREATE TABLE test(time TIMESTAMPTZ, value DOUBLE PRECISION);
SELECT create_hypertable('test', 'time');
```

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

### SQL - Sample Usage of Rollup and Distinct Count

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/hyperloglog.md

Demonstrates how to use the `rollup` function to combine HyperLogLogs from different series and then use `distinct_count` to get the total distinct count.

```SQL
SELECT distinct_count(rollup(logs))
FROM (
    (SELECT hyperloglog(32, v::text) logs FROM generate_series(1, 100) v)
    UNION ALL
    (SELECT hyperloglog(32, v::text) FROM generate_series(50, 150) v)
) hll;
```

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

### Cargo Packaging Command

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/release.md

Packages the extension using Cargo, installing it into a directory under `$CARGO_TARGET_DIR`. This is an alternative first step for creating deb and rpm packages.

```bash
cargo pgrx package
```

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

### Two-Step Aggregate with CTE

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/two-step_aggregation.md

Shows how to achieve the same result as the previous example by first computing the aggregate in a Common Table Expression (CTE) and then applying accessor functions to the result. This explicitly separates the aggregation step from the accessor step.

```SQL
WITH pct as (SELECT percentile_agg(val) as approx FROM foo)
SELECT
    approx_percentile(0.1, approx) as p10,
    approx_percentile(0.5, approx) as p50,
    approx_percentile(0.9, approx) as p90
FROM pct;
```

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

### Clone TimescaleDB Toolkit repository

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Readme.md

Clones the TimescaleDB Toolkit repository from GitHub and navigates into the extension's subdirectory. This is the starting point for building the extension from source.

```bash
git clone https://github.com/timescale/timescaledb-toolkit && \
cd timescaledb-toolkit/extension
```

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

### Create TimescaleDB Toolkit extension in psql

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Readme.md

Initializes the TimescaleDB Toolkit extension within a PostgreSQL database using the psql client. This command must be run after the extension has been successfully installed.

```sql
CREATE EXTENSION timescaledb_toolkit;
```

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

### Counter Aggregation Sample Usage

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/counter_agg.md

This snippet demonstrates how to use `counter_agg` within a `GROUP BY` clause to get a `CounterSummary` for each time bucket, and then uses `irate_right` to extract the instantaneous rate.

```SQL
WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        counter_agg(ts, val) AS cs -- get a CounterSummary
    FROM foo
    WHERE id = 'bar'
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    irate_right(cs) -- extract instantaneous rate from the CounterSummary
FROM t;
```

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

### Interpolate State Periods with String State (Alternative Syntax)

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

This syntax achieves the same result as the previous example but uses a different method to call the function. It directly accesses the results of the interpolated_state_periods function.

```SQL
SELECT ((SELECT state_agg(ts, state) FROM states_test) -> interpolated_state_periods(
    'OK',
    '2019-12-31', '1 days',
    (SELECT state_agg(ts, state) FROM states_test_3)
)).*
ORDER BY start_time;
```

```output
       start_time       |        end_time
------------------------+------------------------
 2020-01-01 00:00:11+00 | 2020-01-01 00:01:00+00
 2020-01-01 00:01:03+00 | 2020-01-01 00:02:00+00
```

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

### Aggregate Builder Syntax Example

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/crates/aggregate_builder/Readme.md

This snippet demonstrates the basic structure and optional components for defining a custom aggregate function using the `#[aggregate]` macro. It includes the required `State` type, `transition` function, and `finally` function, along with optional `PARALLEL_SAFE` marker, `serialize`, `deserialize`, and `combine` functions.

```rust
#[aggregate] impl aggregate_name {
    type State = InternalTransitionType;

    fn transition(
        state: Option<State>,
        #[sql_type("sql_type")] argument: RustType, // can have an arbitrary number of args
    ) -> Option<State> {
        // transition function function body goes here
    }

    fn finally(state: Option<&mut State>) -> Option<ResultType> {
        // final function function body goes here
    }

    // the remaining items are optional

    // parallel-safety marker if desirable
    const PARALLEL_SAFE: bool = true;

    fn serialize(state: &State) -> bytea {
        // serialize function body goes here
    }

    fn deserialize(bytes: bytea) -> State {
        // deserialize function body goes here
    }

    fn combine(state1: Option<&State>, state2: Option<&State>) -> Option<State> {
        // combine function body goes here
    }
}
```

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

### Sort Timevector Pipeline Element Usage Example

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/timeseries_pipeline_elements.md

Demonstrates how to use the toolkit_experimental.sort() pipeline element to sort a timevector by time. The input timevector is generated using a series of dates and calculated values.

```SQL
SELECT time, value
FROM unnest(
    (SELECT timevector('2020-01-06'::timestamptz - step * '1 day'::interval, step * step)
        -> toolkit_experimental.sort()
    FROM generate_series(1, 5) step)
);
```

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

### Get State Periods with String State

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

Retrieves the start and end times of periods for a specific string state ('OK') from aggregated state data. Requires the state_agg function to pre-process the state data.

```SQL
SELECT start_time, end_time
FROM state_periods(
    (SELECT state_agg(ts, state) FROM states_test),
    'OK'
)
ORDER BY start_time;
```

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

### Get State Timeline from Aggregated States

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

Extracts the state timeline from aggregated state data. Use when you need to see the start and end times of each state. Requires the state_agg function to be applied first.

```SQL
SELECT (state_agg(ts, state) -> state_timeline()).* FROM states_test;
```

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

### Post-Installation Script

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/release.md

The `tools/post-install` script performs essential post-installation procedures, including finalizing control files, version-renaming shared objects, and generating update scripts. It requires stabilization information from `extension/src/stabilization_info.rs`.

```bash
tools/post-install
```

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

### Get State Periods with Numeric State

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

Retrieves the start and end times of periods for a specific numeric state (51351) from aggregated state data. This demonstrates the function's ability to handle different state data types.

```SQL
SELECT start_time, end_time
FROM state_periods(
    (SELECT state_agg(ts, state) FROM states_test_4),
    51351
)
ORDER BY start_time;
```

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

### Sample Usage of unnest Function

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/timeseries.md

Demonstrates how to use the unnest function to extract and display the first 10 (time, value) pairs from a generated timevector series. This example shows the practical application of unnesting timevector data.

```SQL
SELECT unnest(
    (SELECT timevector(a.time, a.value)
    FROM
        (SELECT time, value
        FROM toolkit_experimental.generate_periodic_normal_series('2020-01-01 UTC'::timestamptz, 45654)) 
        a)
    )
LIMIT 10;
```

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

### Install cargo-pgrx

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Readme.md

Installs cargo-pgrx, a tool for managing PostgreSQL extensions with Cargo. Ensure to use the specified version and flags for compatibility. Reinstall when updating the Rust compiler.

```bash
cargo install cargo-pgrx --version 0.18.0 --locked --force
```

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

### Create and Prepare Response Times Table

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/percentile_approximation.md

Sets up a 'response_times' table with timestamp, API ID, user ID, and response time, then converts it into a hypertable for efficient time-series data management.

```sql
CREATE TABLE response_times (
    ts timestamptz,
    api_id int,
    user_id int,
    response_time_ms float
);
-- and we'll make it a hypertable for ease of use in the rest of the example
SELECT create_hypertable('response_times', 'ts');
```

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

### Create Table and Hypertable

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

Sets up the 'email_status' table and converts it into a hypertable partitioned by timestamp. This is a prerequisite for using continuous aggregates.

```SQL
CREATE TABLE email_status (
  id BIGINT,
  ts TIMESTAMPTZ,
  status TEXT
);
SELECT create_hypertable('email_status','ts');

INSERT INTO email_status("ts", "id", "status")
VALUES
('2022-01-11 11:51:12',1,'draft'),
('2022-01-11 11:53:23',1,'queued'),
('2022-01-11 11:57:46',1,'sending'),
('2022-01-11 11:57:50',1,'sent'),
('2022-01-11 11:52:12',2,'draft'),
('2022-01-11 11:58:23',2,'queued'),
('2022-01-11 12:00:46',2,'sending'),
('2022-01-11 12:01:03',2,'bounced');

```

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

### SQL Doctester Options

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tools/sql-doctester/Readme.md

This section details the command-line options available for the sql-doctester, including flags for help and version, and options for database connection parameters and startup files/scripts.

```text
USAGE:
    sql-doctester [OPTIONS] <tests>

FLAGS:
        --help       Prints help information
    -V, --version    Prints version information

OPTIONS:
    -d, --database <database>
            postgres database the root connection should use. By default this DB will only be used
            to spawn the individual test databases; no tests will run against it.
    -h, --host <hostname>                    postgres host
    -a, --password <password>                postgres password
    -p, --port <portnumber>                  postgres port
    -f, --startup-file <startup_file>
            File containing SQL commands that should be run when each test database is created.

    -s, --startup-script <startup_script>
            SQL command that should be run when each test database is created.

    -u, --user <username>                    postgres user

ARGS:
    <tests>    Path in which to search for tests
```

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

### Install TimescaleDB Toolkit for a specific PostgreSQL version

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/Readme.md

Installs the TimescaleDB Toolkit extension for a PostgreSQL version other than the default. Use the --features flag to specify the desired PostgreSQL version (e.g., pg17).

```shell
cargo pgrx install --release --no-default-features --features pg17
```

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

### Create Table and Insert Data for TWA Tests

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tests/update/time-weighted-average.md

Sets up a table named 'time_weight_test' with timestamp, value, and bucket columns, and populates it with sample data for time-weighted average calculations. Requires toolkit version 1.15.0 or later.

```sql
CREATE TABLE time_weight_test(time timestamptz, value double precision, bucket timestamptz);
INSERT INTO time_weight_test VALUES
    ('2020-1-1 8:00'::timestamptz, 10.0, '2020-1-1'::timestamptz),
    ('2020-1-1 12:00'::timestamptz, 40.0, '2020-1-1'::timestamptz),
    ('2020-1-1 16:00'::timestamptz, 20.0, '2020-1-1'::timestamptz),
    ('2020-1-2 2:00'::timestamptz, 15.0, '2020-1-2'::timestamptz),
    ('2020-1-2 12:00'::timestamptz, 50.0, '2020-1-2'::timestamptz),
    ('2020-1-2 20:00'::timestamptz, 25.0, '2020-1-2'::timestamptz),
    ('2020-1-3 10:00'::timestamptz, 30.0, '2020-1-3'::timestamptz),
    ('2020-1-3 12:00'::timestamptz, 0.0, '2020-1-3'::timestamptz), 
    ('2020-1-3 16:00'::timestamptz, 35.0, '2020-1-3'::timestamptz);
CREATE MATERIALIZED VIEW twa AS (
    SELECT bucket, time_weight('linear', time, value) as agg 
    FROM time_weight_test 
    GROUP BY bucket
);
```

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

### Count Values in UddSketch

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/uddsketch.md

Get the number of values contained in a UddSketch. Use this to determine the cardinality of the data aggregated within the sketch.

```SQL
SELECT num_vals(
    uddsketch(100, 0.01, data)
) FROM generate_series(1, 100) data;
```

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

### Sample Output of Uptime Calculation

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tests/update/heartbeat.md

Displays the expected output format and values for the uptime and interpolated_uptime query, showing the calculated durations for each interval.

```text
start          |  uptime  | interpolated_uptime 
------------------------+----------+---------------------
 2020-01-01 00:00:00+00 | 00:24:40 | 00:24:40
 2020-01-01 00:30:00+00 | 00:29:30 | 00:29:30
 2020-01-01 01:00:00+00 | 00:29:30 | 00:30:00
 2020-01-01 01:30:00+00 | 00:21:59 | 00:29:59
```

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

### Create and Populate States Table

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tests/update/state_agg.md

Sets up a table to store state changes with timestamps and inserts sample data. This is a prerequisite for using the state_agg function.

```sql
CREATE TABLE states_test(ts TIMESTAMPTZ, state TEXT);
INSERT INTO states_test VALUES
    ('2020-01-01 00:00:00+00', 'START'),
    ('2020-01-01 00:00:11+00', 'OK'),
    ('2020-01-01 00:01:00+00', 'ERROR'),
    ('2020-01-01 00:01:03+00', 'OK'),
    ('2020-01-01 00:02:00+00', 'STOP');

CREATE TABLE agg(sa StateAgg);
INSERT INTO agg SELECT state_agg(ts, state) FROM states_test;
```

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

### Sample Usage of extrapolated_rate

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/counter_agg.md

Demonstrates how to use the extrapolated_rate function with counter_agg and with_bounds to calculate the per-second rate of change.

```SQL
SELECT
    id,
    bucket,
    extrapolated_rate(
        with_bounds(
            summary,
            time_bucket_range('15 min'::interval, bucket)
        )
    )
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        counter_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t
```

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

### Calculate Mean of UddSketch

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/uddsketch.md

Get the average of all values contained in a UddSketch. Use this when you need to find the mean of aggregated data stored in a sketch.

```SQL
SELECT mean(
    uddsketch(100, 0.01, data)
) FROM generate_series(1, 100) data;
```

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

### Create and Populate Temperature Table

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/timeseries_pipeline_elements.md

Sets the time zone and creates a table to store temperature readings from devices over time. Then, it populates the table with random data for 10,000 readings across 10 devices over 30 days.

```SQL
SET TIME ZONE 'UTC';
CREATE TABLE test_data(time TIMESTAMPTZ, device INTEGER, temperature DOUBLE PRECISION);
```

```SQL
INSERT INTO test_data
    SELECT
        '2020-01-01 00:00:00+00'::timestamptz + ((test_random() * 2592000)::int * '1 second'::interval),
        floor(test_random() * 10 + 1),
        50 + test_random() * 20
    FROM generate_series(1,10000);
```

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

### Get Minimum Value from TDigest

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/tdigest.md

Extracts the smallest value recorded in a TDigest. This is useful for finding the absolute minimum of a dataset that has been aggregated into a TDigest.

```SQL
SELECT min_val(tdigest(100, data))
FROM generate_series(1, 100) data;
```

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

### Load and Display Temperature Data

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/asap.md

This snippet shows how to create a table, load CSV data, and display the first 10 rows of temperature readings. Ensure the CSV file is accessible and correctly formatted.

```SQL
CREATE TABLE temperatures(month TIMESTAMPTZ, value DOUBLE PRECISION);
COPY temperatures from 'temperature.csv' CSV HEADER;
SELECT * FROM temperatures ORDER BY month LIMIT 10;
```

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

### Build Multi-Platform Docker Image

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docker/README.md

Builds a Docker image for multiple platforms and tags it for testing. Ensure DOCKER_BUILDKIT is set to 1.

```bash
ARCH=amd64
OS_NAME=debian
OS_VERSION=11
OS_CODE_NAME=bullseye
DOCKER_BUILDKIT=1 docker build \
                --platform $ARCH \
                --build-arg ARCH=$ARCH \
                --build-arg OS_NAME=$OS_NAME \
                --build-arg OS_VERSION=$OS_VERSION \
                --build-arg OS_CODE_NAME=$OS_CODE_NAME \
                -f docker/ci/Dockerfile \
                -t timescaledev/toolkit-builder-test:$OS_NAME-$OS_VERSION-$ARCH \
                .
```

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

### Sample Usage of average()

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/time_weighted_average.md

This snippet demonstrates how to use the average() function by first creating a TimeWeightSummary using time_weight() and then applying average() to it.

```SQL
SELECT
    id,
    average(tws)
FROM (
    SELECT
        id,
        time_weight('LOCF', ts, val) AS tws
    FROM foo
    GROUP BY id
) t
```

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

### Get Counter Zero Time

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/counter_agg.md

Calculates the time when the counter value is predicted to be zero using a least squares fit. Requires a CounterSummary object.

```SQL
counter_zero_time(
    summary CounterSummary
) RETURNS TIMESTAMPTZ
```

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

### Get Number of Values in Percentile Sketch

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/percentile_approximation.md

Use num_vals to retrieve the count of values from a percentile sketch. This is typically used after aggregating data with percentile_agg.

```SQL
SELECT num_vals(percentile_agg(data))
FROM generate_series(0, 100) data;
```

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

### Get Approximate Percentile with percentile_agg

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/percentile_approximation.md

Demonstrates how to use the `percentile_agg` function in conjunction with the `approx_percentile` accessor to retrieve an approximate percentile value from generated data.

```SQL
SELECT
    approx_percentile(0.01, percentile_agg(data))
FROM generate_series(0, 100) data;
```

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

### Create Table and Populate with Series

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/client.md

This SQL snippet creates a table named 'test' and populates it with a series of double-precision values.

```sql
CREATE TABLE test (data DOUBLE PRECISION);
INSERT INTO test SELECT generate_series(0.01, 1, 0.01);
```

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

### Get Maximum Relative Error of UddSketch

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/uddsketch.md

Returns the maximum relative error for percentile estimates from a UddSketch. This value can increase if the sketch has combined buckets.

```SQL
SELECT error(
    uddsketch(100, 0.01, data)
) FROM generate_series(1, 100) data;
```

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

### Sample Usage: counter_zero_time

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/counter_agg.md

Demonstrates how to use the counter_zero_time function with aggregated counter data. It groups data by time buckets and then applies the function to the generated summary.

```SQL
SELECT
    id,
    bucket,
    counter_zero_time(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        counter_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t
```

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

### Create View with Hyperloglog Digest

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/hyperloglog.md

Shows how to create a view that stores a hyperloglog digest, which can be used with other hyperloglog functions.

```SQL
CREATE VIEW digest AS SELECT hyperloglog(64, data) FROM samples;
```

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

### Create and Insert Data for Rollup

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tests/update/time-vector.md

Creates a table and inserts sample data with an additional bucket column for testing the rollup function. Handles NULL values and multiple entries per bucket.

```sql
CREATE TABLE tv_rollup_data(time TIMESTAMPTZ, value DOUBLE PRECISION, bucket INTEGER);
INSERT INTO tv_rollup_data VALUES
    ('2020-1-1 UTC', 30.0, 1),
    ('2020-1-2 UTC', 45.0, 1),
    ('2020-1-3 UTC', NULL, 2),
    ('2020-1-4 UTC', 55.5, 2),
    ('2020-1-5 UTC', 10.0, 3),
    ('2020-1-6 UTC', 13.0, 3),
    ('2020-1-7 UTC', 71.0, 4),
    ('2020-1-8 UTC', 0.0, 4);
```

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

### Get State Periods with 'ANYTHING' State

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/state_agg.md

Demonstrates retrieving state periods when the specified state ('ANYTHING') does not result in any matching periods. This returns an empty set.

```SQL
SELECT start_time, end_time
FROM state_periods(
    (SELECT state_agg(ts, state) FROM states_test),
    'ANYTHING'
)
ORDER BY start_time;
```

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

### unnest

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/timeseries.md

The unnest function is used to get the (time, value) pairs back out of a timevector object. It takes a timevector as input and returns a table of time-stamped values.

```APIDOC
## unnest

### Description
Extracts (time, value) pairs from a timevector object.

### Function Signature
```sql
unnest(series timevector) RETURNS TABLE("time" timestamp with time zone, value double precision)
```

### Parameters
#### Required Arguments
- **series** (timevector) - The series to return the data from.

### Returns
- **unnest** (TABLE) - The (time,value) records contained in the timevector.

### Sample Usage
```sql
SELECT unnest(
    (SELECT timevector(a.time, a.value)
    FROM
        (SELECT time, value
        FROM toolkit_experimental.generate_periodic_normal_series('2020-01-01 UTC'::timestamptz, 45654))
        a)
    )
LIMIT 10;
```
```

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

### Skip SQL Test Execution

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/tools/sql-doctester/Readme.md

Add the `ignore` tag after `SQL` to completely skip a SQL code block. This is useful for examples that are not meant to be run or are syntactically incorrect.

```sql
This never runs, so it doesn't matter if it's valid SQL
```

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

### Re-aggregating Percentiles with Rollup

Source: https://github.com/timescale/timescaledb-toolkit/blob/main/docs/percentile_approximation.md

Demonstrates how to use the rollup function to re-aggregate percentile data from a continuous aggregate view. This example calculates the 95th and 99th percentiles over daily buckets.

```SQL
SELECT
    time_bucket('1 day'::interval, bucket) as bucket,
    approx_percentile(0.95, rollup(pct_agg)) as p95,
    approx_percentile(0.99, rollup(pct_agg)) as p99
FROM foo_hourly
GROUP BY 1;
```