Django filter: different results in filtering between SQLite and PostgreSQL

Question

I have a relatively simple query running on a database table; the filter checks the value of 2 date fields and returns a queryset. The fields checked are authored_report_received and subbed_complete. If authored_report_received has a date set AND subbed_complete has no date set, then a result will be returned. If both these fields have a date set then no results are returned. (A simple calculation for the 'days outstanding' between any result returned and the current date is then made.) This works absolutely fine running locally on a SQLite database but when I upload to heroku to be used with a PostgreSQL database, the filter does not seem to work in that it seems to ignore any date value in the subbed_complete field. I do not understand why not. Is there a difference between the way SQLite and PostgreSQL handles the filters? I can't find any docs which indicate this.

# views.py
class EpgReportSubeditListView(LoginRequiredMixin, EpgUserPassesTestMixin, ListView):
    """EPG-only: create a list view of reports currently being subedited
    """
    model = Report
    template_name = 'tracker/epg_report_subedit.html'
    context_object_name = 'report_subedit'
    ordering = ['subeditor']

    def get_queryset(self):
        today = datetime.now().date()
        out = []
        for obj in (self.model.objects.filter(subbed_complete__isnull=True) and self.model.objects.filter(authored_report_received__isnull=False)):
            setattr(obj, 'days_diff', (today - obj.authored_report_received).days)
            out.append(obj)
        return out

# models.py
class Report(models.Model):
    """Define fields for Report object (representing a case report)
    """
    case = models.ForeignKey(Case, on_delete=models.CASCADE, related_name='reports')
    editor = models.ForeignKey(User, on_delete=False,
                               limit_choices_to={'groups__name': 'editors'},
                               related_name='editor', null=True)
    
    # editing workflow info
    authored_report_received = models.DateField(null=True, blank=True)
    subbed_complete = models.DateField(null=True, blank=True)
    subeditor = models.ForeignKey(User, on_delete=False,
                                  limit_choices_to={'groups__name': 'subeditors'},
                                  related_name='subeditor', null=True, blank=True)
    sent_to_editor = models.DateField(null=True, blank=True)
    edited = models.DateField(null=True, blank=True)
    reporter_queries = models.CharField(max_length=3,
                                        choices=Binary_Choices,
                                        default="n")
    sent_to_deskeditor = models.DateField(null=True, blank=True)
    taken_by_deskeditor = models.DateField(null=True, blank=True)
    deskeditor = models.ForeignKey(User, on_delete=False,
                                   limit_choices_to={'groups__name': 'deskeditors'},
                                   related_name='deskeditor', null=True, blank=True)
    deskedit_complete = models.DateField(null=True, blank=True)
    sent_to_counsel = models.DateField(null=True, blank=True)
    received_from_counsel = models.DateField(null=True, blank=True)
    date_editor_publish_approve = models.DateField(null=True, blank=True)

    def get_absolute_url(self):
        return reverse("epg_home")

    def __str__(self):
        return f'{self.case} | f: {self.filename}'

marcanuy · Accepted Answer · 2020-08-31 16:19:52Z

1

Sqlite and PostgreSQL handle dates differently. subbed_complete is a models.DateField field, but sqlite3:

does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

So you should test your code and adjust the query in an another environment with postgresql.

https://www.sqlite.org/datatype3.html

answered Aug 31, 2020 at 16:19

marcanuy

24.2k10 gold badges68 silver badges118 bronze badges

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

Collectives™ on Stack Overflow

Django filter: different results in filtering between SQLite and PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related