Hey. I attempted to use this adapter with Azure postgres offering where only timescale 1.7.4 is available. Sadly it fails right out the gate due to the meta table having a different name in the older version (timescaledb_information.hypertable instead of timescaledb_information.hypertables).

So my question is, do you have any plans to make this adapter more dynamic and allow it to support the older version too? Or would you be willing to accept patches which add support for this? As we need this I am willing to take on the effort required and want to know which direction to take (either patching and contributing back upstream or just forking it).

• Can take inspiration from django-pgviews

I am in no hurry for this feature, but it would be nice to have.

Hi,
I'm new to TimescaleDb and I'm starting to use it in a Django project.

I'm interested to use django-timescaledb, I would like to use the partitioning_column option of the create_hypertable function but it seems not supported yet, right?

I could be interested to make a pull request to add this support.
I was thinking to implement it with these changes:

• add a new set of models fields (like TimescalePartinioningCharField, TimescalePartinioningIntegerField, TimescalePartinioningForeignKeyField)
• modify _create_hypertable functions (postgis / postgresql ) and related functions calling it to support this new models fields

Appreciate if there was a parameter that would set time=NOW() automatically on INSERT

According to the official documentation, the functions should be after the keyword "SELECT".

I wrote:

ranges = (datetime.now() - timedelta(days=3), datetime.now())
res = Metric.timescale\
    .filter(pool=pool, time__range=ranges)\
    .values('time', 'value')

And I've got this SQL:

SELECT "graphs_volume24hoursmetric"."time", "graphs_volume24hoursmetric"."value"
FROM "graphs_volume24hoursmetric"
WHERE ("graphs_volume24hoursmetric"."pool_id" = c6e6b7bd-1f0e-4766-89ed-56bc592742ed AND "graphs_volume24hoursmetric"."time" BETWEEN 2023-03-02 10:58:03.333117+00:00 AND 2023-03-05 10:58:03.333130+00:00)

This query outputs all the points that were created after 3 days.

Then I've used time_bucket_gapfill according to the documentation in the README.
Wrote:

