How to create a Django custom Field to store MYSQL DATETIME(6) and enable fractional seconds (milliseconds and or microseconds) in Django/MySQL?

Question

MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision: http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

Django 1.5 and up supports fractional seconds as imputformat: https://docs.djangoproject.com/en/1.5/ref/settings/#datetime-input-formats

But the DATETIME(6) field isn't implemented in Django yet. https://code.djangoproject.com/ticket/19716

I decided to write a custom DateTimeFractionField. It's the standard DateTimeField with the DATETIME([1-6]) db_type. 'precicion' is to set milliseconds, microseconds or any other fraction precicion.

class DateTimeFractionField(models.DateTimeField):
    description = "Datetimefield with fraction second."

    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)
    dt_millis = DateTimeFractionField(3)
    dt = models.DateTimeField()

    def __unicode__(self):
        return "%s - %s" %(self.dt_micros, self.dt_millis)

The mysql backend is responsible for replacing milliseconds with 0. The Django documentation suggests to write my own backend. https://docs.djangoproject.com/en/1.5/ref/settings/#engine

I hacked:

$ cd /path/to/site-packages/django/db/backends/
$ cp -r mysql mysql564

And modified mysql564/base.py:

Line 42:45

from django.db.backends.mysql564.client import DatabaseClient
from django.db.backends.mysql564.creation import DatabaseCreation
from django.db.backends.mysql564.introspection import DatabaseIntrospection
from django.db.backends.mysql564.validation import DatabaseValidation

Line 167

supports_microsecond_precision = True

Line 214

compiler_module = "django.db.backends.mysql564.compiler"

Line 357

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

Line 368

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

Line 373

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

Then i activated my new backend in settings.py:

'ENGINE': 'django.db.backends.mysql564',

When I add and save my model in the admin, the values get saved to de db! :)

But they are not returned (None - None and empty form fields). :(

What am I missing here?

1. Why are DateTimeFractionField values not returned?
2. Is there a better (simpler) way to implement a datetimefield that support fractions?

I know there are other db's supporting fractions. But I like to use MySQL and get the ticket a little closer to fixed.

UPDATE:

It's not (only) the form, getting a datetime object from de db fails.

In [1]: from spacetime.models import MyModel

In [2]: from django.shortcuts import get_object_or_404

In [3]: get_object_or_404(MyModel, pk=1).dt
Out[3]: datetime.datetime(2013, 7, 25, 0, 22, 23)

In [4]: get_object_or_404(MyModel, pk=1).dt_millis

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

Answer 1

I've tried this succesfully, but my approach is quite different: I split the info in a DateTimeField (UTC) and a IntegerField (microseconds = uS):

from datetime                   import datetime, timedelta
import pytz

class MyModel (models.Model):
    # My model stuff and other fields here
    _UTC = models.DateTimeField ()
    _uS  = models.IntegerField()

And then I set a property in the class to return UTC with microseconds and zone info aware (because my database doesn't support zoneinfo)

    @property
    def UTC (self): 
        utc = self._UTC
        us  = self._uS
        # add microseconds
        utc += timedelta(microseconds=us)
        return utc.replace(tzinfo=pytz.utc)
    @UTC.setter
    def UTC (self,utc):
        self._UTC = utc-timedelta(microseconds=utc.microsecond) # without microseconds
        self._uS = utc.microsecond