### Start fresh

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

Options to start a fresh TimescaleDB instance using either a one-line install script or a manual Docker command.

```bash
curl -sL https://tsdb.co/start-local | sh
```

```bash
docker run -d --name timescaledb -p 6543:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb-ha:pg18
```

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

### One-line install for TimescaleDB

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/README.md

This command downloads and starts TimescaleDB, exposes PostgreSQL on port 6543, tunes settings, and sets up a persistent data volume.

```sh
curl -sL https://tsdb.co/start-local | sh
```

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

### Verify TimescaleDB Installation

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/financial-ticks/README.md

SQL query to check if the timescaledb extension is installed and its version.

```sql
SELECT extname, extversion FROM pg_extension WHERE extname = 'timescaledb';
```

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

### Install target

Source: https://github.com/timescale/timescaledb/blob/main/sql/CMakeLists.txt

Installs the extension file and update scripts.

```cmake
install(FILES ${CMAKE_CURRENT_BINARY_DIR}/${INSTALL_FILE} ${UPDATE_SCRIPTS}
        DESTINATION "${PG_SHAREDIR}/extension")
```

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

### View container logs

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

Commands to view logs for TimescaleDB Docker containers, differentiating between a one-line install and a manual Docker command.

```bash
docker logs timescaledb-ha-pg18-quickstart
```

```bash
docker logs timescaledb
```

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

### Expected output for verification query

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

The expected output format when verifying the TimescaleDB installation.

```text
   extname   | extversion
------------+------------
 timescaledb | 2.x.x
```

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

### C99 declarations before code example

Source: https://github.com/timescale/timescaledb/blob/main/docs/StyleGuide.md

Example demonstrating C99 style with declarations after code execution within a function.

```C
void some_function()
{
    prepare();
	int result = fetch();
	...
}
```

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

### Installation Rule

Source: https://github.com/timescale/timescaledb/blob/main/src/CMakeLists.txt

Installs the project target to the specified library directory.

```cmake
install(TARGETS ${PROJECT_NAME} DESTINATION ${PG_PKGLIBDIR})
```

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

### Modular code and namespacing example (correct)

Source: https://github.com/timescale/timescaledb/blob/main/docs/StyleGuide.md

Example of correct namespaced function naming for modular code, using the module name as a prefix.

```C
void
cache_initialize(Cache *c)
{
    ...
}
```

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

### Troubleshooting Docker container won't start

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

A bash command to check if a Docker container is running, useful for troubleshooting startup issues.

```bash
# Check if container is running
docker ps -a
```

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

### Install Extension Control File

Source: https://github.com/timescale/timescaledb/blob/main/CMakeLists.txt

Installs the configured extension control file to the PostgreSQL share directory.

```cmake
install(FILES ${CMAKE_CURRENT_BINARY_DIR}/${EXT_CONTROL_FILE}
        DESTINATION "${PG_SHAREDIR}/extension")
```

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

### Object-Oriented Style Example

Source: https://github.com/timescale/timescaledb/blob/main/docs/StyleGuide.md

An example demonstrating how to achieve an object-oriented programming style in C, using struct embedding and function pointers.

```C
typedef struct Shape
{
    int color;
    void (*draw)(Shape *, Canvas *);
}
Shape;

void
shape_draw(Shape *shape)
{
    /* open canvas for drawing */
    Canvas *c = canvas_open();

    /* other common shape code */
    ...

    shape->draw(shape, c);

    canvas_close(c);
}

typedef struct Circle
{
    Shape shape;
    float diameter;
}
Circle;

Circle blue_circle =
    {
        .shape = {
            .color = BLUE,
            .draw = circle_draw,
        },
        .diameter = 10.1,
    };

void
circle_draw(Shape *shape, Canvas *canvas)
{
    Circle *circle = (Circle *) shape;

    /* draw circle */
    ...
}

```

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

### Target Properties and Installation for OSM Mock Library

Source: https://github.com/timescale/timescaledb/blob/main/test/src/loader/CMakeLists.txt

Configures output name and installs the OSM mock library.

