### Install and Start PostgreSQL 17

Source: https://github.com/timescale/docs/blob/latest/_partials/_install-self-hosted-homebrew-based.mdx

Installs PostgreSQL version 17, cleans up old prefixes, and starts the PostgreSQL service.

```bash
brew install postgresql@17
brew cleanup --prune-prefix
brew services start postgresql@17
```

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

### Install timescaledb-parallel-copy

Source: https://github.com/timescale/docs/blob/latest/_partials/_migrate_using_parallel_copy.md

Install the timescaledb-parallel-copy tool using the go get command. This is a prerequisite for using the tool to import data.

```bash
go get github.com/timescale/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy
```

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

### Example output of pg_available_extensions

Source: https://github.com/timescale/docs/blob/latest/use-timescale/hyperfunctions/troubleshoot-hyperfunctions.md

Illustrates the expected output when checking for the timescaledb_toolkit extension, showing its default and installed versions. This helps confirm successful installation or identify version discrepancies.

```sql
name              | timescaledb_toolkit
default_version   | 1.6.0
installed_version | 1.6.0
comment           | Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities
```

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

### Example: Estimate Gauge Start Time

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/gauge_agg/gauge_zero_time.md

This example demonstrates how to use gauge_zero_time() to estimate the time when a gauge started, by first aggregating gauge data using gauge_agg() within time buckets.

```sql
SELECT
    id,
    bucket,
    gauge_zero_time(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t
```

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

### Run PostgreSQL Package Setup Script

Source: https://github.com/timescale/docs/blob/latest/_partials/_install-self-hosted-debian-based-start.md

Executes the script to set up the official PostgreSQL APT repository, ensuring you can install the latest versions.

```bash
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
```

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

### List Installed Extensions

Source: https://github.com/timescale/docs/blob/latest/use-timescale/extensions/postgis.md

Confirms if the PostGIS extension is installed and shows its version. This is a command-line output example.

```sql
                                         List of installed extensions
    Name         | Version |   Schema   |                                      Description                                      
---------------------+---------+------------+---------------------------------------------------------------------------------------
 pg_stat_statements  | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 pgcrypto            | 1.3     | public     | cryptographic functions
 plpgsql             | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis             | 3.3.3   | public     | PostGIS geometry and geography spatial types and functions
 timescaledb         | 2.24.0  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
 timescaledb_toolkit | 1.22.0  | public     | Library of analytical hyperfunctions,     time-series pipelining, and other SQL utilities
(6 rows)
```

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

### Show average daily temperature results

Source: https://github.com/timescale/docs/blob/latest/use-timescale/time-buckets/use-time-buckets.md

Example output of the previous query, displaying the start time of each daily bucket and the corresponding average temperature.

```text
bucket                 |      avg_temp
-----------------------+---------------------
2016-11-15 00:00:00+00 | 68.3704391666665821
2016-11-16 00:00:00+00 | 67.0816684374999347
```

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

### Install psql on Windows

Source: https://github.com/timescale/docs/blob/latest/integrations/psql.md

Install psql on Windows by running the PostgreSQL installer from EnterpriseDB. Ensure the 'Command Line Tools' component is selected during installation.

```bash
Download and run the $PG installer from [www.enterprisedb.com][windows-installer].
 
1. In the `Select Components` dialog, check `Command Line Tools`, along with any other components you want to install, and click `Next`.

1. Complete the installation wizard to install the package.
```

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

### Enable and Start PostgreSQL Service

Source: https://github.com/timescale/docs/blob/latest/_partials/_install-self-hosted-archlinux-based.md

Enables the PostgreSQL service to start on boot and starts the service immediately.

```bash
sudo systemctl enable postgresql.service
sudo systemctl start postgresql.service
```

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

### Example: Get state at a specific time

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/state_agg/state_at.md

Demonstrates how to create a state aggregate from sample data and then use state_at() to retrieve the state at a specific timestamp.

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

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

### Install Homebrew

