Partially null dates in MySQL/Django

Question

I'm currently converting my site from PHP to Django, but as I'm new to Python I'm struggling to get my head around a few thingss.

Sometimes I have incomplete dates in my database (ie I may not know the day or even the month), so currently have three integer fields: date_year, date_month and date_day.

I noticed that MySQL accepted 'partially null' date fields (eg 2004-04-00, 1994-00-00), but Django just treats them as 'completely' null.

I'm only going to want to display these dates in the following formats:

• 16 March 2009 (completely known)
• March 2009 (unknown day)
• 2009 (unknown day and month)

I presume that I'm going to be unable to use proper date fields in MySQL and that I'd have to have a create a faux date in the model then use a custom template filter; would anyone be able to help?

Answer 1

When MySQL is running in ALLOW_INVALID_DATES mode it doesn't perform any validation checking on the input so you can store invalid dates. This is an extension to standard SQL and because Django is cross-database compatible it doesn't support this. Also, Django converts dates into Python date objects which also don't support partial dates.

Your best bet is to either split the column in to three or to store the date as text and process it yourself.

Answer 2

You could store two datetimes as a range. If midnight to midnight means "sometime in this day" and if the range is across a whole month, thats what you know. If you know the exact time, they'll be equal.

Answer 3

I was looking for a solution to a similar problem, but using PostgreSQL.

Fuzzy date matching in PostgreSQL

Perhaps you could adapt something like that? It's a bit off-the-wall, I know.