Use Timescaledb With Django To Analyze Timeseries Data

TimeScaleDB is an open-source database designed specifically for storing and working with time-series data. It is built on top of PostgreSQL and provides additional features and capabilities for efficiently storing and querying large amounts of time-series data.

If you want to track sensor data from any IOT device for example arduino or raspberry pi etc in Django, you should definitely use timescaledb with your postgresql database. 

In this article, we will cover how to use TimeScaleDB with Django, a popular web framework for Python.

Prerequisites

Before getting started, make sure that you have the following installed on your machine:

• Python 3.x
• Django 2.x or higher
• PostgreSQL
• psycopg2 (PostgreSQL adapter for Python)
• TimescaleDB

Setting up TimeScaleDB

To set up TimeScaleDB, follow these steps:

3. Install PostgreSQL and create a new database.
4. Install the TimescaleDB extension. You can follow this link if you are on ubuntu :
   https://docs.timescale.com/install/latest/self-hosted/installation-debian/
   If you are on another operating system (such as windows or mac os), you should choose so from the left side menu (sidebar) on the timescaledb documentation page.
5. Next, create a new Django project and app:

django-admin startproject myproject
cd myproject
python manage.py startapp myapp

4. Install ‘django-timescaledb’ by running the following command:

   pip install django-timescaledb

4. Add ‘timescaledb’ to the INSTALLED_APPS list in myproject/settings.py.
5. Set up the database connection in myproject/settings.py by adding the following lines:

DATABASES = {
    'default': {
        'ENGINE': 'timescale.db.backends.postgresql',
        'NAME': 'mydatabase',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '',
    }
}

Note : If you want to use postgis you can use the engine ‘timescale.db.backends.postgis’

Replace ‘mydatabase’, ‘myuser’, and ‘mypassword’ with the actual name, username, and password of your database.

6. Run the following command to create the necessary tables in the database:

python manage.py migrate

Creating a TimeSeriesModel

To store time-series data in TimeScaleDB, we will need to create a TimeSeriesModel. A TimeSeriesModel is a Django model that is designed specifically for storing time-series data.

To create a TimeSeriesModel, follow these steps:

1. Create an abstract model by using the following code:

   
   
   
   from django.db import models
   
   
   from timescale.db.models.fields import TimescaleDateTimeField
   from timescale.db.models.managers import TimescaleManager
   from django.utils.timezone import now
   
   
   class TimescaleModel(models.Model):
   
       """
   
   
       A helper class for using Timescale within Django, has the TimescaleManager and
       TimescaleDateTimeField already present. This is an abstract class it should
       be inheritted by another class for use.
   
   
       """
   
   
   
       time = TimescaleDateTimeField(interval="1 day",default=now)
       objects = TimescaleManager()
   
       class Meta:
           abstract = True
   
   
   
   

2. Inherit from the TimescaleModel. A hypertable will automatically be created.
3. Create a new model by subclassing TimescaleModel:

class TemperatureReading(TimescaleModel):
    temperature = models.FloatField()
    location = models.CharField(max_length=255)

The TemperatureReading model has two fields: temperature, which is a floating-point field for storing the temperature reading, and location, which is a character field for storing the location of the temperature reading.

3. Run the following command to create the necessary tables in the database:

python manage.py makemigrations
python manage.py migrate

Inserting Time-Series Data To Timescaledb

Now that you have set up TimeScaleDB and created a TimeSeriesModel for storing time-series data, you can start inserting data into the model.

To insert data into the TemperatureReading model, you can use the TemperatureReading.objects.create() method, passing in the necessary data as keyword arguments:

TemperatureReading.objects.create(temperature=72.5, location='San Francisco')
TemperatureReading.objects.create(temperature=61.2, location='New York')
TemperatureReading.objects.create(temperature=58.7, location='Seattle')

You can also insert multiple records at once using the TemperatureReading.objects.bulk_create() method:

readings = [
    TemperatureReading(temperature=72.5, location='San Francisco'),
    TemperatureReading(temperature=61.2, location='New York'),
    TemperatureReading(temperature=58.7, location='Seattle'),
]
TemperatureReading.objects.bulk_create(readings)

Querying Time-Series Data From Timescaledb

TimeScaleDB provides a number of additional query functions for efficiently querying time-series data. Some of the commonly used query functions are:

• TimeSeriesModel.objects.last(): Returns the last record in the table.
• TimeSeriesModel.objects.first(): Returns the first record in the table.
• TimeSeriesModel.objects.at_time(time): Returns the record at a specific time.
• TimeSeriesModel.objects.between_time(start_time, end_time): Returns all records between two times.

For example, to get the last temperature reading in the TemperatureReading model, you can use the following code:

last_reading = TemperatureReading.objects.last()
print(last_reading.temperature)

You can also use the Django ORM’s filter() method to filter the results of your queries. For example, to get all temperature readings from San Francisco, you can use the following code:

sf_readings = TemperatureReading.objects.filter(location='San Francisco')
for reading in sf_readings:
    print(reading.temperature)

Conclusion

In this article, we covered how to work with TimeScaleDB and Django to store and query time-series data. We learned how to set up TimeScaleDB, create a TimeSeriesModel, and insert and query time-series data. With these skills, you should be able to easily integrate TimeScaleDB into your Django projects and start working with time-series data efficiently.

Further Steps: