We need to store some date information in our database that has been recorded over the last 50 years. Although for approx the last 30 years, events have been recorded to the nearest day, prior to that we may only have the correct month or, in some cases, the year.
How should I store this information in my database so that:
i. It can still be used for computations, orderings etc.
ii. We don't assign an undeserved level of accuracy to old data - I really don't want to pretend that an event happened on 1st Jan 1958 if all I know is that it happened in 1958.
iii. We can display the appropriate level of accuracy when retrieving date information - ie. if all we know is that the year was 1958 then this is what all that we should display.
I suspect that I need to use two columns, one storing a date and a second highlighting the level of accuracy of that date (YEAR / MONTH / FULL). Any input as to just how we might arrange this would be most welcome.
John
How should I store this information in my database so that:
i. It can still be used for computations, orderings etc.
ii. We don't assign an undeserved level of accuracy to old data - I really don't want to pretend that an event happened on 1st Jan 1958 if all I know is that it happened in 1958.
iii. We can display the appropriate level of accuracy when retrieving date information - ie. if all we know is that the year was 1958 then this is what all that we should display.
I suspect that I need to use two columns, one storing a date and a second highlighting the level of accuracy of that date (YEAR / MONTH / FULL). Any input as to just how we might arrange this would be most welcome.
John