# What is an intuitive method of sorting partial dates?

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.

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

• Note that the X** fields are unnecessary, as you can just set E** and L** to the same value. Also, L** will probably default to the entry date. Feb 14, 2016 at 18:44