Django ORM, Insert None datetime as 0 into MySQL

Question

I have a MySQL database that is used by another application besides Django. That application uses '0000-00-00 00:00:00' as default value for datetimes.

Django (v1.5.5) interprets '0000-00-00 00:00:00' datetime as None when reading the database and None as NULL when writing into the database. This causes an error since the database defines the field as NOT NULL.

Manually setting:

model.datetime = '0000-00-00 00:00:00'

Doesn't work because Django feels that this is an invalid date.

How do I create a custom datetime field which inserts None as '0000-00-00 00:00:00'?

Answer 1

building on Seppo's solution (which didn't work for django 1.9.4 because of lacking connection.ops.value_to_db_datetime), I ended up using a custom DateTimeField as follows:

from django.db import models

class ZeroDateTimeField(models.DateTimeField):
    def get_db_prep_value(self, value, connection, prepared=False):
        value = super( ZeroDateTimeField, self ).get_db_prep_value( value, connection, prepared )
        if value is None:
            return  "0000-00-00 00:00:00"
        return value

Then just use ZeroDateTimeField in your models instead of DateTimeField.

works like a charm and I got around having to tell the customer to fix his mysql db ;-)

EDIT: I ended up overriding constructor to set null=True and blank=True to ease use of automatically generated models. Since this may be useful for some, here it is (this is optional and questionable):

def __init__(self, verbose_name=None, name=None, auto_now=False, auto_now_add=False, **kwargs):
    kwargs['null'] = True
    kwargs['blank'] = True
    super( ZeroDateTimeField, self ).__init__(verbose_name, name, auto_now, auto_now_add, **kwargs)

Answer 2

Create a custom DateTimeField and override the get_db_prep_value. The method is copypasted from the django source and a case is added for handling None. The value should be converted in a database specific manner so this is a bit hacky but it works as long as the database accepts 0000-00-00 00:00:00 as a datetime.

from django.db import models

class ZeroDateTimeField(models.DateTimeField):
    def get_db_prep_value(self, value, connection, prepared=False):
        # Casts datetimes into the format expected by the backend
        if not prepared:
            value = self.get_prep_value(value)

        # Use zeroed datetime instead of NULL
        if value is None:
            return  "0000-00-00 00:00:00"

        else:
            return connection.ops.value_to_db_datetime(value)

EDIT: This answer was written for Django 1.5 and support with other versions has not been tested.