How do I store (and sort) vague date ranges?

Question

I have a database which needs to store year ranges (such as lifespan) which in some instances aren't very specific. For example, as well as the usual '1960', we have 'c. 1960' for "around 1960", and 1960s for in the decade 1960, as well as 'nd' for no date which is distinctly different from NULL which would be until present.

What would be the best way to store these?

I was thinking of possibly storing the year as a string and appending 's' for decades and 'c' for circa, with 'nd' for no date... but this feels just wrong. Also, when sorting chronologically I need to sort by inserting 1960s items before c. 1960 items, which in turn are before 1960 items.

How do I do this properly?

Some example date ranges:

1957
c. 1957
c. 1957 - 1965
c. 1957 - 1960s
c. 1957 - present
c. 1957 - nd

Thanks for your help!

Answer 1

You can insert the earliest and the latest date possible for the range.

Like, exact date will be

Jan 1, 1960   Jan 1, 1960

, c. 1960 will be

Jan 1, 1960   Dec 31, 1960

, 1960s will be

Jan 1, 1960   Dec 31, 1969

etc.

Then you can order by the first date and the datediff:

SELECT  *
FROM    mytable
ORDER BY
        start_date, DATEDIFF(end_date, start_date) DESC

Alternatively, you can store the expected value and the deviation instead of the start and end dates.

Answer 2

This sounds like a 'fuzzy logic' type problem. I would tackle it by using multiple columns:

• Earliest Possible date
• Earliest Certain date
• Latest Certain Date
• Latest Possible Date

Your examples might be represented as

• 1957,1957,1957,1957
• 1955,1957,1957,1959
• 1955,1959,1965,1965
• 1955,1959,1960,1969
• 1955,1959,NULL,NULL
• 1955,1959,9999,9999

I'm assuming 'c' means +/-2 years, a decade runs from '0 to '9, and 9999 is a flag value for 'nd'

Any use?