Handling Time Series Data On The Timescale Database

Timescale DB is an open source database and it can also be considered as Postgres DB enhancement. It is used for fast ingest and complex queries.

More specifically, compared to PostgreSQL, TimescaleDB exhibits:

• Scalable up to 20x higher inserts (no hiccups even at billions of rows)
• Ultra-fast queries, for time-based queries you get improvements ranging from 1.2x to over 14,000x
• 2000x faster deletes, it facilitates implementation of data retention policies that is very critical
• New time-centric functions, for easier manipulation of time-series in SQL

Hypertables are something that makes timescale different and unique from other databases

Hypertables are easy to manage tables and also behave as expected to users who are familiar with standard PostgreSQL tables. Due to this, you have identical SQL commands to create, alter, or delete (hyper)tables in TimescaleDB as in PostgreSQL. Despite consisting of many interlinked "chunk" tables, the hypertable automatically passes down any command made to its every chunk. 

Also Read: An Introduction To T SQL Server

How To Create a Hypertable

Creating a hypertable is a two-step process.

1. Create a standard table 

   CREATE TABLE conditions (
    time        TIMESTAMPTZ       NOT NULL,
    location    TEXT              NOT NULL,
    temperature DOUBLE PRECISION  NULL
   );

2. ALTER TABLE conditions
     ADD COLUMN humidity DOUBLE PRECISION NULL;

   The schema changes will automatically reach every chunk of the hypertable with the help of TimescaleDB.

DELETING A HYPERTABLE

DROP TABLE conditions;

SOME IMPORTANT FUNCTIONS THAT MAKE TIMESCALE DB FASTER FROM OTHER SQL DATABASES

Getting the number of data inserted in the past 10 hours

SELECT COUNT(*) FROM conditions WHERE time > NOW() - INTERVAL '10 hours';

Use this function to get information for a 15-min period for each location over the past 3 hours, ordered by time and temperature

SELECT time_bucket('15 minutes', time)

AS fifteen_min, location, COUNT(*), MAX(temperature)

AS max_temp, MAX(humidity)

AS max_hum FROM conditions WHERE time > NOW() - INTERVAL '3 hours' GROUP BY fifteen_min, location

ORDER BY fifteen_min DESC, max_temp DESC;

Also Read: Enhancing SQL query Performance

How many distinct locations with air conditioning  have reported data in the past day

SELECT COUNT(DISTINCT location) FROM conditions JOIN locations ON conditions.location = locations.location WHERE locations.air_conditioning = True AND time > NOW() - INTERVAL '1 day'

All the time series functions can be enabled only if you create a hypertable of your existing table otherwise you won't be able to use these functions.

There are many more important features in the timescale database such as Histogram, First Last, Time Bucket, etc.

You can visit the official documentation of the Timescale database for more.

Choose Oodles For SaaS App Development Services

We are a 360-degree SaaS app development company that focuses on building high-quality web and mobile applications using the latest tools, frameworks, and SDKs. Our development team analyzes your project requirements and formulates effective strategies to create performance-driven applications that maximize enterprise benefits. We have successfully completed several full-scale SaaS application development projects with a focus on next-gen technologies. For project-related queries, reach us out at [email protected].