```cmake
foreach(MOCK_VERSION mock-1)
  set_target_properties(
    ${PROJECT_NAME}_osm-${MOCK_VERSION}
    PROPERTIES OUTPUT_NAME ${PROJECT_NAME}_osm-${MOCK_VERSION} PREFIX "")

  install(
    TARGETS ${PROJECT_NAME}_osm-${MOCK_VERSION}
    DESTINATION ${PG_PKGLIBDIR}
    OPTIONAL)
endforeach(MOCK_VERSION)
```

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

### Connect to TimescaleDB

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/financial-ticks/README.md

Command to connect to the running TimescaleDB instance using psql.

```sh
psql "postgres://postgres:password@localhost:6543/postgres"
```

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

### Example test script execution

Source: https://github.com/timescale/timescaledb/blob/main/test/perl/README.md

Example of setting up a TimescaleNode, running a psql command, and asserting the result.

```perl
my $node = get_new_ts_node('access_node');
$node->init;
$node->start;

my $ret = $node->safe_psql('postgres', 'SELECT 1');
is($ret, '1', 'SELECT 1 returns 1');

$node->stop('fast');
```

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

### Stop and remove existing container

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

Commands to stop and remove existing TimescaleDB Docker containers, with variations for one-line install and manual Docker command.

```bash
docker stop timescaledb-ha-pg18-quickstart && docker rm timescaledb-ha-pg18-quickstart
```

```bash
docker stop timescaledb && docker rm timescaledb
```

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

### Troubleshooting connection issues

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

Commands and checks for when unable to connect to TimescaleDB with psql.

```bash
docker ps
```

```bash
lsof -i :6543
```

```bash
psql -h 127.0.0.1 -p 6543 -U postgres
```

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

### Target Properties and Installation for Mock Libraries

Source: https://github.com/timescale/timescaledb/blob/main/test/src/loader/CMakeLists.txt

Configures output names and installs mock libraries to the PostgreSQL package library directory.

```cmake
foreach(
  MOCK_VERSION
  mock-1
  mock-2
  mock-3
  mock-4
  mock-broken
  mock-5
  mock-6)
  set_target_properties(
    ${PROJECT_NAME}-${MOCK_VERSION}
    PROPERTIES OUTPUT_NAME ${PROJECT_NAME}-${MOCK_VERSION} PREFIX "")

  install(
    TARGETS ${PROJECT_NAME}-${MOCK_VERSION}
    DESTINATION ${PG_PKGLIBDIR}
    OPTIONAL)
endforeach(MOCK_VERSION)
```

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

### Modular code and namespacing example (incorrect)

Source: https://github.com/timescale/timescaledb/blob/main/docs/StyleGuide.md

Example of incorrect function naming for modular code, lacking the module name prefix.

```C
void
initialize_cache(Cache *c)
{

}
```

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

### Setting INSTALL_FILE

Source: https://github.com/timescale/timescaledb/blob/main/sql/CMakeLists.txt

Sets the installation filename for the project, incorporating the project name and version.

```cmake
set(INSTALL_FILE ${PROJECT_NAME}--${PROJECT_VERSION_MOD}.sql)
```

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

### Stop and Remove One-Line Install Container

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/nyc-taxi/README.md

Commands to stop and remove the Docker container created using the one-line install method.

```bash
# Stop the container
docker stop timescaledb-ha-pg18-quickstart

# Remove the container
docker rm timescaledb-ha-pg18-quickstart

# Remove the persistent data volume
docker volume rm timescaledb_data

# (Optional) Remove the Docker image
docker rmi timescale/timescaledb-ha:pg18
```

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

### Main Install Check Target

Source: https://github.com/timescale/timescaledb/blob/main/test/CMakeLists.txt

Defines the 'installcheck' custom target which depends on all local install checks.

```cmake
add_custom_target(installcheck DEPENDS ${_install_checks})
```

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

### Install Mock Extension Files

Source: https://github.com/timescale/timescaledb/blob/main/test/sql/loader/CMakeLists.txt

Installs various mock SQL and control files for TimescaleDB extensions into the PostgreSQL share directory when the build type is Debug.

