#24176: Incorrect SQL text when searching for datetime values with milliseconds
----------------------------------------------+--------------------
Reporter: me21 | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.6
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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.
{{{#!python
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
{{{#!python
day = Calendar.objects.filter(day_date=date(2015,1,18))
}}}
I get SQL with the following WHERE clause:
{{{#!sql
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:
{{{#!python
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))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24176>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/047.f383b2390834f60bdcd06010d3d8c2e3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
