#32699: Comparisons using TruncTime are unreliable in MySQL
-------------------------------+------------------------------------
Reporter: Alex Hill | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 3.2
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+------------------------------------
Changes (by Simon Charette):
* stage: Unreviewed => Accepted
Comment:
> But if you make a `TIME` column and insert `'10:30:00.000000'` into it,
then `SELECT ... WHERE TIME(dt) = '10:30:00.000000'` won't find the row.
It's a mess.
It doesn't happen if you create the column as `TIME(6)`
[https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html to get
microseconds precision] though which
[https://github.com/django/django/blob/7582d913e7db7f32e4cdcfafc177aa77cbbf4332/django/db/backends/mysql/base.py#L130
is something Django does].
{{{#!sql
mysql [email protected]:django> CREATE TABLE foo (id int auto_increment
primary key, t TIME, t6 TIME(6));
Query OK, 0 rows affected
Time: 0.017s
mysql [email protected]:django> INSERT INTO foo (id, t, t6) VALUES(0,
'10:30:00.000000', '10:30:00.000000');
Query OK, 1 row affected
Time: 0.009s
mysql [email protected]:django> SELECT t = '10:30:00.000000', t6 =
'10:30:00.000000', TIME(t) = '10:30:00.000000', TIME(t6) =
'10:30:00.000000' FROM foo;
+-----------------------+------------------------+-----------------------------+------------------------------+
| t = '10:30:00.000000' | t6 = '10:30:00.000000' | TIME(t) =
'10:30:00.000000' | TIME(t6) = '10:30:00.000000' |
+-----------------------+------------------------+-----------------------------+------------------------------+
| 1 | 1 | 0
| 1 |
+-----------------------+------------------------+-----------------------------+------------------------------+
1 row in set
Time: 0.009s
}}}
Which is kind of expected as `TIME`
[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-
functions.html#function_time returns a string representation of the
temporal value] and the `TIME` column type doesn't have microsecond
precision and thus doesn't represent them.
I think the patch makes sense since the time you'd be passing a literal
`time` instance would be when
1. Comparing against a `TimeField` column from a model and MySQL work
properly in this case.
2. Against `DateTimeField` transform such as `__time` as reported here
where `TIME` is used and works properly with `DATETIME(6)` which are used
by default since Django 1.8.
--
Ticket URL: <https://code.djangoproject.com/ticket/32699#comment:1>
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.41834454ab5d54b478d94656f12c0093%40djangoproject.com.
