Opened 9 years ago

Closed 5 years ago

#24176 closed Bug (fixed)

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

Reported by: Alexandr Zarubkin Owned by: nobody
Component: Database layer (models, ORM) Version: 1.7
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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))

Change History (5)

comment:1 by Alexandr Zarubkin, 9 years ago

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

comment:2 by Shai Berger, 9 years ago

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.

comment:3 by Alexandr Zarubkin, 9 years ago

Version: 1.61.7

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

comment:4 by Tim Graham, 9 years ago

Triage Stage: UnreviewedAccepted

comment:5 by Claude Paroz, 5 years ago

Resolution: fixed
Status: newclosed

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

Note: See TracTickets for help on using tickets.
Back to Top