Geek Answers Handbook

Partially null dates in MySQL / Django

I am currently converting my site from PHP to Django, but since I'm new to Python, I'm struggling to figure out a few things.

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

I noticed that MySQL accepted the “partially null” date fields (for example, 2004-04-00, 1994-00-00), but Django just treats them as “completely” null.

I will need to display these dates in the following formats:

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

I assume that I will not be able to use the correct date fields in MySQL and that I will need to create a faux date in the model and then use a custom template filter; can anyone help?

+4
source share
3 answers

When MySQL runs on ALLOW_INVALID_DATES , it does not perform validation checks on the input, so you can store invalid dates. This is an extension of standard SQL, and because Django is compatible with multiple databases, it does not support this. In addition, Django converts dates to Python date objects, which also do not support partial dates.

It’s best to either break the column into three, or save the date as text and process it yourself.

+1
source

You can save two times as a range. If midnight to midnight means “someday that day,” and if the range goes through a whole month, here's what you know. If you know the exact time, they will be equal.

+3
source

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

Fuzzy Date Matching in PostgreSQL

Perhaps you could adapt something like that? I know this is bit street.

+1
source

More articles:


All Articles