ranges = (datetime.now() - timedelta(days=3), datetime.now())
res = Volume24HoursMetric.timescale\
    .filter(pool=pool, time__range=ranges)\
    .time_bucket_gapfill('time', '1 day', ranges[0], ranges[1])\
    .annotate(value=Avg('value'))\
    .values('time', 'value'

Got:

SELECT "graphs_volume24hoursmetric"."time", AVG("graphs_volume24hoursmetric"."value") AS "value"
FROM "graphs_volume24hoursmetric" WHERE ("graphs_volume24hoursmetric"."pool_id" = c6e6b7bd-1f0e-4766-89ed-56bc592742ed AND "graphs_volume24hoursmetric"."time" BETWEEN 2023-03-02 11:07:24.915758+00:00 AND 2023-03-05 11:07:24.915784+00:00) 
GROUP BY time_bucket_gapfill(INTERVAL 1 day, "graphs_volume24hoursmetric"."time", 2023-03-02 11:07:24.915758+00:00, 2023-03-05 11:07:24.915784+00:00), "graphs_volume24hoursmetric"."time"

Hoping to get 3 points, I get some bullshit out of 40 points.
According to the official documentation, you need to use the function after the 'SELECT" keyword, and not after "GROUP BY". In my case, aggregation should occur in the interval of 1 day, not 40.

Time buckets are usually used together with GROUP BY to aggregate data. But you can also run time_bucket on a single time value.

Hi guys, any plan to support
https://github.com/timescale/timescaledb-toolkit/blob/main/docs/lttb.md ?

It would be very useful in the project I'm working on.

when I tried to create tsdb table with name longer than 58 symbols I've got an exception:

...........
  File "/venv/lib/python3.12/site-packages/pgtrigger/migrations.py", line 420, in create_model
    super().create_model(model)
  File "/venv/lib/python3.12/site-packages/timescale/db/backends/postgresql/schema.py", line 131, in create_model
    self._create_hypertable(model, field)
  File "/venv/lib/python3.12/site-packages/timescale/db/backends/postgresql/schema.py", line 95, in _create_hypertable
    self._drop_primary_key(model)
  File "/venv/lib/python3.12/site-packages/timescale/db/backends/postgresql/schema.py", line 85, in _drop_primary_key
    self.execute(sql)
  File "/venv/lib/python3.12/site-packages/pgtrigger/migrations.py", line 404, in execute
    return super().execute(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/django/db/backends/base/schema.py", line 202, in execute
    cursor.execute(sql, params)
  File "/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/venv/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 737, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: constraint "slm_metrics_optimizer_foo_123456789_123456789_123456789_123c369" of relation "slm_metrics_optimizer_foo_123456789_123456789_123456789_123c369" does not exist

it's happened due to table/field/constraint name length limitation (63 bytes), and actual pkey name in that case is slm_metrics_optimizer_foo_123456789_123456789_123456789_12_pkey

The time_bucket function by default operates in UTC time - which has implications if you're attempting to to do daily time buckets with data from a specific timezone.

Per the timescale docs, the best way to adjust for this is with the the offset or origin parameters to the time_bucket function.

Relevant Doc: https://docs.timescale.com/api/latest/hyperfunctions/time_bucket/

Hi, I was integrating timescaledb into my project and Django can't find the 'timescaledb_information.hypertables' schema.
The complete exception is this:
`Operations to perform:
Apply all migrations: apuestas
Running migrations:
Applying apuestas.0001_initial... OK
Applying apuestas.0002_initial... OK
Applying apuestas.0003_auto_20210628_2210... OK
Applying apuestas.0004_auto_20210628_2224... OK
Applying apuestas.0005_auto_20210704_2321...Traceback (most recent call last):
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedTable: relation "timescaledb_information.hypertables" does not exist
LINE 1: SELECT EXISTS ( SELECT * FROM timescaledb_information.hypert...
^
QUERY: SELECT EXISTS ( SELECT * FROM timescaledb_information.hypertables WHERE hypertable_name = 'apuestas_candado')
CONTEXT: PL/pgSQL function inline_code_block line 1 at IF

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "manage.py", line 22, in
main()
File "manage.py", line 18, in main
execute_from_command_line(sys.argv)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/core/management/init.py", line 419, in execute_from_command_line
utility.execute()
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/core/management/init.py", line 413, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv
self.execute(*args, **cmd_options)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute
output = self.handle(*args, **options)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/core/management/base.py", line 89, in wrapped
res = handle_func(*args, **kwargs)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 244, in handle
post_migrate_state = executor.migrate(
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 227, in apply_migration
state = migration.apply(state, schema_editor)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/migrations/migration.py", line 126, in apply
operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/migrations/operations/fields.py", line 104, in database_forwards
schema_editor.add_field(
File "/home/david/banca_central/venv/lib/python3.8/site-packages/timescale/db/backends/postgresql/schema.py", line 129, in add_field
self._create_hypertable(model, field, True)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/timescale/db/backends/postgresql/schema.py", line 81, in _create_hypertable
self._assert_is_not_hypertable(model)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/timescale/db/backends/postgresql/schema.py", line 60, in _assert_is_not_hypertable
self.execute(sql)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/backends/base/schema.py", line 145, in execute
cursor.execute(sql, params)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
return super().execute(sql, params)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/home/david/banca_central/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: relation "timescaledb_information.hypertables" does not exist
LINE 1: SELECT EXISTS ( SELECT * FROM timescaledb_information.hypert...
^
QUERY: SELECT EXISTS ( SELECT * FROM timescaledb_information.hypertables WHERE hypertable_name = 'apuestas_candado')
CONTEXT: PL/pgSQL function inline_code_block line 1 at IF`
Thanks.

I have forked your project and added some extra functionality for my own purposes but also included an example of the time_bucket, time_bucket_gapfil, and histogram functions. They are rough but currently work as expected, I am working on testing currently but just wondering if you want any of it? - https://github.com/bencleary/django-timescaledb

Happy Holidays!
Ben

Hi all and thanks for this project.

I was wondering why TimescaleModel does not use the field as primary_key . Is this a choice left open to who implements his schema? If so, it would be better to integrate in documentation.

It makes sense: if I want all my iot endpoints writing in one big table the "time" field must not be a primary key, but if I have one table for each endpoint (this is my case now) setting the "time" filed as primary_key shoudl be the right choice.

What do you think?

TIA

The current implementation of TimeBucket only allows the use of the required arguments (bucket_width and ts).

It would be nice if the remaining arguments could be used as well with the package.
The available argument combinations are as follow:

• time_bucket(bucket_width, ts)
• time_bucket(bucket_width, ts, offset)
• time_bucket(bucket_width, ts, origin)
• time_bucket(bucket_width, ts, timezone, origin, offset)

Just worth to noting that old migrations from 0.2.3 won't work as the TimescaleDateTimeField was moved. There are probably not that many using this package since that version, but could be worth mentioning.

Hello there, would it be a good idea to provide a Django Admin widget/form to provide a time-series plot?
We could use something similar to pandas/matplotlib or something even better.
Let's have some brainstorming here.

Hey guys. Good work!

But why I need extra id from Django standart ORM?
('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),

If I want a composite PK: time+id, I will have to use raw sql, because it's not recomended use composite PK in Django ORM?

Could you please share your thoughts about it?

In sql_add_hypertable string it is used partition_column as variable to be replaced with the name of the column containing time, I suggest to rename this variable in something like time_column since partition_column seems related to partitioning_column option in create_hypertable function.

In the case I can provide a pull request for it

Docs

• https://docs.timescale.com/latest/using-timescaledb/distributed-hypertables
• https://docs.timescale.com/latest/api#add_dimension

django.db.utils.InternalError: cannot drop constraint shop_order_pkey on table shop_order because other objects depend on it

DETAIL: constraint shop_reviews_order_id_e2796d7b_fk_shop_order_id on table shop_reviews depends on index shop_order_pkey
constraint shop_orderitems_order_id_f7d02868_fk_shop_order_id on table shop_orderitems depends on index shop_order_pkey

Hi,
I am planning to convert my postgreSQL to a timescaledb and use this awesome package.
I just wonder if it is absolutely necessary to rename the existing field to "time". Is there any way I can set a variable to specify the current field name.

Hi!

TimescaleDB offers a function for linear interpolation: interpolate() Documentation.

As django-timescaledb does not support this feature yet, I tried integrating the the functionality, however I am facing an issue with providing the optional arguments prev and next, which are needed, if you want to interpolate the values for the first and last bucket in the desired range.

The idea was to add the following Interpolate class to the expressions.py:

class Interpolate(models.Func):
    function = 'interpolate'
    name = 'interpolate'

    def __init__(self, expression, prev, next, *args, **kwargs):
        super().__init__(expression, prev, next, *args, **kwargs) # naive approach :)

And use it like this:

# set range
start = timestamp.replace(hour=9, minute=0, second=0)
end = timestamp.replace(hour=16, minute=0, second=0)

# lookup queries for data _before_ and _after_ the gapfill range
prev = Metric.timescale.filter(time__lte=start).order_by('-time')[0]
next = Metric.timescale.filter(time__gte=end).order_by('time')[0]

metrics = (Metric.timescale
    .values('time', 'temperature')
    .annotate(hour=TimeBucketGapFill('time', '1 hour', start, end, datapoints=None))
    .annotate(avg=Avg('temperature'))
    .annotate(interpolate=Interpolate(Avg('temperature'), prev=prev, next=next))
    .values('hour', 'avg', 'interpolate')
    .order_by('hour')
    .distinct())

However, the prev and next queries are evaluated into objects (Metric object (1) and Metric object (5)), which is obviously not compatible:

SELECT DISTINCT 
    time_bucket_gapfill(INTERVAL 1 hour, "metrics_metric"."time", 2022-07-05 09:00:00.896143+00:00, 2022-07-05 16:00:00.896143+00:00) AS "hour",
    AVG("metrics_metric"."temperature") AS "avg",
    interpolate(AVG("metrics_metric"."temperature"), Metric object (1), Metric object (5)) AS "interpolate"
FROM "metrics_metric"
GROUP BY 
    "metrics_metric"."time",
    "metrics_metric"."temperature",
    time_bucket_gapfill(INTERVAL 1 hour, "metrics_metric"."time", 2022-07-05 09:00:00.896143+00:00, 2022-07-05 16:00:00.896143+00:00)
ORDER BY "hour" ASC

This results in an error:

File "/home/<name>/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: can't adapt type 'Metric'

In the interpolate() Documentation the prev and next are sub-select statements, using the Python ORM they are getting evaluated "beforehand".

Do you have any idea, what should adapted in order to get it to work?

thanks & br
Chris :)

This isn't an error caused by this backend, but it is one which people may come across when using Django and TimescaleDB with hypertable compression and may also affect your roadmap plans for managing compression through this engine.

We've enabled compression on one of our hypertables using a Django migration using RunSQL commands. We did this just to ease deployment and so that we can more easily test with compression.

An unexpected consequence of this was errors in several of our pytests, the root cause being django.db.backends.postgresql.operations.sql_flush() truncating every table at the end of some tests, called by django.test.testcases.TransactionTestCase._fixture_teardown().

The problem is that our (now compressed) hypertable has a foreign key relation back to one of our other tables, and with compression enabled it seems that there is now always at least one chunk created in the hypertable, which has that same foreign key (chunks are tables after all) but because the chunk is not in the list of tables to truncate, postgres errors saying that it won't get

Where as before it seems that at least during the minimal unit tests the hypertable itself contained all the data we're inserting in each test.

This presented as:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "_compressed_hypertable_4" references "other_table".
HINT:  Truncate table "_compressed_hypertable_4" at the same time, or use TRUNCATE ... CASCADE.

Short-term our workaround has been to:

1. Stop using django_db(transaction=True) on some of our tests (turns out they didn't need this anyway)
2. Skip some of our older migration tests which made use of django_test_migrations which was also using TransactionTestCase internally

Although obviously we want to get back to covering those migrations, and writing new migration tests in future.

I think the correct long-term solution is for us to submit a PR to django to add a way to always set allow_cascade=True in _fixture_teardown(), this would mean no changes to either the postgres backend or this backend.

https://docs.timescale.com/latest/using-timescaledb/reading-data#locf

Should probably be integrated with time_bucket_gapfill

    metrics.timescale.time_bucket_gapfill('time', '1 day', ranges[0], ranges[1], datapoints=240, locf=True)

Hi, thanks for this project!

IMHO time_bucket() function doesn't work... or I can't understand how my usage is wrong. Code to reproduce the error:

class Metrics(models.Model):

    time = TimescaleDateTimeField(primary_key=True, interval="15 days")
    data = models.JSONField()

    objects = TimescaleManager()

    class Meta:
        db_table = 'metric'
        verbose_name = _('metric')
        verbose_name_plural = _('metrics')
        abstract = True

class MyMetrics(Metrics):
     pass

In Django shell:

In [1]: from myapp.models import MyMetrics
In [2]: qs = MyMetrics.objects.filter(time__range=('2021-08-30', '2021-08-31')).time_bucket("time", "1 hour")
in [3]: qs

[...]
FieldError: Expression contains mixed types: CharField, TimescaleDateTimeField. You must set output_field.

With current implementation (create_hypertable with migrate_data => true), if table is large could be locked for long time and could also cause "deadlock" for certain condition, as the below quote from doc suggest:

WARNING:The use of the migrate_data argument to convert a non-empty table can lock the table for a significant amount of time, depending on how much data is in the table. It can also run into deadlock if foreign key constraints exist to other tables.

If you would like finer control over index formation and other aspects of your hypertable, follow these migration instructions instead.

When converting a normal SQL table to a hypertable, pay attention to how you handle constraints. A hypertable can contain foreign keys to normal SQL table columns, but the reverse is not allowed. UNIQUE and PRIMARY constraints must include the partitioning key.

The deadlock is likely to happen when concurrent transactions simultaneously try to insert data into tables that are referenced in the foreign key constraints and into the converting table itself. The deadlock can be prevented by manually obtaining SHARE ROW EXCLUSIVE lock on the referenced tables before calling create_hypertable in the same transaction, see PostgreSQL documentation for the syntax.

As described in README:

If you already have a table and want to just add a field you can add the TimescaleDateTimeField to your model. This also triggers the creation of a hypertable.

this is not trigger migration to hypertable.

I digged into the code and found out that "TimescaleSchemaEditor" only overwrites def create_model(self, model):, I don't see how the above mention in "README" would work.

Hello,
django timescaledb seems to work fine. just 2 questions.
Timescaledb mentions no unique / primary key constraint is needed.
using your code i see that the primary key gets removed (e.g. no unique constraints, no primary keys) and again: that all seems to work fine.
However: django is quite explicit it needs a unique identifier based on a single column.
see for example here: https://docs.djangoproject.com/en/5.0/topics/db/models/#automatic-primary-key-fields

How is this uniqueness covered when using timescaledb ?

other question:
in order to have some speed letting django identify the instances(i tested on django admin with ~ 40 million rows) i created an index manually (raw sql) on the id column. That brings good performance. However I did not see id index management in your code. What are your thoughts, recommendations to get a good speed finding instances (for example in django admin) ?

Settings is not found in _create_hypertable while python manage.py migrate.
I am using custom named settings from django cookiecutter (base.py, local.py, production.py).

File "manage.py", line 31, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/base.py", line 402, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/base.py", line 448, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/base.py", line 96, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/commands/migrate.py", line 349, in handle
    post_migrate_state = executor.migrate(
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/migration.py", line 130, in apply
    operation.database_forwards(
  File "/usr/local/lib/python3.8/dist-packages/django/db/migrations/operations/fields.py", line 108, in database_forwards
    schema_editor.add_field(
  File "/usr/local/lib/python3.8/dist-packages/timescale/db/backends/postgis/schema.py", line 139, in add_field
    self._create_hypertable(model, field, True)
  File "/usr/local/lib/python3.8/dist-packages/timescale/db/backends/postgis/schema.py", line 101, in _create_hypertable
    if should_migrate and getattr(settings, "TIMESCALE_MIGRATE_HYPERTABLE_WITH_FRESH_TABLE", False):
NameError: name 'settings' is not defined

If models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID') exits, how do u create hypertable?

I'm a newbie trying to use timescaledb with my django project. I'm having trouble running the initial migration despite trying the two implementation methods listed in point 3 of your quickstart.
From the traceback, it looks like a problem with userauth.models.CustomUser, which inherits from core.models.CreationModificationDateBase, which inherits TimescaleDateTimeField/TimescaleModel.
I can't figure out what I'm missing so I'm hoping to get some help.

# traceback
Operations to perform:
  Apply all migrations: account, admin, auth, contenttypes, sessions, sites, socialaccount, userauth
Running migrations:
  Applying userauth.0001_initial...Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.DatabaseError: cannot create a unique index without the column "time" (used in partitioning)


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/code/manage.py", line 22, in <module>
    main()
  File "/code/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 244, in handle
    post_migrate_state = executor.migrate(
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/executor.py", line 227, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/migration.py", line 126, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.9/site-packages/django/db/migrations/operations/models.py", line 92, in database_forwards
    schema_editor.create_model(model)
  File "/usr/local/lib/python3.9/site-packages/timescale/db/backends/postgresql/schema.py", line 120, in create_model
    self._create_hypertable(model, field)
  File "/usr/local/lib/python3.9/site-packages/timescale/db/backends/postgresql/schema.py", line 98, in _create_hypertable
    self.execute(sql)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 145, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DatabaseError: cannot create a unique index without the column "time" (used in partitioning)

# core.models.py version 1

from django.db import models
from django.utils.translation import gettext_lazy as _
from timescale.db.models.fields import TimescaleDateTimeField
from timescale.db.models.managers import TimescaleManager
from timescale.db.models.models import TimescaleModel


class CreationModificationDateBase(models.Model):
    """Abstract base class with a creation and modification date and time."""
    time = TimescaleDateTimeField(_("Creation Date and Time"), auto_now_add=True, interval="1 day")
    modified = models.DateTimeField(_("Modification Date and Time"), auto_now=True,)

    objects = models.Manager()
    timescale = TimescaleManager()

    class Meta:
        abstract = True

# core.models.py version 2

class CreationModificationDateBase(TimescaleModel):
    """Abstract base class with a creation and modification date and time."""
    modified = models.DateTimeField(_("Modification Date and Time"), auto_now=True,)

    class Meta:
        abstract = True

# userauth.models.py

from django.db import models
from django.contrib.auth.models import AbstractUser
from django.core.validators import RegexValidator
from django.utils.translation import gettext_lazy as _
from django_countries.fields import CountryField
import sys
sys.path.append("..") # Adds higher directory to python modules path.
from core.models import CreationModificationDateBase


class CustomUser(AbstractUser, CreationModificationDateBase):
    display_name = models.CharField(verbose_name=_("Display name"), max_length=30, help_text=_("Will be shown e.g. when commenting"))
    date_of_birth = models.DateField(verbose_name=_("Date of birth"), blank=True, null=True)
    address1 = models.CharField(verbose_name=_("Address line 1"), max_length=1024, blank=True, null=True)
    address2 = models.CharField(verbose_name=_("Address line 2"), max_length=1024, blank=True, null=True)
    zip_code = models.CharField(verbose_name=_("Postal Code"), max_length=12, blank=True, null=True)
    city = models.CharField(verbose_name=_("City"), max_length=1024, blank=True, null=True)
    country = CountryField(blank=True, null=True)
    phone_regex = RegexValidator(regex=r"^\+(?:[0-9]●?){6,14}[0-9]$", message=_("Enter a valid international mobile phone number starting with +(country code)"))
    mobile_phone = models.CharField(validators=[phone_regex], verbose_name=_("Mobile phone"), max_length=17, blank=True, null=True)
    additional_information = models.CharField(verbose_name=_("Additional information"), max_length=4096, blank=True, null=True)
    photo = models.ImageField(verbose_name=_("Photo"), upload_to='photos/', default='photos/default-user-avatar.png')

    class Meta:
        ordering = ['last_name']

    def __str__(self):
        return f"{self.username}: {self.first_name} {self.last_name}"

#Dockerfile

FROM python:3.9.6-slim-buster

# Set environment variables
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

# Set work directory
WORKDIR /code

# Install system packages required
RUN apt-get update --yes --quiet && apt-get install --yes --quiet --no-install-recommends \
    build-essential \
    libpq-dev \
    libmariadbclient-dev \
    libjpeg62-turbo-dev \
    zlib1g-dev \
    libwebp-dev \
 && rm -rf /var/lib/apt/lists/*

# Install dependencies
COPY Pipfile Pipfile.lock /code/
RUN pip install pipenv && pipenv install --system --dev

# Copy project
COPY . /code/

# docker-compose.yml
version: '3.8'

services:
    web:
        build: .
        command: python manage.py runserver 0.0.0.0:8000 --settings=IT.settings.dev
        container_name: web
        restart: unless-stopped
        volumes:
            - .:/code
        ports:
            - 8000:8000
        env_file:
            - .env/.dev
        depends_on:
            - timescaledb
            - redis

    timescaledb:
        image: timescale/timescaledb:2.4.2-pg13
        container_name: timescaledb
        restart: unless-stopped
        ports:
            - 5432:5432
        env_file:
            - .env/.dev_db
        volumes:
            - timescaledbdata:/var/lib/postgresql/data
    redis:
        image: redis:6.2.5-alpine3.14
        container_name: redis
        restart: unless-stopped

volumes:
    timescaledbdata:

# Pipfile
[[source]]
url = "https://pypi.org/simple"
verify_ssl = true
name = "pypi"

[packages]
django = "~=3.2.7"
django-timescaledb = "~=0.2.10"
psycopg2-binary = "~=2.9.1"
django-allauth = "~=0.45.0"
django-countries = "*"
celery = "~=5.1.2"
redis = "~=3.5.3"
flower = "~=1.0.0"
pandas = "~=1.3.3"
pillow = "~=8.3.2"

[dev-packages]

[requires]
python_version = "3.9"

Hi!

With timescaledb 2.4 a new (experimental) feature was introduced, improving the exiting time_bucket functionality: time_bucket_ng [1].
Using that function it is now possible to create timebuckets with intervals such as months and years, as well as to set a custom timezone.

Would it be possible to support this new function?

[1] https://docs.timescale.com/api/latest/hyperfunctions/time_bucket_ng/#timescaledb-experimental-time-bucket-ng

Hi!

I'm trying to run a query with time_bucket_gapfill as follows:

MyModel.timescale.filter(time__range=ranges).time_bucket_gapfill('time', '1 day', start=ranges[0], end=ranges[1]).annotate(Count('temperature'))

However, since datapoints defaults to 240, it's not possible to pass None to the interval.

Last Updated - 17 Aug 2021

django-timescaledb

django-timescaledb aims to achieve following objectives

• feature rich integration of TimescaleDB with Django
• high-quality module - proper testing and documentation
• keeping the module active with future releases of TimescaleDB and Django
• clearly communicate - version supports for Django and TimescaleDB - along with LTS, deprecation/upgrade notes

Improvements/Feature Requests

• #22 Why not allow making "partition key" as primary key? [M.1]
• Why does the partition key have to be named "time"? [M.1]
• data retention policy [M.2]
• Compression policies [M.2]
• #12 Continuously aggregated views [M.2]
• additional partitioning key [M.2]

Milestone 1 [Basic usage]

• Create hypertable (for empty or non-empty tables) with single "time" based partition
• proper model.Manager that can be used to run queries
• proper testing framework
• proper documentation
• all features in this step should work seamlessly with Django's makemigrations and migrate commands, without user having to run single query himself
• APIs
  • DB backends (postgres and postgis) - TimescaleSchemaEditor
  • models.Model - TimescaleModel (abstract)
  • models.Manager - TimescaleManager (time_bucket, time_bucket_gapfill, histogram)
  • models.Field - TimescaleDateTimeField
  • models.Func - Interval, TimeBucket, TimeBucketGapFill
  • models.Aggregate - Histogram, Last, First

Milestone 2 [Advance features with manual management]

• have migrations operations for adding/removing/altering advanced features of TSDB, this has to be dropped in by the user in the migrations files manually
• support for "AggregateViews" - not sure how to implement it?
• additional partitioning key - not sure how to implement it?
• additional functions and/or queryset methods - "Drop chunks", "Percentile approximation", "Time-weighted average functions"
• new APIs
  • data retention policy - AddRetentionPolicy, AlterRetentionPolicy, RemoveRetentionPolicy
  • compression - AlterTableCompression, AddCompressionPolicy, RemoveCompressionPolicy

Milestone 3 [Advance features with automatic management]

• Have a parallel "migration system" like Django's
  • Model.TSMeta class - since Model.Meta class in Django is restrictive (cannot add new attributes, etc), need to have a parallel TSMeta class for storing "TSDB related meta" info (policies, etc) about the Model
  • makemigrations_ts command - to generate "migrations" files with "TSDB" migration operations
  • maketsmeta command - that goes through all migrations and outputs all TSMeta class based on "migration operations" added manually - tooling to migrate from M.2 -> M.3