Integrating multiple date metadata into a single sort

I am trying to harmonize several dating series, which I am currently structuring as custom metadata strings:

Document date
Start (also for Birth/Opening)
End (also for Death/Closure)

I settled on using strings because I am writing a nonfiction book that requires both precise and fuzzy dating. I need a precise chronology over a 2-year period, decent precision for a 10-year period, and then broader resources covering a 200-year context. Often, items like census records have no information beyond YYYY and in a large number of cases I will have death information to the minute but no birth information. Per several prior discussions strings seemed like the way to go, and no complaints here—most of my files were manually assigned YYYY-MM-DD names in the dark years before DT. A lot of the files are primary source PDFs, but others are markdown note files representing people, organizations, events, etc.

The issue seems to be that I can’t harmonize these parameters as a single timeline. I cannot sort by all three parameters together in a file list. This is particularly true for files that have the start and end dates, which can’t appear in one list. Is there a way to display these three dates in order together?

Alternatively, are there ways to generate markdown or other files that could link a set of documents/events within a given span, e.g. my densest period, “1925-11” to “1926-07”.

I would prefer not to use an external timeline app—I played around with Aeon and found it was useful but not enough.

Thanks in advance—loving DTP!

Do you want to sort these three dates for each item (e.g. in the name or a custom metadata field) or sort the item list by using these three dates? The first task should be doable using a script, the second one is difficult as sorting supports only one criterion (and as there might be conflicts).

Would completing the dates with fictional values like January 1st for years or first day for months work?

I have a similar requirement for my task list
My solution is a custom Applescript to perform the sort

When I was volunteering at our local regional archive I was instructed to enter “1” for missing days & months. I guess it’s more or less a standard.
It’s always benificial to have just one format for dates.

Thank you for the response. It is the latter scenario. Your comment about conflicts, and events appearing at the same time is precisely the reason I created separate start and end dates. So I understand that it’s difficult.

Speaking with a friend, she suggested creating one-line markdown files that are dated for birth and death. This makes sense as I have death notices/certificates/clippings for most key figures, which already have the document date metadata. I will try adding these one-line files for individuals with undocumented life events and report back.

@chrillek @alastor933 I appreciate the suggestions, but I feel using strings like I am is the most dependable. Based on experience, adding 1/1/YYYY entries can create a false sense of certainty, especially for people who access the database but don’t know how I fudged it. This is a real risk as I am planning on donating my project’s database to the local historical society. In my experience in both architecture and journalism, you never want to provide certainty when you don’t need it, and especially when you aren’t certain!

Is there an argument to be made for multiple custom metadata date fields with specific uses
Begin (project, building, group, etc)
End
Birth (persons only)
Death
Era/General (limited to year or year/month)
Publication (documents with specific data about this)

Along with metadata that matches those categories you could script ordering based on which things take priority, which are empty etc.

just thinking out loud here…

Why not use zero then instead of one? That’ll show sorting and convey the missing accuracy. Since you’re using strings, it first interfere with date rules.