```cmake
if(CMAKE_BUILD_TYPE MATCHES Debug)
  install(
    FILES timescaledb--mock-1.sql
          timescaledb--mock-2.sql
          timescaledb--mock-3.sql
          timescaledb--mock-4.sql
          timescaledb--mock-5.sql
          timescaledb--mock-6.sql
          timescaledb--mock-broken.sql
          timescaledb--mock-1--mock-2.sql
          timescaledb--mock-2--mock-3.sql
          timescaledb--mock-3--mock-4.sql
          timescaledb--mock-5--mock-6.sql
          timescaledb--mock-broken--mock-5.sql
          timescaledb--0.0.0.sql
          timescaledb_osm.control
          timescaledb_osm--mock-1.sql
    DESTINATION "${PG_SHAREDIR}/extension")
endif(CMAKE_BUILD_TYPE MATCHES Debug)
```

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

### Load Sample Data into Columnstore

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/financial-ticks/README.md

SQL commands to enable direct to columnstore copy and load data from a CSV file into the stock_prices table.

```sql
-- Enable direct to columnstore for this session
SET timescaledb.enable_direct_compress_copy = on;

-- Load data directly into columnstore (if you have a CSV file)
\COPY stock_prices FROM 'sp500_stock_prices_3d_1s.csv' WITH (FORMAT csv, HEADER true);

-- Verify data loaded
SELECT COUNT(*) FROM stock_prices;
```

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

### Download and decompress sample data

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

Bash commands to download the sample events_uuid.csv.gz file and then decompress it into events_uuid.csv.

```bash
# Download the sample data
wget https://assets.timescale.com/timescaledb-datasets/events_uuid.csv.gz

# Decompress the CSV file
gunzip events_uuid.csv.gz

# This will create events_uuid.csv ready for loading
```

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

### Clean Up - Manual Docker command

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

Commands to stop and remove the container for a TimescaleDB instance started with a manual Docker command.

```bash
# Stop the container
docker stop timescaledb

# Remove the container
docker rm timescaledb

# (Optional) Remove the Docker image
docker rmi timescale/timescaledb-ha:pg18
```

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

### Installation

Source: https://github.com/timescale/timescaledb/blob/main/tsl/src/CMakeLists.txt

Installs the tsl library to the PostgreSQL package library directory.

```cmake
install(TARGETS ${TSL_LIBRARY_NAME} DESTINATION ${PG_PKGLIBDIR})
```

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

### Performance Comparison

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

Compares the query performance between the raw hypertable and the continuous aggregate.

```sql
-- Query the raw hypertable (slower on large datasets)
\timing on
SELECT
    time_bucket('1 hour', time) AS hour,
    AVG(temperature) AS avg_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '60 days'
GROUP BY hour
ORDER BY hour DESC
LIMIT 24;

-- Query the continuous aggregate (much faster)
SELECT
    hour,
    avg_temp
FROM sensor_data_hourly
WHERE hour > NOW() - INTERVAL '60 days'
ORDER BY hour DESC
LIMIT 24;
```

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

### Activate time measuring

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/financial-ticks/README.md

Enables timing of SQL queries in psql.

```sql
-- Activate time measuring
\timing on
```

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

### Local Install Check Target

Source: https://github.com/timescale/timescaledb/blob/main/test/CMakeLists.txt

Defines the 'installchecklocal' custom target for running install checks against an existing PostgreSQL instance.

```cmake
add_custom_target(installchecklocal DEPENDS ${_local_install_checks})
```

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

### Gapfill query

Source: https://github.com/timescale/timescaledb/blob/main/tsl/src/nodes/gapfill/README.md

Example of a basic gapfill query.

```sql
SELECT
  time_bucket_gapfill(1,time,0,6) AS time,
  min(value) AS value
FROM (values (0,1),(5,6)) v(time,value)
GROUP BY 1 ORDER BY 1;
```

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

### Query continuous aggregate

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/financial-ticks/README.md

Example query to retrieve data from the pre-aggregated continuous view.

```sql
SELECT * from candlesticks_hourly WHERE ticker = 'NFLX';
```

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

### Perlbrew installation and usage

Source: https://github.com/timescale/timescaledb/blob/main/test/perl/README.md

Steps to install and use a specific Perl version with perlbrew.

```bash
perlbrew --force install 5.8.0
perlbrew use 5.8.0
perlbrew install-cpanm
cpanm install IPC::Run
```

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

### Download Dataset

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/financial-ticks/README.md

Command to download and extract the financial tick dataset.

```sh
curl -L https://assets.timescale.com/timescaledb-datasets/sp500_stock_prices_3d_1s.tar.gz | tar -xzf -
```

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

