I have a table of partial dates, with columns as follows:
EXA: Exact date and time (e.g. 2016-02-14 12:21:23) XYY: Year XMM: Month XDD: Day EYY: Earliest Possible Year EMM: Earliest Possible Month EDD: Earliest Possible Day LYY: Latest Possible Year LMM: Latest Possible Month LDD: Latest Possible Day
At least one column will be populated, but all could potentially be NULL. It is not possible to enter contradictory information (e.g. EYY=2006, LYY=1977).
The table is used to describe events whose exact date can only be approximated. For example, if you have a photo of a Christmas tree with presents under it, you might assume it was taken on or about December 25th, without knowing which year.
My goal is to sort these partial dates into a sequence that can be browsed by the users - and I want the order to make some kind of intuitive sense, even if there can be no exact total ordering of all dates.
Options I have considered are:
Only sort the records that have an EXAct date, refuse to sort any further, and advise users that the sequence contains unordered events.
Transform some of the information, "filling in the blank":
(?YY,) ==> (Year=?YY, Month=01, Day=01)
(?YY,?MM) ==> (Year=?YY, Month=?MM, Day=01)
And then sort the transformed records excluding records such as (XMM, XDD) that can not be "anchored" in a year - again advising users of the limitations of the sort.
- As above, plus additionally attempting to infer/average an exact date from earliest/latest information, such as (EYY=2010,LYY=2012) ==> (Year=2011,Month=01,Day=01).
None of these feel like great or complete solutions - could I be doing more?