[Django] #32909: strftime got None on sqlite

9 views

Skip to first unread message

Django

unread,

Jul 7, 2021, 5:13:06 AM7/7/21

to django-...@googlegroups.com

{{{
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}]>

}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32909>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jul 7, 2021, 5:17:35 AM7/7/21

to django-...@googlegroups.com

#32909: strftime got None on sqlite
-------------------------------------+-------------------------------------
Reporter: gojuukaze | Owner: nobody

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => invalid

Comment:

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