### Setting MODULE_PATHNAME and LOADER_PATHNAME

Source: https://github.com/timescale/timescaledb/blob/main/sql/CMakeLists.txt

Defines the installation paths for the TimescaleDB module and loader.

```cmake
set(MODULE_PATHNAME "$libdir/timescaledb-${PROJECT_VERSION_MOD}")
set(LOADER_PATHNAME "$libdir/timescaledb")
```

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

### Manually convert to columnstore

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

Immediately converts rowstore data to columnstore format for optimal query performance.

```sql
DO $$
DECLARE ch TEXT;
BEGIN
    FOR ch IN SELECT show_chunks('app_events') LOOP
        CALL convert_to_columnstore(ch);
    END LOOP;
END $$;
```

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

### Create Stock Prices Hypertable

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/financial-ticks/README.md

SQL command to create an optimized hypertable for stock prices, partitioned by timestamp and segmented by ticker.

```sql
-- Create the stock_prices table with the column ts as partitioning
-- Note: for optimized query performance grouping on ticker, we select this column to segment by
CREATE TABLE stock_prices (
  ts                TIMESTAMPTZ         NOT NULL,
  ticker            TEXT                NOT NULL,
  price             DOUBLE PRECISION    NOT NULL,
  change_delta      DOUBLE PRECISION    NOT NULL,
  change_percentage DOUBLE PRECISION    NOT NULL,
  volume            BIGINT NOT NULL CHECK (volume >= 0)
)
WITH (
  timescaledb.hypertable,
  timescaledb.segmentby='ticker'
);
```

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

### Standard COPY command

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

Loads data from a CSV file into the app_events table using the standard COPY command.

```bash
psql -h localhost -p 6543 -U postgres \
  -v ON_ERROR_STOP=1 \
  -c "COPY app_events FROM STDIN WITH (FORMAT csv, HEADER true);" \
  < events_uuid.csv
```

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

### Perl File Installation Logic

Source: https://github.com/timescale/timescaledb/blob/main/test/perl/CMakeLists.txt

Installs Perl files if TAP checks are enabled and the Perl test directory exists.

```cmake
set(PERL_FILES TimescaleNode.pm)

# Check if PostgreSQL was compiled with --enable-tap-tests
if(TAP_CHECKS AND EXISTS "${PG_PKGLIBDIR}/pgxs/src/test/perl")
  install(FILES ${PERL_FILES} DESTINATION "${PG_PKGLIBDIR}/pgxs/src/test/perl")
endif()
```

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

### Verify Data Loaded

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

SQL query to count the number of rows in the app_events table to verify that the data has been loaded successfully.

```sql
SELECT COUNT(*) FROM app_events;
```

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

### Create Continuous Aggregate

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

This SQL statement creates a materialized view that pre-aggregates sensor data into hourly buckets.

```sql
CREATE MATERIALIZED VIEW sensor_data_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity,
    AVG(pressure) AS avg_pressure,
    MIN(temperature) AS min_temp,
    MAX(temperature) AS max_temp,
    COUNT(*) AS reading_count
FROM sensor_data
GROUP BY hour, sensor_id;
```

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

### Hour-over-Hour Return

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/financial-ticks/README.md

Calculates the percentage growth by comparing the current price to the price exactly one hour ago.

```sql
WITH hourly_close AS (
  SELECT
    time_bucket('1 hour', ts) AS bucket,
    ticker,
    LAST(price, ts) AS closing_price
  FROM stock_prices
  GROUP BY bucket, ticker
)
SELECT
  bucket,
  ticker,
  closing_price,
  LAG(closing_price, 1) OVER (PARTITION BY ticker ORDER BY bucket) AS prev_close,
  ((closing_price - LAG(closing_price, 1) OVER (PARTITION BY ticker ORDER BY bucket)) 
   / LAG(closing_price, 1) OVER (PARTITION BY ticker ORDER BY bucket)) * 100 AS hourly_return_pct
FROM hourly_close;
```

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

### Verify Data Insertion

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

Counts the total number of rows in the sensor_data table to verify data insertion.

```sql
SELECT COUNT(*) FROM sensor_data;
```

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

### Install Check Post Hook

