Seppo Erviälä Seppo Erviälä - 2 months ago 21
MySQL Question

Django ORM, Insert None datetime as 0 into MySQL

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

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.