This is a problem I've run into a few times. Imagine you have a record that you want to store into a database table. This table has a DateTime column called "date_created". This one particular record was created a long time ago, and you're not really sure about the exact date, but you know the year and month. Other records you know just the year. Other records you know the day, month and year.
You can't use a DateTime field, because "May 1978" isn't a valid date. If you split it up into multiple columns, you lose the ability to query. Has anyone else run into this, if so how did you handle it?
To clarify the system I'm building, it is a system that tracks archives. Some content was produced a long time ago, and all that we know is "May 1978". I could store it as May 1 1978, but only with some way to denote that this date is only accurate to the month. That way some years later when I'm retrieving that archive, I'm not confused when the dates don't match up.
For my purposes, it is important to differentiate "unknown day in May 1978" with "May 1st, 1978". Also, I would not want to store the unknowns as 0, like "May 0, 1978" because most database systems will reject that as an invalid date value.
Best Answer
Store all dates in normal DATE field in the database and have additional accuracy field how accurate DATE field actually is.
date_created_accuracy: 1 = exact date, 2 = month, 3 = year.
If your date is fuzzy (e.g May 1980) store it at start of period (e.g. May 1st. 1980). Or if your date is accurate to year (e.g. 1980) store it as January 1st. 1980 with corresponding accuracy value.
This way can easily query in a somewhat natural way and still have notion how accurate dates are. For example this allows you to query dates between
Jan 1st 1980andFeb 28th 1981, and get fuzzy dates1980andMay 1980.