Source: https://github.com/timescale/timescaledb/blob/main/test/CMakeLists.txt

Defines a 'installcheck-post-hook' target that is invoked after the 'installcheck' target finishes, useful for tasks like code coverage.

```cmake
add_custom_target(installcheck-post-hook COMMENT "Post test hook")
add_custom_command(
  TARGET installcheck
  POST_BUILD
  COMMAND cmake --build ${CMAKE_CURRENT_BINARY_DIR} --target
          installcheck-post-hook)
```

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

### Dataset Preview

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/financial-ticks/README.md

A preview of the financial tick data, showing columns like timestamp, ticker, price, and volume.

```csv
2025-11-12 14:30:00+00:00,NVDA,38.25,0.25,0.65359,5095712
2025-11-12 14:30:00+00:00,AAPL,152.03,0.03,0.01973,6466554
2025-11-12 14:30:00+00:00,MSFT,129.23,0.23,0.17798,4417848
2025-11-12 14:30:00+00:00,GOOG,174.93,-0.07,-0.04002,19602229
2025-11-12 14:30:00+00:00,GOOGL,71.21,0.21,0.2949,3149482
2025-11-12 14:30:00+00:00,AMZN,95.95,-0.05,-0.05211,12150474
2025-11-12 14:30:00+00:00,AVGO,196.15,0.15,0.07647,6166047
2025-11-12 14:30:00+00:00,META,133.22,0.22,0.16514,12230004
2025-11-12 14:30:00+00:00,TSLA,82.0,0.0,0.0,10298937
2025-11-12 14:30:00+00:00,BRK.B,56.84,-0.16,-0.28149,10980047
```

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

### Query Continuous Aggregate

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

This SQL query retrieves pre-aggregated hourly averages from the continuous aggregate for faster results.

```sql
-- Get hourly averages for the last 24 hours
SELECT
    hour,
    sensor_id,
    ROUND(avg_temp::numeric, 2) AS avg_temp,
    ROUND(avg_humidity::numeric, 2) AS avg_humidity,
    reading_count
FROM sensor_data_hourly
WHERE hour > NOW() - INTERVAL '24 hours'
ORDER BY hour DESC, sensor_id
LIMIT 50;
```

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

### Insert Sample Data

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

Enables timing and direct compress insert, then inserts sample sensor data for multiple sensors over 90 days. Finally, it converts chunks to columnstore for optimization.

```sql
-- Enable timing to see time to execute queries
\timing on

-- Insert sample data for multiple sensors
-- SET timescaledb.enable_direct_compress_insert = on to insert data directly to the columnstore (columnnar format for performance)
SET timescaledb.enable_direct_compress_insert = on;
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, pressure)
SELECT
    time,
    'sensor_' || ((random() * 9)::int + 1),
    20 + (random() * 15),
    40 + (random() * 30),
    1000 + (random() * 50)
FROM generate_series(
    NOW() - INTERVAL '90 days',
    NOW(),
    INTERVAL '1 seconds'
) AS time;

-- Once data is inserted into the columnstore we optimize the order and structure 
-- this compacts and orders the data in the chunks for optimal query performance and compression
DO $$
DECLARE ch TEXT;
BEGIN
    FOR ch IN SELECT show_chunks('sensor_data') LOOP
        CALL convert_to_columnstore(ch, recompress := true);
    END LOOP;
END $$;
```

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

### Funnel Analysis Query

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

A query to perform funnel analysis, calculating conversion rates between different event types within user sessions.

```sql
WITH funnel AS (
    SELECT
        user_id,
        session_id,
        MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) as viewed,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) as added_to_cart,
        MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) as purchased
    FROM app_events
    WHERE event_id >= to_uuidv7_boundary(now() - interval '30 days')
    GROUP BY user_id, session_id
)
SELECT
    SUM(viewed) as sessions_with_view,
    SUM(added_to_cart) as sessions_with_cart,
    SUM(purchased) as sessions_with_purchase,
    ROUND(100.0 * SUM(added_to_cart) / NULLIF(SUM(viewed), 0), 2) as view_to_cart_pct,
    ROUND(100.0 * SUM(purchased) / NULLIF(SUM(added_to_cart), 0), 2) as cart_to_purchase_pct
FROM funnel;
```

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

### Unix-based Systems Build Steps

