Date's of the form 0000-00-00 don't work

[mlambert@…]

MySQL uses 0000-00-00 for some dates (as a default for non-null fields, among other things)

This breaks the loader here:

File "django/core/db/typecasts.py", line 8, in typecast_date

return s and datetime.date(*map(int, s.split('-'))) or None # returns None if s is null

ValueError: year is out of range

Perhaps it should just return None instead?

[dustin@…]

Shouldn't this be a bug filed against mySQL?

[Adrian Holovaty]

Wait, can you explain this ticket further?

[dustin@…]

I haven't seen any updates from the submitter, but I can describe it as I see it.

If a date field is NOT NULL in mySQL and you don't have a valid date to insert, you can (or it will) insert 0000-00-00 and return that value when you try to get the date out.

I don't see how this could be considered anything but a bug in mySQL. If you intend to not have valid data for a column, make it nullable.

I don't think Django should be dirtied up to work around a database vendor's belief that the schema should only be followed where it doesn't prevent a user from entering invalid data. (I also don't know why anybody would use mySQL for anything ever, but people do, so I wouldn't hold it against you guys for working around their bugs).

It might be better to recommend that mySQL users configure their DBs to behave correctly. There apparently are options in the mySQL config to at least make it less stupid:

​http://dev.mysql.com/doc/mysql/en/server-sql-mode.html

In particular, NO_ZERO_DATE (or better, mode TRADITIONAL -- both as of 5.0.2) will work around this bug in mySQL.

[Adrian Holovaty]

Dustin put it really well: We shouldn't have to clean up MySQL's mess, in this regard. If you're using Django with MySQL, don't use "0000-00-00" for dates.

[Jens]

Here a small work-a-round patch, that works for me fine:

its for ​http://code.djangoproject.com/browser/django/trunk/django/db/backends/util.py

@@ -55,6 +55,12 @@
     return datetime.time(int(hour), int(minutes), int(seconds), int(float('.'+microseconds) * 1000000))
 
 def typecast_timestamp(s): # does NOT store time zone information
+    if s.startswith("0000-00-00"):
+        # workaround mysql issue.
+        # see http://code.djangoproject.com/ticket/2763 and 2369
+        # see http://code.djangoproject.com/ticket/443
+        return None
+
     # "2005-07-29 15:48:00.590358-05"
     # "2005-07-29 09:56:00-05"
     if not s: return None

Look at this tickets, too:

• 2763
• 2369