Django SQLite3 ExtractWeek returns week one instead of the last week of the year.

[Daniel Mössner]

I'm using: SQLite3 and Django 3.03

I've got the following query:

self.changes
            .annotate(week=ExtractWeek('date'),
                      year=ExtractYear('date'))
            .values('year', 'week')
            .annotate(balance=Avg('balance'))
            .values('year', 'week', 'balance', 'date')
            .order_by('year', 'week')

The Change model looks like this:

class Change(models.Model):
    account = models.ForeignKey(Account, on_delete=models.CASCADE, related_name="changes")
    date = models.DateTimeField()
    category = models.ForeignKey(Category, related_name="changes", null=True,
                                 on_delete=models.SET_NULL)
    description = models.TextField(blank=True)
    change = models.DecimalField(decimal_places=2, max_digits=15)
    # query optimization
    balance = models.DecimalField(max_digits=15, decimal_places=2, null=True, blank=True)

I've got a change with the following date:

datetime.datetime(2019, 12, 30, 15, 12, tzinfo=<UTC>)

Now the query turns this date into:

{'week': 1, 'year': 2019, 'balance': Decimal('7488.74000000000')}

That makes no sense, as the week should be 52 or 53.

I tried the following:

select *, strftime('%W', date) from banking_change where id=2742 limit 1;
returns week: 52

datetime(2019, 12, 30, 15, 12, tzinfo=pytz.utc).strftime('%W')
returns week: 52

It doesn't matter if the datetime is localized to Europe/Berlin before.

Why does django say that the week is 1? Seems like a bug to me.

[Mariusz Felisiak]

ExtractWeek returns a week number week based on ​ISO-8601, it works correctly.