Source: https://github.com/timescale/docs/blob/latest/_partials/_install-self-hosted-homebrew-based.mdx

Run this command if Homebrew is not already installed on your machine.

```bash
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
```

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

### Install TimescaleDB and PostgreSQL Client Libraries

Source: https://github.com/timescale/docs/blob/latest/_partials/_install-self-hosted-macports-based.mdx

Use this command to install the TimescaleDB and libpqxx packages via MacPorts. Ensure MacPorts is installed and updated before running.

```bash
sudo port install timescaledb libpqxx
```

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

### Install timescaledb-tune using Go

Source: https://github.com/timescale/docs/blob/latest/self-hosted/configuration/timescaledb-tune.md

Install the timescaledb-tune tool using the Go package manager. This is an alternative to installing it as part of a TimescaleDB binary release.

```bash
go install github.com/timescale/timescaledb-tune/cmd/timescaledb-tune@latest
```

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

### SQL Example for irate_left()

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/gauge_agg/irate_left.md

This example demonstrates how to get the instantaneous rate of change at the start of each 15-minute gauge aggregate using irate_left().

```sql
SELECT
    id,
    bucket,
    irate_left(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t
```

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

### Example PostgreSQL Configuration File Path

Source: https://github.com/timescale/docs/blob/latest/self-hosted/configuration/postgres-config.md

An example output showing the path to the PostgreSQL configuration file.

```text
/home/postgres/pgdata/data/postgresql.conf
```

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

### SQL Example: Get Instantaneous Rate of Change

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/counter_agg/irate_left.md

This example demonstrates how to use irate_left() to calculate the instantaneous rate of change at the start of each 15-minute counter aggregate. It first aggregates counter data using counter_agg() and then applies irate_left() to the resulting summary.

```sql
SELECT
    id,
    bucket,
    irate_left(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
```

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

### Install MCP Client

Source: https://github.com/timescale/docs/blob/latest/_partials/_devops-mcp-commands-cli.md

Installs and configures the MCP short for a specific client. Use `--no-backup` to skip backing up the existing configuration or `--config-path` to specify a configuration file location.

```bash
tiger mcp install `[client]`
```

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

### Download Environment Sample

Source: https://github.com/timescale/docs/blob/latest/ai/tiger-agents-for-work.md

Download the sample environment file to configure your agent's settings.

```bash
curl -L -o .env https://raw.githubusercontent.com/timescale/tiger-agent/refs/heads/main/.env.sample
```

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

### Bootstrap Build System (Linux)

Source: https://github.com/timescale/docs/blob/latest/_partials/_install-self-hosted-source-based.mdx

Initialize the build system for TimescaleDB on Linux.

```bash
./bootstrap
```

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

### Calculate time in 'running' state with interpolation

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/state_agg/interpolated_duration_in.md

This example demonstrates how to calculate the total time spent in the 'running' state using `interpolated_duration_in`. It utilizes `LAG` to provide the previous state aggregate for interpolation at the start of each interval. The results can be further processed, for instance, by extracting the epoch to get the duration in seconds.

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

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

### Copy MCP Config Example

Source: https://github.com/timescale/docs/blob/latest/ai/tiger-agents-for-work.md

Copy the example MCP configuration file to your project directory.

```bash
curl -L -o mcp_config.json https://raw.githubusercontent.com/timescale/tiger-agent/refs/heads/main/examples/mcp_config.json
```

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

### Example: Create a Grafana Fork

Source: https://github.com/timescale/docs/blob/latest/mst/aiven-client.md

Example demonstrating the creation of a fork named 'grafana-fork' for a 'grafana' service with specified project, cloud, and plan parameters.

```bash
avn service create grafana-fork --project project-fork -t grafana --plan dashboard-1 --cloud timescale-aws-us-east-1  -c service_to_fork_from=grafana
```

```bash
avn service list
```

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

### Get Service Response Example

Source: https://github.com/timescale/docs/blob/latest/api/api-reference.md

