django sqlite strftime & parametric SQL query
<p>I have a <strong>django</strong> based web application doing, among others, analyses of time histories. I am currently using <strong>sqlite</strong> as db backend. </p>

<p>I typically group slices of data in blocks of ten minutes, thus I aggregate to get my final results.</p>

<p>To obtain ten minutes blocks of data I have the following queryset </p>

<pre><code>blocks = qs.extra({'ten_min' : u"strftime('%Y%m%d%H0', date) + strftime('%M', date)/10"})\
        .values('ten_min').annotate(mop.Sum('event_val1'),mop.Sum('event_val2')).all()
</code></pre>

<p>which I also possibly restrict on given time intervals, e.g. </p>

<pre><code>qs.get(date__gte = last_midnight)
</code></pre>

<p>While the two queries work perfectly while separated, <strong>they do not work chained</strong>.</p>

<p>The final parametric SQL query string that django generates cannot be rendered as the <code>%</code> in the <code>strtftime</code> of sqlite are misinterpreted as element to be replaced while rendering the string.</p>

<p>Let me report the entire query for clarity:</p>

<pre><code>params = (u"'2016-10-06 00:00:44.054382'",)
sql =   u'SELECT (strftime(\'%Y%m%d%H0\', date) + strftime(\'%M\', date)/10) AS "ten_min", SUM("event_logger_event"."event_val1") AS "event_val1__sum", SUM("event_logger_event"."event_val2") AS "event_val2__sum" FROM "event_logger_event" WHERE "event_logger_event"."date" &gt;= %s GROUP BY (strftime(\'%Y%m%d%H0\', date) + strftime(\'%M\', date)/10)'
</code></pre>

<p>the query is rendered as <code>sql % params</code> which gives an error.</p>

<p>In fact the system tries to replace elements such as <code>%Y%m%d</code> rather than the correct </p>

<pre><code>... "date" &gt;= %s ...
</code></pre>

<p>Any suggestion on how to deal with this?</p>