Source: https://github.com/timescale/timescaledb/blob/main/docs/BuildSource.md

Steps to clone the repository, checkout a release tag, bootstrap the build system, build, and install TimescaleDB on Unix-like systems.

```bash
git clone git@github.com:timescale/timescaledb.git
cd timescaledb
# Find the latest release and checkout, e.g. for 2.15.0:
git checkout 2.15.0
# Bootstrap the build system
./bootstrap
# To build the extension
cd build && make
# To install
make install
```

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

### Create a hypertable with columnstore enabled

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

SQL command to create a hypertable named 'sensor_data' with time, sensor_id, temperature, humidity, and pressure columns, enabling TimescaleDB hypertable functionality.

```sql
-- Create a hypertable with automatic columnstore
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id TEXT NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    pressure DOUBLE PRECISION
) WITH (
    tsdb.hypertable
);
```

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

### Windows Build Steps (Older Visual Studio Versions)

Source: https://github.com/timescale/timescaledb/blob/main/docs/BuildSource.md

Steps to clone the repository, checkout a release tag, bootstrap the build system, and build/install TimescaleDB using command line for older Visual Studio versions on Windows.

```bash
git clone git@github.com:timescale/timescaledb.git
cd timescaledb

# Find the latest release and checkout, e.g. for 2.15.0:
git checkout 2.15.0
# Bootstrap the build system
bootstrap.bat
# To build the extension from command line
cmake --build ./build --config Release
# To install
cmake --build ./build --config Release --target install

# Alternatively, build in Visual Studio via its built-in support for
# CMake or by opening the generated build/timescaledb.sln solution file.
```

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

### C type declaration convention (incorrect)

Source: https://github.com/timescale/timescaledb/blob/main/docs/StyleGuide.md

Example of incorrect C type declaration, contrasting with the preferred UpperCamelCase naming.

```C
typedef struct my_type
{
    ...
} my_type;
```

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

### C function and variable naming convention (correct)

Source: https://github.com/timescale/timescaledb/blob/main/docs/StyleGuide.md

Example of correct lowercase underscore-separated naming for C functions and variables.

```C
static void
my_function_name(int my_parameter)
{
    int my_variable;
    ...
}
```

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

### C type declaration convention (correct)

Source: https://github.com/timescale/timescaledb/blob/main/docs/StyleGuide.md

Example of correct C type declaration using typedef and UpperCamelCase for new composite/aggregate types.

```C
typedef struct MyType
{
    ...
} MyType;
```

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

### C function and variable naming convention (incorrect)

Source: https://github.com/timescale/timescaledb/blob/main/docs/StyleGuide.md

Example of incorrect C naming conventions, contrasting with the preferred lowercase underscore-separated style.

```C
static void
MyFunctionName(int myParameter)
{
    int myVariable;
    ...
}
```

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

### Run Analytical Queries

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

Enables query timing and runs several analytical queries including average readings per sensor, hourly averages using time_bucket, daily statistics, and the latest reading for each sensor.

```sql
-- Enable query timing to see performance
\timing on

-- Query 1: Average readings per sensor over the last 7 days
SELECT
    sensor_id,
    COUNT(*) as readings,
    ROUND(AVG(temperature)::numeric, 2) as avg_temp,
    ROUND(AVG(humidity)::numeric, 2) as avg_humidity,
    ROUND(AVG(pressure)::numeric, 2) as avg_pressure
FROM sensor_data
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY sensor_id
ORDER BY sensor_id;

-- Query 2: Hourly averages using time_bucket 
-- Time buckets enable you to aggregate data in hypertables by time interval and calculate summary values.
SELECT
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    ROUND(AVG(temperature)::numeric, 2) as avg_temp,
    ROUND(AVG(humidity)::numeric, 2) as avg_humidity
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour, sensor_id
ORDER BY hour DESC, sensor_id
LIMIT 20;

-- Query 3: Daily statistics across all sensors
SELECT
    time_bucket('1 day', time) AS day,
    COUNT(*) as total_readings,
    ROUND(AVG(temperature)::numeric, 2) as avg_temp,
    ROUND(MIN(temperature)::numeric, 2) as min_temp,
    ROUND(MAX(temperature)::numeric, 2) as max_temp
FROM sensor_data
GROUP BY day
ORDER BY day DESC
LIMIT 10;

-- Query 4: Latest reading for each sensor
-- Highlights the value of Skipscan executing in under 100ms without skipscan it takes over 5sec
SELECT DISTINCT ON (sensor_id)
    sensor_id,
    time,
    ROUND(temperature::numeric, 2) as temperature,
    ROUND(humidity::numeric, 2) as humidity,
    ROUND(pressure::numeric, 2) as pressure
FROM sensor_data
ORDER BY sensor_id, time DESC;
```

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

