Opened 11 years ago

Closed 9 years ago

#19716 closed New feature (fixed)

Support microsecond precision in MySQL ORM DateTimeField

Reported by: erik@… Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

MariaDB, and MySQL 5.6, support sub-second precision in DATETIME fields (see https://kb.askmonty.org/en/microseconds-in-mariadb/).

Ideally, the ORM should support storing and retrieving datetime() with microsecond precision and creating DATETIME(n) columns.

I tested a models.DateTimeField() field backed by DATETIME(3) on MariaDB, but unfortunately the field on the Django model instance is always None even though the database contains a valid date.

Attachments (1)

19716-1.diff (1.7 KB) - added by Claude Paroz 10 years ago.
Stop stripping microseconds

Download all attachments as: .zip

Change History (21)

comment:1 Changed 11 years ago by Carl Meyer

Triage Stage: UnreviewedAccepted

comment:2 Changed 10 years ago by sarang (@…

This is a 3 line change. Hope Django developers include it.

In "db/backends/mysql/base.py"
function "def value_to_db_datetime(self, value)"

Change from:

return six.text_type(value.replace(microseconds=0))

to:

return six.text_type(value)

In "db/backends/mysql/base.py"
function "def value_to_db_time(self, value)"

Change from:

return six.text_type(value.replace(microseconds=0))

to:

return six.text_type(value)

In "db/backends/mysql/creation.py"
In definition of "data_types"

Change from:

'DateTimeField': 'datetime',

to:

'DateTimeField': 'datetime(6)',

comment:3 Changed 10 years ago by Aymeric Augustin

Sure, it's a three line change if you disregard tests, documentations and backwards compatibility -- three concepts that are highly regarded in this community :)

If we include this change in a future version of Django, what's going to happen:

  • for developers running an older version of MySQL?
  • for developers upgrading from an older version of Django?

Do we need different code paths depending on the version of MySQL?

What are the consequences for backwards compatibility?

Here's a theoretical example. Let's assume an database column defaulting to now(). Right now, this column contains values without sub-second precision. These values can safely be fed into a webservice that doesn't support sub-second precision. If we make this change, suddenly, this will fail.

To move this ticket forwards, you need to identify the consequences and propose a way to deal with them, either with code or with docs.

comment:4 Changed 10 years ago by Coen van der Kamp

I tested as Erik but on MySQL 5.4.6 and had the same. The datetime with fractions get stored in db (column datatime(6)) but get NoneType returned.

In [5]: get_object_or_404(MyModel, pk=1).dt_millis.__class__
Out[5]: NoneType #This should be datetime.datetime

I created a custom model to create the datetime([1-6]) column. This way a developer has a choice on what type of datetime to use:

class DateTimeFractionField(models.DateTimeField):
    description = "Datetimefield with fraction second. Requires MySQL 5.4.3 or greater"

    def __init__(self, precision, *args, **kwargs):
        self.precision = precision
        super(DateTimeFractionField, self).__init__(*args, **kwargs)

    def db_type(self, connection):
        return 'DATETIME(%s)' % self.precision

class MyModel(models.Model):
    dt_micros  = DateTimeFractionField(6)

The current backend 'strips' the milliseconds with .replace(microsecond=0). So microseconds aren't gone. They are 0. The MySQL docs state that < MySQL 5.4.3 discards any fractional part. So I assume that removing all .replace(microseconds=0) from the MySQL backend is a good beginning. Doing so let's MySQL decide if the value's get stored or not.

I also tried to store datetime with fractions in Postgresql. The buildin datetimefield works. But I needed to modify the formfield clean method to submit and admin widget to display the fracrions in the admin.

This is my first contribution. I'd like to collaborate to get this ticket closer to fixed.

comment:5 Changed 10 years ago by anonymous

I have been running with this for awhile. For 1.6.1 this is the diff for db/backends/mysql/base.py
162c162
< supports_microsecond_precision = True
---

supports_microsecond_precision = False

352c352
< return six.text_type(value)
---

return six.text_type(value.replace(microsecond=0))

363c363
< return six.text_type(value)
---

return six.text_type(value.replace(microsecond=0))

368c368
< return [first, second]
---

return [first.replace(microsecond=0), second.replace(microsecond=0)]

I must add that I also updated mysqldb 1.2.4 to handle microseconds at the same time. Both have been working without issue ever since.

comment:6 Changed 10 years ago by russ.blaisdell@…

Here is the update to mysqldb/times.py
54c54,59
< return datetime(*[ int(x) for x in d.split('-')+t.split(':') ])
---

if '.' in t:

t, ms = t.split('.',1)
ms = ms.ljust(6, '0')

else:

ms = 0

return datetime(*[ int(x) for x in d.split('-')+t.split(':')+[ms] ])

63,65c68,75
< h, m, s = int(h), int(m), float(s)
< td = timedelta(hours=abs(h), minutes=m, seconds=int(s),
< microseconds=int(math.modf(s)[0] * 1000000))
---

if '.' in s:

s, ms = s.split('.')
ms = ms.ljust(6, '0')

else:

ms = 0

h, m, s, ms = int(h), int(m), int(s), int(ms)
td = timedelta(hours=abs(h), minutes=m, seconds=s,

microseconds=ms)

77,79c87,94
< h, m, s = int(h), int(m), float(s)
< return time(hour=h, minute=m, second=int(s),
< microsecond=int(math.modf(s)[0] * 1000000))
---

if '.' in s:

s, ms = s.split('.')
ms = ms.ljust(6, '0')

else:

ms = 0

h, m, s, ms = int(h), int(m), int(s), int(ms)
return time(hour=h, minute=m, second=s,

microsecond=ms)

Changed 10 years ago by Claude Paroz

Attachment: 19716-1.diff added

Stop stripping microseconds

comment:7 Changed 10 years ago by Claude Paroz

Attached a proposal, which is only the first step towards the resolution of this ticket.

comment:8 Changed 10 years ago by Claude Paroz

Has patch: set

comment:9 Changed 9 years ago by Anssi Kääriäinen

Needs tests: set

comment:10 Changed 9 years ago by Claude Paroz

As for my patch, I think that the fact that the current test suite pass (I'll make a PR to test this) should be sufficient.
Of course, when we really support microseconds, the supports_microsecond_precision db feature will have to depend on the MySQL version.
PR: https://github.com/django/django/pull/3049

comment:11 Changed 9 years ago by Claude Paroz

After seeing that the tests were successful with the first commit, I pushed a second commit which should really add microseconds support with MySQL 5.6.4 and up. However, it is completely untested yet (and the CI server has still MySQL 5.5).

comment:12 Changed 9 years ago by Claude Paroz

Needs tests: unset

The current state of the patch seems to generate two failures in the test suite (https://github.com/django/django/pull/3049#issuecomment-52261704).

comment:13 Changed 9 years ago by Claude Paroz

Patch needs improvement: set

I have a good news and a bad news:

  • the good news is that I found and fixed the source of the test failures mentioned above (in adapt_datetime_with_timezone_support).
  • the bad news is that we need a very recent version of MySQLdb (1.2.5 released on January 2 2014) to have a bug fixed when retrieving datetime value with microseconds from the database, unless we obtain None. The patch will need to check and document that limitation.

comment:14 Changed 9 years ago by Claude Paroz

comment:15 Changed 9 years ago by Tim Graham

Would it be unreasonable to bump the minimum version of MySQLdb we support? It seems we currently require 1.2.1 or later (django.db.backends.mysql.base). I assume the only reason to use an older version is if you want to use the global packages provided by your OS instead of virtualenv, etc.

comment:16 Changed 9 years ago by Claude Paroz

We could bump it, but not to 1.2.5. Even Debian unstable has still 1.2.3, which might mean that there are other issues with more recent versions.

comment:17 Changed 9 years ago by Claude Paroz

Patch needs improvement: unset

Patch updated.

comment:18 Changed 9 years ago by Tim Graham

Triage Stage: AcceptedReady for checkin

comment:19 Changed 9 years ago by Claude Paroz <claude@…>

In 9e746c13e81241fbf1ae64ec118edaa491790046:

Stopped stripping microseconds with MySQL backend

Refs #19716.

comment:20 Changed 9 years ago by Claude Paroz <claude@…>

Resolution: fixed
Status: newclosed

In 22da5f8817ffff3917bcf8a652dce84f382c9202:

Fixed #19716 -- Added support for microseconds with MySQL 5.6.4 and up

Thanks erik@… for the report and Tim Graham for the review.

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