> ## Documentation Index
> Fetch the complete documentation index at: https://docs-relytone.data.cloud/llms.txt
> Use this file to discover all available pages before exploring further.

# TimescaleDB

> Get started with TimescaleDB time-series database functionality in Relyt ONE. Learn how to create hypertables, insert time-series data, and run efficient queries.

<Note>
  Relyt ONE integrates [TimescaleDB](https://github.com/timescale/timescaledb), an open-source time-series database extension for PostgreSQL. We use TimescaleDB Apache 2 Edition v2.21.3 to provide powerful time-series capabilities. For a detailed comparison of features between Apache 2 Edition and Community Edition, see the [TimescaleDB editions feature comparison](https://docs.tigerdata.com/about/latest/timescaledb-editions/#feature-comparison).
</Note>

## What is TimescaleDB?

TimescaleDB is a powerful PostgreSQL extension that transforms your database into a high-performance time-series database. It's designed for handling large volumes of time-stamped data while maintaining full SQL compatibility and leveraging PostgreSQL's reliability.

Key benefits:

* **Automatic partitioning** via hypertables for efficient time-series data storage
* **Fast queries** with optimized indexing and query planning
* **Full SQL support** - no need to learn new query languages
* **Horizontal scaling** capabilities for massive datasets
* **Built-in functions** for time-series analysis

## Prerequisites

Before getting started, ensure you have:

* An active Relyt database connection
* TimescaleDB extension enabled on your database
* Basic familiarity with SQL and PostgreSQL

## Quick Start

### Step 1: Enable TimescaleDB Extension

First, connect to your Relyt database and enable the TimescaleDB extension:

```sql theme={null}
-- Connect to your database and enable TimescaleDB
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Verify the extension is installed
\dx
```

You should see `timescaledb` listed among your installed extensions.

### Step 2: Create Your First Hypertable

A hypertable is TimescaleDB's core abstraction - it looks like a regular PostgreSQL table but is automatically partitioned by time.

```sql theme={null}
-- Create a regular table first
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    location TEXT
);

-- Convert it to a hypertable
SELECT create_hypertable('sensor_data', by_range('time'));
```

<Note>
  The `time` column must be the first argument to `create_hypertable()` and should be of type `TIMESTAMPTZ`, `TIMESTAMP`, or `DATE`.
</Note>

<Warning>
  Always include a `time` column in your hypertables and ensure it's properly indexed for optimal performance.
</Warning>

### Step 3: Insert Time-Series Data

Insert data just like you would with any PostgreSQL table:

```sql theme={null}
-- Insert sample sensor readings
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, location) VALUES
    (NOW() - INTERVAL '1 hour', 1, 22.5, 45.0, 'living_room'),
    (NOW() - INTERVAL '45 minutes', 1, 22.8, 44.5, 'living_room'),
    (NOW() - INTERVAL '30 minutes', 2, 19.2, 52.0, 'bedroom'),
    (NOW() - INTERVAL '15 minutes', 2, 19.5, 51.5, 'bedroom'),
    (NOW(), 3, 15.8, 68.0, 'outside');

-- Insert bulk data efficiently
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, location)
SELECT 
    generate_series(NOW() - INTERVAL '7 days', NOW(), INTERVAL '5 minutes') AS time,
    (random() * 3 + 1)::INTEGER AS sensor_id,
    (random() * 10 + 15)::DOUBLE PRECISION AS temperature,
    (random() * 30 + 40)::DOUBLE PRECISION AS humidity,
    CASE (random() * 2)::INTEGER 
        WHEN 0 THEN 'indoor'
        ELSE 'outdoor'
    END AS location;
```

### Step 4: Query Time-Series Data

TimescaleDB provides powerful functions for time-series analysis:

```sql theme={null}
-- Get latest readings per sensor
SELECT DISTINCT ON (sensor_id) 
    sensor_id, 
    time, 
    temperature, 
    humidity, 
    location
FROM sensor_data 
ORDER BY sensor_id, time DESC;

-- Calculate hourly averages using time_bucket
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity
FROM sensor_data 
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour, sensor_id
ORDER BY hour DESC, sensor_id;

-- Find temperature trends over the last week
SELECT 
    time_bucket('1 day', time) AS day,
    location,
    MIN(temperature) AS min_temp,
    MAX(temperature) AS max_temp,
    AVG(temperature) AS avg_temp
FROM sensor_data 
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY day, location
ORDER BY day DESC;
```

## Essential TimescaleDB Functions

### time\_bucket()

The most important function for time-series analysis - groups data into time intervals:

```sql theme={null}
-- 15-minute intervals
SELECT time_bucket('15 minutes', time) AS bucket, AVG(temperature)
FROM sensor_data 
GROUP BY bucket 
ORDER BY bucket;

-- Daily intervals with offset
SELECT time_bucket('1 day', time, '6 hours'::INTERVAL) AS bucket, COUNT(*)
FROM sensor_data 
GROUP BY bucket;
```

### first() and last()

Get the first or last value in a time range:

```sql theme={null}
-- Get first and last temperature reading per day
SELECT 
    time_bucket('1 day', time) AS day,
    first(temperature, time) AS first_temp,
    last(temperature, time) AS last_temp
FROM sensor_data 
GROUP BY day 
ORDER BY day;
```

### Continuous Aggregates

Pre-compute common queries for faster performance:

```sql theme={null}
-- Create a continuous aggregate for hourly averages
CREATE MATERIALIZED VIEW hourly_sensor_avg AS
SELECT 
    time_bucket('1 hour', time) AS hour,
    sensor_id,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity
FROM sensor_data 
GROUP BY hour, sensor_id;

-- Query the pre-computed view
SELECT * FROM hourly_sensor_avg 
WHERE hour > NOW() - INTERVAL '24 hours'
ORDER BY hour DESC;
```

## Common Use Cases

<CardGroup cols={2}>
  <Card title="IoT Sensor Data" icon="thermometer">
    Store and analyze readings from temperature, humidity, and other sensors with automatic time-based partitioning.
  </Card>

  <Card title="Application Metrics" icon="chart-line">
    Monitor application performance, response times, and system metrics with efficient time-series queries.
  </Card>

  <Card title="Financial Data" icon="chart-candlestick">
    Track stock prices, trading volumes, and market data with millisecond precision.
  </Card>

  <Card title="Log Analytics" icon="file-lines">
    Store and analyze application logs, system events, and audit trails over time.
  </Card>
</CardGroup>

## Learn More

This guide covers the basics of using TimescaleDB in Relyt ONE. For comprehensive documentation on advanced features, best practices, and detailed API references, visit the official TimescaleDB documentation:

<Card title="TimescaleDB Official Documentation" icon="book-open" href="https://docs.timescale.com/">
  Explore advanced features like data retention, compression, continuous aggregates, and performance optimization.
</Card>

<Tip>
  **Getting Started**: Begin with simple hypertables and basic queries. As your data grows, explore advanced features like continuous aggregates and compression in the [official TimescaleDB docs](https://docs.timescale.com/).
</Tip>