# Time Series Database

The time series database in this system is based on PostgreSQL's Timescale extension (opens new window), which can be used to store runtime status data of industrial control, IoT, and other devices, or to store massive time series data from other scenarios.

# Reference

This document is referenced from https://docs.timescale.com/getting-started/latest/create-hypertable/ (opens new window)

# Target Audience

The target audience for this document is: developers who need to use this system to store large amounts of time series data

# About Timescale Extension

Timescale extends PostgreSQL's time series and analytics capabilities, allowing you to build applications faster and scale scenarios better.

Official website: https://www.timescale.com/ (opens new window)
Docker image: https://hub.docker.com/r/timescale/timescaledb/ (opens new window)

# Running TimescaleDB

We are using a PostgreSQL image with the Timescale extension. For more detailed information, please refer to https://docs.timescale.com/self-hosted/latest/install/installation-docker/ (opens new window)

Docker image: https://hub.docker.com/r/timescale/timescaledb/ (opens new window) The current version is 14.7, Below is an example docker-compose configuration snippet

 database:
   image: timescale/timescaledb-ha:pg14-latest
   restart: always
   environment:
     - POSTGRES_DATABASE=platform
     - POSTGRES_USER=postgres
     - POSTGRES_PASSWORD=password
   volumes:
     - ${PWD}/runtime/database/data:/var/lib/postgresql/data
   healthcheck:
     test: ['CMD-SHELL', 'pg_isready -U postgres']
     interval: 10s
     timeout: 5s
     retries: 5
   ports:
     - 5432:5432
   networks:
     - scm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

You can directly download the complete docker-compose configuration file from https://github.com/muyantech/grails-docker (opens new window), and then run docker compose up -d database redis pgadmin.

WARNING

If you are currently using the grails-docker project, please backup the data directory ${PWD}/runtime/database/data before switching from the original postgresql image to the timescale/timescaledb-ha image.

# Creating TimescaleDB Extension

To create the TimescaleDB extension in PostgreSQL, you can use the following commands:

-- If the database hasn't been created yet, create it first
CREATE DATABASE platform WITH ENCODING='UTF8' OWNER=postgres;

-- Connect to the database
\c platform

-- Create the timescaledb extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Show active extensions to confirm
\dx

1
2
3
4
5
6
7
8
9
10
11

# Creating Tables with Timescale Extension

The TimescaleDB extension provides a special function create_hypertable to convert existing tables into hypertables,


-- Create table
CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  price DOUBLE PRECISION NULL,
  day_volume INT NULL
);

-- Create hypertable, convert stocks_real_time to a time series table, with time column as the time series column
SELECT create_hypertable('stocks_real_time','time');

-- If the table already exists, you can use the following command to migrate to a time series table
SELECT create_hypertable('stocks_real_time','time', migrate_data => true);

-- Create an index based on symbol and time columns
CREATE INDEX ix_symbol_time ON stocks_real_time (symbol, time DESC);

-- Create another regular table
CREATE TABLE company (
  symbol TEXT NOT NULL,
  name TEXT NOT NULL
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# Querying Data

TimeScaleDB uses standard PostgreSQL syntax for querying, in addition, TimescaleDB extension provides some special functions for querying time series data, such as the time_bucket function.

# Inserting Test Data

Prepare some test data, download sample data from the following address https://assets.timescale.com/docs/downloads/get-started/real_time_stock_data.zip (opens new window) Use the command unzip real_time_stock_data.zip to decompress
Import the sample data into the database: Go to the running Timescale Docker container and enter the psql console

# Copy the sample data to the running Timescale Docker container
docker cp tutorial_sample_tick.csv <running_container_id>:/tutorial_sample_tick.csv
docker cp tutorial_sample_company.csv <running_container_id>:/tutorial_sample_company.csv
# Enter the psql console
docker exec -it <running_container_id> bash
psql -U postgres

1
2
3
4
5
6

-- Import CSV files into stocks_real_time and company tables
\COPY stocks_real_time from './tutorial_sample_tick.csv' CSV HEADER;
\COPY company from './tutorial_sample_company.csv' CSV HEADER;

1
2
3

# Querying Data with Raw SQL

-- Select all stock data from the past four days
SELECT * FROM stocks_real_time srt
 WHERE time > now() - INTERVAL '4 days';

-- Select the 10 most recent trades for Amazon in order
SELECT * FROM stocks_real_time srt
 WHERE symbol='AMZN'
 ORDER BY time DESC, day_volume desc
 LIMIT 10;

-- Calculate the average trading price for Apple over the past four days
SELECT
  avg(price)
  FROM stocks_real_time srt
       JOIN company c ON c.symbol = srt.symbol
 WHERE c.name = 'Apple' AND time > now() - INTERVAL '4 days';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# Advanced Timescale SQL Functions

Refer to https://docs.timescale.com/getting-started/latest/query-data/ (opens new window)

first(): Find the earliest value within an aggregation group based on time
last(): Find the latest value within an aggregation group based on time
time_bucket(): Bucket data by any time interval and calculate aggregates within these intervals

-- Here's an example using time_bucket() on stock trading data to calculate the daily average price for each trading symbol over the past week.

SELECT
  time_bucket('1 day', time) AS bucket,
  symbol,
  avg(price)
  FROM stocks_real_time srt
 WHERE time > now() - INTERVAL '1 week'
 GROUP BY bucket, symbol
 ORDER BY bucket, symbol;

1
2
3
4
5
6
7
8
9
10

# Continuous Aggregates

TimescaleDB provides a feature called continuous aggregates, which automatically calculates aggregate data in the background without user intervention. This allows users to get faster response times when querying aggregate data without affecting the write speed of the original data.

When creating materialized views, you can use the WITH (timescaledb.continuous) option to indicate the creation of a continuous aggregate.

# Creating Continuous Aggregates


--- Query the highest price, lowest price, opening price, and closing price from daily stock trading data
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol
ORDER BY day DESC, symbol;

-- Create a continuous aggregate using the above SQL, storing the results in the stock_candlestick_daily view
-- Note that we use the WITH (timescaledb.continuous) option to indicate the creation of a continuous aggregate
CREATE MATERIALIZED VIEW stock_candlestick_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# Raw SQL Queries

If you need to use raw SQL for data queries during development, you can use the following methods:

In Service or Controller, inject javax.sql.DataSource, or directly use BeanHelper.getBean(application, "dataSource") to obtain
Use groovy.sql.Sql.rows or other methods for querying

For detailed API documentation of the Sql class, please refer to: https://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html (opens new window)

Example as follows:

import javax.sql.DataSource
import groovy.sql.Sql

Class StockService {

    # Inject DataSource
    DataSource dataSource

    def getStockCandlestickDaily() {
        Sql sql = new Sql(dataSource)
        String sqlString = "SELECT * FROM stock_candlestick_daily"
        List<GroovyRowResult> list = sql.rows(sqlString)
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# Further Reading

The above is just a simple introduction to TimescaleDB. For more detailed features, please refer to the following official documentation links:

Continuous Aggregates: https://docs.timescale.com/timescaledb/latest/how-to-guides/continuous-aggregates/ (opens new window)
Data Retention Policies: https://docs.timescale.com/timescaledb/latest/how-to-guides/data-retention/ (opens new window)

← 🔌 Plugin Development 📥 Data Import →