Incorrect SQL text when searching in SQLite for datetime values with milliseconds

[Alexandr Zarubkin]

I have SQLite database where certain column in table is created as TIMESTAMP. The database is created not by Django, but by SymmetricDS, a DB replication software. The database is replicated from Microsoft SQL Server 2005, where it has datetime type. In my Django application, the model has DateTimeField for that column.

class Calendar(models.Model):
    day_id = models.IntegerField(primary_key=True)
    day_date = models.DateTimeField()

The table contains a row with day_date equal to '2015-01-18 00:00:00.000'.

If I try to search for that row with

day = Calendar.objects.filter(day_date=date(2015,1,18))

I get SQL with the following WHERE clause:

WHERE day_date = '2015-01-18 00:00:00'

, i.e. it doesn't contain milliseconds part. As a consequence, the row is not found. According to ​http://www.sqlite.org/datatype3.html, the text representation of datetime values in SQLite should have milliseconds part.

In MS SQL it works fine (I use django-pyodbc-azure driver).

The workaround is:

dt = datetime(2015,1,18) # datetime, not date
day = Calendar.objects.filter(day_date__lt=day+timedelta(seconds=1),
                              day_date__gt=day-timedelta(seconds=1))

[Shai Berger]

Hi, thanks for your report.

Can you please try these two variations:

a) filter(day_date=datetime(2015,1,18))

b) The whole thing on a newer Django -- 1.6 now gets important (that is, security or data-loss) fixes only, and I don't think this qualifies.

Thanks again.

[Alexandr Zarubkin]

The bug still exists in Django 1.7.3, and filter(day_date=datetime(2015,1,18)) doesn't change anything.

[Claude Paroz]

Since Django 1.9, the query should force to date with:
Calendar.objects.filter(day_date__date=date(2015,1,18))