It could be I already have an answer to this, but I just wanted to put it by the community here before implementing it, to check this is the right way to go.
The problem is having tables with (historic) dates, but I don’t always have data for an exact date.
In some rows I know the full date: year, month and day. But in others I just know the year and month, or just the year and sometimes no date info at all.
I was thinking of the best way to handle this. For no data a NULL value will do, but with partial data I have in the past tried invalid dates like 1885-06-00 if I know the month but not the day, but some apps and systems cough at such invalid data.
Mysql has a YEAR type, but not MONTH and DAY types, so I’m not sure if using a 3 column system would be a good idea, as I would lose the built in date functionality.
Searching around I found this on SO:-
Which gave me the idea I was thinking of going with.
So I may store a date as 1885-06-01 instead of 1885-06-00, then have an extra column for specificity (Y|M|D) which in this case would be M, as I have accuracy to the month here.
So I would still be storing valid dates, I would then just need to do a little PHP function to deal with the specificity.
My only thought is that although this is an interesting way to deal with it, it only takes three database columns to store the Y, M, D separately instead of the two columns here, and maybe would require less manipulation by PHP. So I’m not sure it would make your task any easier.
I personally would use CASE statements within the SQL to handle the specificity, but that’s just me.
The biggest issue you’d have to over come is ordering your dates. Will you put the “month” dates first for a sort, or last? Once you decide, it’s easy enough to get the CASE statements set up to parse out what you’re looking for.
Using the three columns was another option, I would still be able to sort by date, but I think I would lose some of mysql’s other date selection functions, unless there is some clever method to combine the columns to behave like a real date.
I’m not convinced by this. Either way I think it will require a bit of php jiggery-pokery to format the dates in a meaningful way, but it should only take one function to do that.
I wasn’t seeing that as an issue. I’m OK with using 01 (the lowest possible value) in unknown fields and using the natural order where unknowns come first. Yes, that may cause a mix-up where there are “knowns” on the 1st, but I guess that’s where CASE would come in.
CASE is something I have used infrequently enough that I had to look it up to remind me what it does. How were you thinking of using it, just for the ordering or something else?
I would probably just use it to return the appropriate format in the selection criteria you want. You can put it in the order by field. All depends on how you want to use it…
SELECT CASE dateFormat
WHEN 1 THEN DATE_FORMAT(dateField, "%Y") -- yyyy
WHEN 2 THEN DATE_FORMAT(dateField, "%m %Y") -- mm yyyy
WHEN 3 THEN DATE_FORMAT(dateField, "%d %m %Y") -- dd mm yyyy
ELSE DATE_FORMAT("1900-01-01", "%d %m %Y") -- assume an invalid date
END AS formattedDate
FROM tableName
Yes, that may cause a mix-up where there are “knowns” on the 