strftime got None on sqlite

[gojuukaze]

Model:

from django.utils.timezone import now

class AdV2Log(models.Model):

    adv2_id = models.IntegerField()
    app_id = models.CharField(max_length=50)
    device_id = models.CharField(max_length=50)

    create_time = models.DateTimeField(default=now)
    update_time = models.DateTimeField(auto_now_add=True)

Func:

from django.db.models import F, Func

class DateFormatFun(Func):
    function = 'date_format'
    template = "%(function)s( %(expressions)s, '%%%%Y-%%%%m-%%%%d')"

    def as_sqlite(self, compiler, connection, **extra_context):
        sql, params = self.as_sql(compiler, connection,
                                  function='strftime',
                                  template="%(function)s('%%%%Y-%%%%m-%%%%d', %(expressions)s)",
                                  **extra_context)
        try:
            if self.output_field.get_internal_type() == 'DecimalField':
                sql = 'CAST(%s AS NUMERIC)' % sql
        except FieldError:
            pass
        return sql, params

---

# use DateFormatFun
In [2]: AdV2Log.objects.values('adv2_id', date=DateFormatFun('create_time')).annotate(counts=Count('id'))
Out[2]: <QuerySet [{'adv2_id': 7, 'date': None, 'counts': 1}, {'adv2_id': 8, 'date': None, 'counts': 1}, {'adv2_id': 8, 'date': None, 'counts': 1}, {'adv2_id': 9, 'date': None, 'counts': 1}, {'adv2_id': 9, 'date': None, 'counts': 1}]>

# use extra
In [3]: AdV2Log.objects.extra(select={"date": "strftime( '%%Y-%%m-%%d',create_time)"}).values('adv2_id','date').annotate(counts=Count('id'))
Out[3]: <QuerySet [{'date': '2021-07-05', 'adv2_id': 7, 'counts': 1}, {'date': '2021-07-05', 'adv2_id': 8, 'counts': 1}, {'date': '2021-07-06', 'adv2_id': 8, 'counts': 1}, {'date': '2021-07-05', 'adv2_id': 9, 'counts': 1}, {'date': '2021-07-06', 'adv2_id': 9, 'counts': 1}]>

[Mariusz Felisiak]

Trac is not a support channel. If you're having trouble implementing a custom database function, see TicketClosingReasons/UseSupportChannels for ways to get help.