Question 385828 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use postgres database function with django queryset?

I have a django project which stores real-time data of a few devices. For this manner I have used timescaledb which is suitable for time series and it’s based on postgres. Timescale offers a few hyperfunctions which I need to use (specifically lttb which is used for downsampling data).

For example, this is one of the queries that I am looking to achieve:

SELECT time as timestamp, value as value, %s as device_id 
FROM unnest((SELECT lttb(timestamp, value, %s) 
FROM core_devicedata where device_id=%s and timestamp between %s and %s))

I can get result of this query as a raw query set by:

for data in DeviceData.objects.raw(query):
    ...

I have already tried raw sql queries using django. They work. The thing is they offer no filtering and ordering functionality since they do not return actual queryset. Instead, they return a raw query set. What I am trying to achieve is to run a query like below using only power of djagno orm.

SELECT time as timestamp, value as value, %s as device_id 
FROM unnest((SELECT lttb(timestamp, value, %s)

Any suggestions? If there is no way to do this with django orm itself, would please help me write a modular manager method for this manner? Like I should be able to filter or order only if I want.

Tags:

2

Answers


  1. 0

    Django or not, my guess is that you can unnest it inline. Let’s see some example:

    select lttb(time,price,5)->unnest() from crypto_ticks where symbol = 'BTC/USD' and time > now() - interval '1 month';
              ?column?
------------------------------------
 ("2024-04-09 13:23:29+00",70431)
 ("2024-04-17 16:08:07+00",59714.9)
 ("2024-04-22 23:15:59+00",67228.5)
 ("2024-05-01 08:24:03+00",56534.5)
 ("2024-05-07 14:37:03+00",63190.8)
(5 rows)

    Now, let’s include the symbol and group by it.

    tsdb=> select symbol, lttb(time,price,5)->unnest() from crypto_ticks where symbol = 'BTC/USD' and time > now() - interval '1 month' group by 1;
 symbol  |              ?column?
---------+------------------------------------
 BTC/USD | ("2024-04-09 13:23:29+00",70431)
 BTC/USD | ("2024-04-17 16:08:07+00",59714.9)
 BTC/USD | ("2024-04-22 23:15:59+00",67228.5)
 BTC/USD | ("2024-05-01 08:24:03+00",56534.5)
 BTC/USD | ("2024-05-07 14:37:38+00",63266.6)
(5 rows)

    Now, let’s unnest the values with ().*

    tsdb=> select symbol, (lttb(time,price,5)->unnest()).* from crypto_ticks where symbol = 'BTC/USD' and time > now() - interval '1 month' group by 1;
 symbol  |          time          |  value
---------+------------------------+---------
 BTC/USD | 2024-04-09 13:23:29+00 |   70431
 BTC/USD | 2024-04-17 16:08:07+00 | 59714.9
 BTC/USD | 2024-04-22 23:15:59+00 | 67228.5
 BTC/USD | 2024-05-01 08:24:03+00 | 56534.5
 BTC/USD | 2024-05-07 14:37:38+00 | 63266.6
    Login or Signup to reply.
  2. 0

    My first advice is that the ORM will only get you so far. When things start getting complicate it’s best to go the raw query route, otherwise you’ll spend more time fighting the ORM than solving your real business problems.

    You can add the filtering and ordering to the raw query with a WHERE clause and ORDER BY clause.

    You can write custom ORM SQL functions with Func() expresions. You can see some examples of timescaledb functions written for Django, in this case time_bucket, in the django-timescale library repo.

    You could define the function something like (this doesn’t work):

    class LTTB(Func):
    arity = 3
    function = "lttb"
    template = "(%(function)s(%(expressions)s)->unnest()).*"

    The problem is that custom functions require an output_field, since unnest returns 2 extra columns, I don’t know if the Django ORM supports that.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search