### Coccinelle Example Block Header

Source: https://github.com/timescale/timescaledb/blob/main/coccinelle/README.md

An example of a basic block header in a Coccinelle patch file.

```coccinelle
@ name @
Expression var1;
Expression var2;
@@
```

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

### Revenue by Country Query

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

Analyzes purchase data to show revenue, purchase count, and average order value by country for the last 30 days.

```sql
SELECT
    country_code,
    COUNT(*) as purchase_count,
    SUM(revenue_cents) / 100.0 as total_revenue,
    ROUND(AVG(revenue_cents) / 100.0, 2) as avg_order_value
FROM app_events
WHERE event_id >= to_uuidv7_boundary(now() - interval '30 days')
  AND event_type = 'purchase'
GROUP BY country_code
ORDER BY total_revenue DESC
LIMIT 10;
```

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

### Coccinelle Example Block Header with Dependencies

Source: https://github.com/timescale/timescaledb/blob/main/coccinelle/README.md

Examples of block headers in Coccinelle patches that specify dependencies on previous blocks.

```coccinelle
@ b2 depends on name @
@@
```

```coccinelle
@ b3 depends on name && !b2 @
@@
```

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

### Load Sample Data - Direct to Columnstore

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

This command loads the events_uuid.csv file directly into the app_events columnstore using psql. It enables direct compress copy and specifies CSV format with a header.

```bash
psql -h localhost -p 6543 -U postgres \
  -v ON_ERROR_STOP=1 \
  -c "SET timescaledb.enable_direct_compress_copy = on;
      COPY app_events FROM STDIN WITH (FORMAT csv, HEADER true);" \
  < events_uuid.csv
```

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

### Rerun Install Check Target

Source: https://github.com/timescale/timescaledb/blob/main/test/CMakeLists.txt

Defines the 'installcheck-rerun' custom target for rerunning the install checks, typically used in CI environments.

```cmake
add_custom_target(
  installcheck-rerun
  COMMAND ${PRIMARY_TEST_DIR}/ci_rerun.sh installcheck
  USES_TERMINAL)
```

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

### Coccinelle Example Block with Variable Referencing Previous Match

Source: https://github.com/timescale/timescaledb/blob/main/coccinelle/README.md

An example demonstrating how variables within a block can reference matches from previous blocks in Coccinelle.

```coccinelle
@ b2 depends on name @
Expression name.var1;
@@
```

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

### Inefficient Query (Anti-Pattern)

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

An example of an inefficient query that scans all chunks and requires evaluating a function for every row, preventing chunk exclusion.

```sql
-- ❌ WRONG: Scans ALL chunks, extracts timestamp from every row
SELECT COUNT(*), event_type
FROM app_events
WHERE uuid_timestamp(event_id) >= now() - interval '7 days'
GROUP BY event_type;
```

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

### SkipScan on Single Column (Distinct Users)

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

Demonstrates SkipScan optimization using a compression index on the segmentby column to efficiently retrieve distinct user IDs.

```sql
-- Demonstrates SkipScan optimization: uses compression index to skip repeated values
\timing on
SELECT DISTINCT ON (user_id)
    user_id,
    event_type,
    uuid_timestamp(event_id) as event_time
FROM app_events
WHERE event_id >= to_uuidv7_boundary(now() - interval '30 days')
ORDER BY user_id, event_id DESC
LIMIT 50;
```

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

### Configure Test Files

Source: https://github.com/timescale/timescaledb/blob/main/test/t/CMakeLists.txt

Iterates through the PROVE_TEST_FILES list and configures each file to be copied to the binary directory.

```cmake
foreach(P_FILE ${PROVE_TEST_FILES})
  configure_file(${P_FILE} ${CMAKE_CURRENT_BINARY_DIR}/${P_FILE} COPYONLY)
endforeach(P_FILE)
```

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