Example JSON response when retrieving details for a specific service. Includes configuration and status information.

```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 chunk with space partitioning

Source: https://github.com/timescale/docs/blob/latest/api/hypertable/create_chunk.md

This advanced example shows how to create a chunk with space partitioning by specifying constraints for additional space dimensions. All dimension constraints must be provided.

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

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

### Bootstrap Build System (Windows)

Source: https://github.com/timescale/docs/blob/latest/_partials/_install-self-hosted-source-based.mdx

Initialize the build system for TimescaleDB on Windows. Ensure pg_config and cmake are in your system's PATH.

```powershell
bootstrap.bat
```

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

### Install python-dotenv

Source: https://github.com/timescale/docs/blob/latest/ai/python-interface-for-pgvector-and-timescale-vector.md

Install the python-dotenv package using pip. This library is used in examples to manage secrets and keys via environment variables.

```bash
pip install python-dotenv
```

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

### Initialize PostgreSQL Instance

Source: https://github.com/timescale/docs/blob/latest/_partials/_install-self-hosted-redhat-rocky.md

Initializes the PostgreSQL data directory for a new instance.

```bash
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
```

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

### Start Kafka Connect in Standalone Mode

Source: https://github.com/timescale/docs/blob/latest/integrations/apache-kafka.md

Starts the Kafka Connect service in standalone mode, making the installed connectors available. The CLASSPATH is set to include the connector plugins.

```bash
export CLASSPATH=`pwd`/plugins/camel-postgresql-sink-kafka-connector/*
./bin/connect-standalone.sh config/connect-standalone.properties
```

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

### Example: Create Specific Read-Only Replica

Source: https://github.com/timescale/docs/blob/latest/mst/aiven-client/replicas-cli.md

An example demonstrating the creation of a read-only replica with specific parameters including project ID, plan type, cloud, and the service to fork from.

```bash
avn service create replica-fork --project fork-project\
-t pg --plan timescale-basic-100-compute-optimized\
--cloud timescale-aws-us-east-1 -c pg_read_replica=true\
-c service_to_fork_from=timescaledb -c\
pg_version=11 -c variant=timescale
```

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

### Download and Install timescaledb-backfill

Source: https://github.com/timescale/docs/blob/latest/migrate/dual-write-and-backfill/dual-write-from-timescaledb.md

Download and install the timescaledb-backfill tool on your instance. Ensure the instance is located close to the target database for optimal performance.

```bash
wget https://assets.timescale.com/releases/timescaledb-backfill-x86_64-linux.tar.gz
tar xf timescaledb-backfill-x86_64-linux.tar.gz
sudo mv timescaledb-backfill /usr/local/bin/
```

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

### SQL Example: Get Rate of Change per ID

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/gauge_agg/rate.md

This example demonstrates how to calculate the rate of change for each 'id' over the entire recorded interval using the rate() function in conjunction with gauge_agg().

```sql
SELECT
    id,
    rate(summary)
FROM (
    SELECT
        id,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id
) t
```

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

### Shifting Week Start to Sunday

Source: https://github.com/timescale/docs/blob/latest/api/time_bucket.md

Bucket data by week, shifting the alignment to start on a Sunday instead of the default Monday. This example uses a specific origin date to define the bucket 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;
```

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

### Example SQL/Bash Command Block

Source: https://github.com/timescale/docs/blob/latest/tutorials/_template/_dataset-tutorial.md

Use this block to include any commands relevant to setting up the dataset. Specify the language and ensure commands are broken appropriately. Do not include prompts or sudo commands.

```sql
Add any commands in a block like this.
Make sure you specify the language.
Do not include any prompts, or sudo commands. If root is required \ state it in the step instead. Break long lines appropriately.
```

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

### Install and Configure TimescaleDB on PostgreSQL

Source: https://github.com/timescale/docs/blob/latest/self-hosted/install/installation-source.md

This section guides through the process of installing the latest version of PostgreSQL and TimescaleDB from source. It assumes you have the necessary build tools and a supported PostgreSQL version already set up.

```bash
# Clone the TimescaleDB repository
git clone https://github.com/timescale/timescaledb.git
cd timescaledb

# Checkout the desired version (e.g., v2.10.0)
git checkout v2.10.0

# Configure the build (adjust paths as needed)
# Ensure PG_CONFIG points to your PostgreSQL installation's pg_config executable
# Example for Linux/macOS:
export PG_CONFIG=/usr/pgsql-15/bin/pg_config
./configure --with-pgconfig=$PG_CONFIG

# Build TimescaleDB
make

# Install TimescaleDB
sudo make install

# After installation, you may need to restart your PostgreSQL server
# and configure postgresql.conf to load the TimescaleDB extension.
# Example postgresql.conf settings:
# shared_preload_libraries = 'timescaledb'
# timescaledb.max_background_workers = 10
# timescaledb.max_shards_per_index = 1024

# Restart PostgreSQL service (command varies by OS and installation method)
# Example for systemd:
sudo systemctl restart postgresql

```

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

### MCP Server JSON Configuration

Source: https://github.com/timescale/docs/blob/latest/ai/mcp-server.md

This JSON configuration defines an MCP server named 'tiger' that can be started using the 'tiger mcp start' command. Use this when your MCP client requires manual server setup.

```json
{
   "mcpServers": {
      "tiger": {
         "command": "tiger",
         "args": [
            "mcp",
            "start"
         ]
      }
   }
}
```

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

### Estimate the time when the counter started

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/counter_agg/counter_zero_time.md

This example demonstrates how to use counter_zero_time() to estimate the start time of a counter. It first aggregates counter data using counter_agg() within time buckets and then applies counter_zero_time() to each 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
```

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

### Install libpq with Homebrew

Source: https://github.com/timescale/docs/blob/latest/_partials/_psql-installation-homebrew.md

Installs the libpq package, which includes the psql command-line tool.

```bash
brew install libpq
```

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

### Connect to TimescaleDB in Java

Source: https://github.com/timescale/docs/blob/latest/getting-started/start-coding-with-timescale.md

Connect to your TimescaleDB service using Java. This example shows the basic connection setup.

```java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class App {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://your_host:5432/your_database";
        String user = "your_user";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT version()");
            rs.next();
            System.out.println(rs.getString(1));
        }
    }
}
```

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

### Successful Connection Output

Source: https://github.com/timescale/docs/blob/latest/_partials/_start-coding-node.md

Example console output indicating a successful Express application start and Sequelize database connection.

```bash
Example app listening at http://localhost:3000
Executing (default): SELECT 1+1 AS result
Connection has been established successfully.
```

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

### Combine State Aggregates and Calculate Duration in START State

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/state_agg/rollup.md

This example demonstrates how to use rollup() to combine state aggregates from 1-minute buckets into a single aggregate, and then calculate the total duration spent in the 'START' state within those combined buckets.

```sql
WITH buckets AS (
    SELECT
        time_bucket('1 minute', ts) as dt,
        state_agg(ts, state) AS sa
    FROM states_test
    GROUP BY time_bucket('1 minute', ts))
SELECT duration_in(
    'START',
    rollup(buckets.sa)
)
FROM buckets;
```

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

### Create Service Response Example

Source: https://github.com/timescale/docs/blob/latest/api/api-reference.md

Example JSON response after successfully initiating the creation of a service. Includes service details and an initial password.

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

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

### SQL - Get first timestamp from TimeWeightSummary

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/time_weight/first_time.md

This example demonstrates how to create a TimeWeightSummary using time_weight() and then extract the first timestamp from it using first_time().

```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;
```

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

### Get All Continuous Aggregate Classes

Source: https://github.com/timescale/docs/blob/latest/_partials/_start-coding-ruby.md

Use this method to retrieve all defined continuous aggregate classes. This is useful for inspecting your continuous aggregate setup.

```ruby
PageLoad.descendants # Get all continuous aggregate classes
```

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

### Initialize PostgreSQL Instance

Source: https://github.com/timescale/docs/blob/latest/_partials/_install-self-hosted-archlinux-based.md

Initializes a new PostgreSQL instance with specified locale, encoding, and data directory.

```bash
sudo -u postgres initdb --locale=en_US.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums
```

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

### SQL - Calculate Instantaneous Change at Gauge Aggregate Start

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/gauge_agg/idelta_left.md

This example demonstrates how to calculate the instantaneous change at the start of each 15-minute gauge aggregate. It first aggregates time-series data into 15-minute buckets using `gauge_agg` and then applies `idelta_left` to find the initial delta for each bucket.

```sql
SELECT
    id,
    bucket,
    idelta_left(summary)
FROM (
    SELECT
        id,
        time_bucket('15 min'::interval, ts) AS bucket,
        gauge_agg(ts, val) AS summary
    FROM foo
    GROUP BY id, time_bucket('15 min'::interval, ts)
) t
```

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

### Full Go Example: Connect, Generate, and Insert Data

Source: https://github.com/timescale/docs/blob/latest/_partials/_start-coding-golang.md

A complete Go program that connects to a database, generates sample sensor data using SQL, scans it into a slice, and then inserts it into the 'sensor_data' hypertable.

```go
package main

import (
    "context"
    "fmt"
    "os"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    /********************************************/
    /* Connect using Connection Pool            */
    /********************************************/
    ctx := context.Background()
    connStr := "yourConnectionStringHere"
    dbpool, err := pgxpool.New(ctx, connStr)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }
    defer dbpool.Close()

    /********************************************/
    /* Insert data into hypertable              */
    /********************************************/
    // Generate data to insert

    //SQL query to generate sample data
    queryDataGeneration := `
        SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
        floor(random() * (3) + 1)::int as sensor_id,
        random()*100 AS temperature,
        random() AS cpu
        `
    //Execute query to generate samples for sensor_data hypertable
    rows, err := dbpool.Query(ctx, queryDataGeneration)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to generate sensor data: %v\n", err)
        os.Exit(1)
    }
    defer rows.Close()

    fmt.Println("Successfully generated sensor data")

    //Store data generated in slice results
    type result struct {
        Time        time.Time
        SensorId    int
        Temperature float64
        CPU         float64
    }
    var results []result
    for rows.Next() {
        var r result
        err = rows.Scan(&r.Time, &r.SensorId, &r.Temperature, &r.CPU)
        if err != nil {
            fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
            os.Exit(1)
        }
        results = append(results, r)
    }
    // Any errors encountered by rows.Next or rows.Scan are returned here
    if rows.Err() != nil {
        fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
        os.Exit(1)
    }

    // Check contents of results slice
    fmt.Println("Contents of RESULTS slice")
    for i := range results {
        var r result
        r = results[i]
        fmt.Printf("Time: %s | ID: %d | Temperature: %f | CPU: %f |\n", &r.Time, r.SensorId, r.Temperature, r.CPU)
    }

    //Insert contents of results slice into TimescaleDB
    //SQL query to generate sample data
    queryInsertTimeseriesData := `
        INSERT INTO sensor_data (time, sensor_id, temperature, cpu) VALUES ($1, $2, $3, $4);
        `

    //Insert contents of results slice into TimescaleDB
    for i := range results {
        var r result
        r = results[i]
        _, err := dbpool.Exec(ctx, queryInsertTimeseriesData, r.Time, r.SensorId, r.Temperature, r.CPU)
        if err != nil {
            fmt.Fprintf(os.Stderr, "Unable to insert sample into TimescaleDB %v\n", err)
            os.Exit(1)
        }
        defer rows.Close()
    }
    fmt.Println("Successfully inserted samples into sensor_data hypertable")
}
```

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

### Create Table on All Data Nodes

Source: https://github.com/timescale/docs/blob/latest/api/distributed-hypertables/distributed_exec.md

This example demonstrates creating a table structure on every data node. Ensure the table definition is compatible with all nodes.

```sql
CALL distributed_exec($$ CREATE TABLE example (ts TIMESTAMPTZ, value INTEGER) $$);
```

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

### Connect using psql Command Line

Source: https://github.com/timescale/docs/blob/latest/_partials/_cloud-connect-service.md

Connect to your service using the psql command-line tool. Ensure you have psql installed and use the service URL from your configuration file.

```bash
psql "<your-service-url>"
```

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

### Drop Chunks Before a Specific Timestamp (Milliseconds)

Source: https://github.com/timescale/docs/blob/latest/api/hypertable/drop_chunks.md

Use this when the time column is stored in milliseconds from the UNIX epoch. This example drops chunks from the 'conditions' hypertable before the start of 2017.

```sql
SELECT drop_chunks('conditions', 1483228800000);
```

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

### Run a Java Application from the Command Line

Source: https://github.com/timescale/docs/blob/latest/_partials/_start-coding-java.md

Demonstrates how to execute a Java file directly from the command line using the `java` command. This is useful for quick testing and running single-file applications.

```bash
java Main.java
```

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

### SQL - Get Last Value from TimeWeightSummary

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/time_weight/last_val.md

This example demonstrates how to use last_val() by first creating a TimeWeightSummary using time_weight() with a 'Linear' method and then extracting the last value from it.

```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,
    last_val(tw)
