Django annotate add interval to date

Question

I am using postgres database with Django 2.2. I need to combine date and time field in model and add an interval of one day in it. I have done first part like this.

Model.objects.annotate(
    end_dt=models.ExpressionWrapper(
        models.F('date') + models.F('end'),
        output_field=models.DateTimeField()
    )
)

Now I have to add 1 day to end_dt. I have done this with plain sql like this.

SELECT 
       "db_shift"."id",
       (("db_shift"."date" + "db_shift"."end") + INTERVAL '1 day') AS "end_dt"
FROM 
     "db_shift";

How can I achieve this using django ORM? Or is it even achievable?

what is the field type of date and end ? Is both are DateTimeField in models? Can you show the relevant parts of the models? — JPG
– JPG, Commented Jun 14, 2019 at 7:01

Nafees Anwar · Accepted Answer · 2019-06-14 07:22:17Z

15

After a bit of experimentation I have found that adding timedelta from datetime works just fine :)

import datetime


Model.objects.annotate(
    end_dt=models.ExpressionWrapper(
        models.F('date') + models.F('end') + datetime.timedelta(days=1),
        output_field=models.DateTimeField()
    )
)

answered Jun 14, 2019 at 7:22

Nafees Anwar

6,6783 gold badges28 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mourad Qqch Over a year ago

Won't work in case your interval depends on other table columns.

Mourad Qqch · Accepted Answer · 2024-12-12 12:28:32Z

5

For people coming here and looking for a way to add interval depending on a column from the table itself, you can proceed as following:

Edit 12 Dec. 2024: The snippet is for Postgresql

class MyModel(models.Model):
    quantity = ...
    created = ...


class Interval(Func):
    function = "INTERVAL"
    template = "(%(expressions)s * %(function)s '1' DAY)"

...

MyModel.objects.annotate(
    expiry_date=ExpressionWrapper(
        F("created") + Interval("quantity"),
        output_field=models.DateTimeField(),
    )
)

The code above will add N days to the created date field, where N is the quantity field.

edited Dec 12, 2024 at 12:28

answered Aug 26, 2023 at 7:54

Mourad Qqch

4791 gold badge7 silver badges19 bronze badges

2 Comments

Fenkiou Dec 12, 2024 at 10:18

Suggested template generates this SQL expression: (created + (quantity * INTERVAL '1' DAY)) AS expiry_date which doesn't work on MySQL. I modified it like that: %(function)s %(expressions)s DAY And it generates the SQL expression: (created + INTERVAL quantity DAY) AS expiry_date which seems to work on MySQL, don't know for other RDMS

Mourad Qqch Dec 12, 2024 at 12:27

This is for postgres, sorry I should have mentionned it.

Collectives™ on Stack Overflow

Django annotate add interval to date

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related