What is an intuitive method of sorting partial dates?

Question

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:

1. Only sort the records that have an EXAct date, refuse to sort any further, and advise users that the sequence contains unordered events.

2. 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.

3. 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?

Answer 1

The second option makes some sense for right-truncated dates at least. It’s basically equivalent to pre-filling minimum fields LMM and LDD both with default value 01, maximum field EMM with 12 and EDD with 28–31 as appropriate. For ascending sort order, one would use L** values and E** for descending – or vice versa if uncertain dates should come after (more) certain dates. It may be helpful to treat them as 00 and 99 instead (and possibly 13 for LMM and 32 for LDD).

PS: Did you consider that other date information may be known, e.g. day of the week?