"Sometime in March": How I Store Dates Nobody Knows

I'm building Casewall, a workspace for investigators and researchers to keep a whole case in one place: the photos, the documents, the people, the places, and a timeline of what happened when.

The timeline is where it gets hard. Almost nothing in a real case comes with a timestamp. A witness says it was "sometime in March." A record says 2019 and nothing else. Someone remembers it was "a few days after the 3rd." Most software gives you a date picker and a time field, so you end up typing March 1 at midnight, and a week later nobody remembers that was a guess.

So the first rule I wrote down was this: store what the source actually said, and nothing more.

Precision Is Part of the Date

Every event in Casewall has a start, an optional end, and a precision. There are seven levels:

Exact, to the second
To the minute
To the hour
Day
Month (approximate)
Year (approximate)
Approximate day

The precision isn't decoration. It changes how the date is shown, where it sits on the timeline, and how it sorts. An event entered as "March 2024, month precision" is a different fact from "March 1, 2024, 12:00 AM," even though they'd be the same value in a database column.

When you read it back, the precision shows. Month precision reads "~March 2024." Year reads "~2024." An approximate day reads "~Mar 1, 2024." A time only appears when the precision includes one, and that's the only time the time zone appears too. Events with no date at all read "Undated" and sit in their own list at the end, which is better than inventing one.

Each event also keeps its own time zone. A case can cover things that happened in three cities, and "10 PM" should mean 10 PM where it happened, not where I'm sitting.

Imports Figure Out Precision Themselves

A lot of events come in through import, not by hand. For those, the precision comes from how much of the date the source gave. "2024" is year precision. "2024-03" is month. "2024-03-01" is day, and it goes down through hour and minute to exact. If the source marks a date as approximate, it gets approximate-day precision no matter how specific it looks.

That way nobody has to remember to set it. A date that came in as "2024-03" can't quietly turn into March 1.

On the Timeline, a Fuzzy Date Is a Window

A dot on a timeline claims a precision the data doesn't have. So coarse dates are drawn as a range instead:

Month: the whole month.
Year: the whole year.
Approximate day: three days either side of the stated day.

Those windows are drawn soft and hatched, so you can tell at a glance which events are pinned down and which are "somewhere in here." The windows are worked out in the event's own time zone too, so "March" starts at midnight on March 1 where it happened, not in UTC.

The Bug: Two Views, Two Orders

The timeline sorts events by the start of their window, which makes sense for a chart. If an event could have happened any time from September 30 on, its range starts on September 30.

Then I built the Walkthrough. It's a view that steps through a case one event at a time, in order, with the map and the link chart following along. It's how you read a case like a story.

It reused the timeline's sort, and that's where it broke. An event marked "~Oct 3" has a window that starts on September 30. An exact event on October 2 starts on October 2. So the Walkthrough showed "~Oct 3" first, then "Oct 2." Anyone reading it would think the dates were wrong.

The fix, on October 26, was to stop pretending one order fit both views. The Walkthrough now sorts by the stated time, so "~Oct 3" comes after Oct 2, where it says. The timeline keeps sorting by window start, because it's drawing ranges, not reading a story. The Walkthrough's sort has a comment saying why it differs from the timeline's, so I don't "fix" it back later.

What I'd Tell Anyone Modeling Dates

Store the precision next to the value. A date without its precision loses information you can't get back.

Let "unknown" be a real state. Undated is better than a made-up date that looks real.

Decide what each view needs from the data. A chart and a reading list can want different orders from the same events. That's fine, as long as it's on purpose and written down.

Casewall isn't open yet, but there's a waitlist at casewall.org. If you're working through a data model with this kind of mess in it, that's the kind of problem I help with in advisory.