### Efficient Time Range Query (Chunk Pruning)

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/events-uuidv7/README.md

An efficient query that uses a boundary function for chunk exclusion, demonstrating fast time-range data retrieval.

```sql
-- ✅ CORRECT: Uses boundary function for chunk exclusion
\timing on
SELECT COUNT(*), event_type
FROM app_events
WHERE event_id >= to_uuidv7_boundary(now() - interval '7 days')
GROUP BY event_type;
```

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

### Gapfill query with interpolation

Source: https://github.com/timescale/timescaledb/blob/main/tsl/src/nodes/gapfill/README.md

Example of a gapfill query using the interpolation function.

```sql
SELECT
  time_bucket_gapfill(1,time,0,6) AS time,
  interpolate(min(value)) AS value
FROM (values (0,1),(5,6)) v(time,value)
GROUP BY 1 ORDER BY 1;
```

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

### SkipScan with Merge Append

Source: https://github.com/timescale/timescaledb/blob/main/tsl/src/nodes/skip_scan/README.md

Example of a SkipScan integrated with a Merge Append node for multiple chunks.

```SQL
Unique
  ->  Merge Append
        Sort Key: _hyper_2_1_chunk.dev_name
        ->  Custom Scan (SkipScan) on _hyper_2_1_chunk
              ->  Index Scan using _hyper_2_1_chunk_idx on _hyper_2_1_chunk
        ->  Custom Scan (SkipScan) on _hyper_2_2_chunk
              ->  Index Scan using _hyper_2_2_chunk_idx on _hyper_2_2_chunk
```

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

### Windows Configuration

Source: https://github.com/timescale/timescaledb/blob/main/CMakeLists.txt

Sets the default configuration types for Windows builds.

```cmake
if(WIN32 AND NOT CMAKE_CONFIGURATION_TYPES)
  set(CMAKE_CONFIGURATION_TYPES
      Release
      CACHE
        STRING
        "Semicolon separated list of supported configuration types, only supports Debug, Release, MinSizeRel, and RelWithDebInfo, anything else will be ignored."
        FORCE)
endif()
```

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

### Launcher per-DB state machine diagram

Source: https://github.com/timescale/timescaledb/blob/main/src/loader/README.md

A diagram illustrating the states and transitions of the launcher's state machine for each database.

```text
                   stop
      ENABLED+--------------+
         +   ^--------------|
         |   start/restart ||
         |                 ||
         |                 ||
         v                 +v
      ALLOCATED+------> DISABLED
        ^+       stop       ^
        ||
restart ||                  |
        ||
        +v                  |
      STARTED+--------------+
                stop / scheduler quit

```

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

### SkipScan with Unique Node

Source: https://github.com/timescale/timescaledb/blob/main/tsl/src/nodes/skip_scan/README.md

Example of a SkipScan integrated with a Unique node for a single chunk or normal table.

```SQL
Unique
  ->  Custom Scan (SkipScan) on skip_scan
        ->  Index Scan using skip_scan_dev_name_idx on skip_scan
```

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

### Remove Docker Image

Source: https://github.com/timescale/timescaledb/blob/main/docs/getting-started/nyc-taxi/README.md

Removes the TimescaleDB HA Docker image.

```bash
docker rmi timescale/timescaledb-ha:pg18
```

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

### Get PostgreSQL Configuration

Source: https://github.com/timescale/timescaledb/blob/main/CMakeLists.txt

Retrieves various PostgreSQL configuration paths and flags using the get_pg_config command.

```cmake
# Get PostgreSQL configuration from pg_config
get_pg_config(PG_INCLUDEDIR --includedir)
get_pg_config(PG_INCLUDEDIR_SERVER --includedir-server)
get_pg_config(PG_LIBDIR --libdir)
get_pg_config(PG_PKGLIBDIR --pkglibdir)
get_pg_config(PG_SHAREDIR --sharedir)
get_pg_config(PG_BINDIR --bindir)
get_pg_config(PG_CFLAGS --cflags)
get_pg_config(PG_CFLAGS_SL --cflags_sl)
get_pg_config(PG_CPPFLAGS --cppflags)
get_pg_config(PG_LDFLAGS --ldflags)
```