FROM t;
```

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

### Create a Basic Java 'Hello, World!' Application

Source: https://github.com/timescale/docs/blob/latest/_partials/_start-coding-java.md

This snippet shows how to create a simple Java class with a main method that prints 'Hello, World!' to the console. It is a fundamental starting point for any Java project.

```java
package com.timescale.java;

public class Main {

    public static void main(String... args) {
        System.out.println("Hello, World!");
    }
}
```

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

### SQL - Calculate instantaneous change with idelta_right()

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/counter_agg/idelta_right.md

Get the instantaneous change at the end of each 15-minute counter aggregate. This example demonstrates how to use idelta_right() on a counter aggregate created with counter_agg().

```sql
SELECT
    id,
    bucket,
    idelta_right(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
```

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

### Install TimescaleDB Toolkit

Source: https://github.com/timescale/docs/blob/latest/self-hosted/tooling/install-toolkit.md

Install the TimescaleDB Toolkit package using Homebrew.

```bash
brew install timescaledb-toolkit
```

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

### Asynchronous Replication Configuration

Source: https://github.com/timescale/docs/blob/latest/self-hosted/replication-and-ha/configure-replication.md

Example configuration for asynchronous streaming replication. This setup prioritizes performance but carries a risk of minor data loss and potential inconsistencies between primary and replica nodes.

```yaml
listen_addresses = '*'
wal_level = replica
max_wal_senders = 2
max_replication_slots = 2
synchronous_commit = off
```

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

### Install postgresql-client on Debian

Source: https://github.com/timescale/docs/blob/latest/_partials/_psql-installation-linux.md

Install the PostgreSQL client package on Debian-based systems using apt.

```bash
apt-get install postgresql-client
```

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

### Convert Example Table to Hypertable

Source: https://github.com/timescale/docs/blob/latest/_partials/_livesync-terminal.md

Example of converting the 'metrics' table to a hypertable with 'time' as the partition column and a 1-day interval.

```sql
psql -X -d $TARGET -c "SELECT public.create_hypertable('public.metrics', by_range('time', '1 day'::interval));"
```

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

### SQL - Get Last Timestamp from TimeWeightSummary

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/time_weight/last_time.md

This example demonstrates how to create a TimeWeightSummary using time_weight() and then extract the last timestamp using last_time(). It groups data by day and calculates the summary for each day.

```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,
    last_time(tw)
FROM t;
```

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

### Connect to the New Database

Source: https://github.com/timescale/docs/blob/latest/self-hosted/install/installation-cloud-image.md

Connect to the newly created database. This is necessary before creating extensions.

```sql
\c tsdb
```

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

### SQL - Get bottom 5 values from min_n aggregate

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/min_n/into_values.md

Use into_values() to retrieve the 5 smallest values from a min_n aggregate. This example calculates values using a series and then extracts the bottom 5.

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

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

### SQL - Get Maximum Frequency of a Value

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/freq_agg/max_frequency.md

Find the maximum frequency of the value `3` in a column named `value` within the table `value_test`. This example demonstrates how to use `max_frequency` with an aggregate created by `mcv_agg`.

```sql
SELECT max_frequency(
    (SELECT mcv_agg(20, value) FROM value_test),
    3
);
```

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

### Create an inventory table

Source: https://github.com/timescale/docs/blob/latest/mst/dblink-extension.md

Create a sample table named 'inventory' to demonstrate data insertion and retrieval.

```sql
CREATE TABLE inventory (id int);
```

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

### Create a backup in directory format with pg_dump

Source: https://github.com/timescale/docs/blob/latest/mst/manage-backups.md

This example demonstrates creating a backup in directory format using two concurrent jobs. The output is stored in a directory named 'backup'.

```bash
pg_dump 'postgres://tsdbadmin:password@mypg-myproject.a.timescaledb.io:26882/defaultdb?sslmode=require' -f backup -j 2 -F directory
```

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

### SQL Example: Get First and Last Values from Daily Counter Aggregates

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/counter_agg/first_val.md

Demonstrates how to use first_val() and last_val() to extract the initial and final values from daily counter aggregates generated using counter_agg().

```sql
WITH t as (
  SELECT
      time_bucket('1 day'::interval, ts) as dt,
      counter_agg(ts, val) AS cs -- get a CounterSummary
  FROM table
  GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    first_val(cs) -- extract the value of the first point in the CounterSummary
    last_val(cs) -- extract the value of the last point in the CounterSummary
FROM t;
```

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

### SQL - Combine state aggregates with rollup()

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/compact_state_agg/rollup.md

This example demonstrates how to combine state aggregates from 1-minute buckets into a single aggregate using rollup(), and then calculate the duration spent in the 'START' state within that combined aggregate.

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

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

### Create a sample products table

Source: https://github.com/timescale/docs/blob/latest/use-timescale/extensions/pg-textsearch.md

This SQL statement defines a table named 'products' with columns for product details, including text fields for name and description, intended for full-text search indexing.

```sql
CREATE TABLE products (
    id serial PRIMARY KEY,
    name text,
    description text,
    category text,
    price numeric
);
```

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

### Get Top 5 Values from MaxN Aggregate

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/max_n/into_values.md

Use `into_values()` to retrieve the top 5 largest values computed by the `max_n()` aggregate. This example demonstrates finding the top 5 values from a generated series of numbers.

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

```sql
into_values 
-------------
10006
10005
10004
10003
10002
```

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

### Show policies for a continuous aggregate

Source: https://github.com/timescale/docs/blob/latest/api/continuous-aggregates/show_policies.md

This example demonstrates how to call the show_policies() function for a continuous aggregate named 'example_continuous_aggregate'.

```sql
SELECT timescaledb_experimental.show_policies('example_continuous_aggregate');
```

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

### SQL: Get Counter Resets per 15-Minute Bucket

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/counter_agg/num_resets.md

This example demonstrates how to calculate the number of counter resets for each 15-minute time bucket using `counter_agg` and `num_resets`. Ensure you have a table with time-series data and a counter value.

```sql
SELECT
    id,
    bucket,
    num_resets(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
```

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

### Calculate time spent in 'running' state

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/state_agg/duration_in.md

This example demonstrates how to create a table tracking system state changes and then use duration_in() to calculate the total time spent in the 'running' state. The result can be further processed using EXTRACT to get the duration in seconds.

```sql
SET timezone TO 'UTC';
CREATE TABLE states(time TIMESTAMPTZ, state TEXT);
INSERT INTO states VALUES
  ('1-1-2020 10:00', 'starting'),
  ('1-1-2020 10:30', 'running'),
  ('1-3-2020 16:00', 'error'),
  ('1-3-2020 18:30', 'starting'),
  ('1-3-2020 19:30', 'running'),
  ('1-5-2020 12:00', 'stopping');

SELECT duration_in(
  state_agg(time, state),
  'running'
) FROM states;
```

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

### Get bottom 5 values from a series

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/min_n/into_array.md

This example demonstrates how to use `into_array` with `min_n` to find the 5 smallest values generated by a series. The `min_n` aggregate is first used to track the lowest 5 values, and then `into_array` extracts them into a sorted array.

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

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

### Get Migration Best Practices

Source: https://github.com/timescale/docs/blob/latest/ai/mcp-server.md

Request guidance from the AI Assistant on optimizing services before migrating a large database. This includes recommendations on compute resources, regions, and storage.

```shell
>  I need to migrate a database with 25 million rows of data to Tiger Cloud, 
   please tell me what service optimizations i should do before migration. 
   
   
```

```text
⏺ For migrating a database with 25 million rows to Tiger Cloud, here are the key service optimizations to do before migration:

   Pre-Migration Service Optimizations

   1. Right-size your compute resources:
     - For 25M rows, start with at least 2 CPU/8GB or 4 CPU/16GB
     - You can scale up during migration if needed, then scale down after

   2. Enable the appropriate addons:
     - Use time-series addon if you are working with time-series data
     - Use ai addon if you need vector/AI capabilities

   3. Choose optimal region:
     - Select the region closest to your current database to minimize network transfer time
     - This significantly reduces migration duration

   4. Consider enhanced storage (Enterprise only):
     - If you expect high write throughput during migration, enhanced storage provides better IOPS (up to 32,000)

   5. Skip replicas initially:
     - Start with 0 replicas during migration
     - Add replicas after migration completes to avoid duplicate data copying

   6. Plan for storage space:
     - Estimate your uncompressed data size
     - Allocate sufficient high-performance storage (scales automatically but good to plan)
     - You can enable tiered storage later for older data

   During Migration Best Practices

   Migration method selection:
     - 25M rows: Use schema-then-data migration (migrate schema first, then copy data in chunks)
     - Allows restart if a copy operation fails
     - Better for databases >10GB

   Database configuration to set AFTER migration:
     - Set up compression policies (once data is loaded)
     - Configure retention policies
     - Create continuous aggregates
     - Enable tiered storage for cost optimization

   Connection settings:
     - Use the pooled connection endpoint for better performance during bulk inserts
     - Disable unnecessary constraints/indexes during migration, re-enable after

   Would you like me to help you create an optimized service for this migration?
   
```

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

### Extract First Timestamp from Daily Counter Aggregate

Source: https://github.com/timescale/docs/blob/latest/api/_hyperfunctions/counter_agg/first_time.md

This example demonstrates how to use first_time() to retrieve the timestamp of the first data point within each daily counter aggregate. It also shows how to use last_time() to get the timestamp of the last data point.

```sql
WITH t as (
  SELECT
      time_bucket('1 day'::interval, ts) as dt,
      counter_agg(ts, val) AS cs -- get a CounterSummary
  FROM table
  GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    first_time(cs) -- extract the timestamp of the first point in the CounterSummary
    last_time(cs) -- extract the timestamp of the last point in the CounterSummary
FROM t;
```

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

### Sample Usage of gauge_agg() with irate_right()

Source: https://github.com/timescale/docs/blob/latest/api/gauge_agg.md

This example demonstrates how to create a gauge summary from time-series data and then use the irate_right accessor function to get the instantaneous rate of change over the last two time intervals. Ensure your data is filtered and grouped appropriately before aggregation.

```sql
WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        gauge_agg(ts, val) AS gs
    FROM foo
    WHERE id = 'bar'
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    irate_right(gs)
FROM t;
```