Querying with Dates in FHIR, date to dateTime

Oh, the vagaries of time .. and date .. and time zones.  What does it mean to query by date when there is a timestamp?

FHIR has this to say:

Where possible, the system should correct for time zones when performing queries. Dates do not have time zones, and time zones should not be considered. Where both search parameters and resource element date times do not have time zones, the servers local time zone should be assumed.

I find this unhelpful without a lot more thought.

What happens (or should happen) when you compare a date (in your query) to a dateTime in a resource?

Consider a dateTime for birth: January 20th, 1965, at 3:30am EST.  Assume this is stored in the Patient resource, and except for the hour (which I changed to explain the problem), this is my birth date.

Now, query for date of birth using 1965-01-20.  This is a whole day.  If I perform the query using Unix Epoch times, this day started in EDT at -156106800, and ended at -156020401.  But in UTC it starts on -156124800 and ends at -156038401.  And in AKST (Alaska), it's different again.

The same time in Alaska is 4 hours earlier than it is in Boston, and which is another 5 hours earlier than the time London.  So, what how do I compare the date?

Let's say I had an evil twin brother from another mother born in Alaska the same time as I was born in Philadelphia.  Well, his birth date is a day before mine.  He's evil because he got to get his driver's permit a day before I got mine ... and birthday presents a day earlier, and a bunch of other stuff I didn't get.

But we were born at the exact same time.  So if I query a FHIR server that has both our patient records for fhir/Patient?birthdate=eq1965-01-20, should his record also appear?

My answer to this question is NO, b/c according to his record, it says: "birthDate": "1965-01-19T23:30:00-09:00", and mine says "1965-01-20T03:30:00-05:00".

So, how does one turn the query parameter 1965-01-20 into an appropriate value to probe for these records, and how does one turn our dates of birth into appropriate values in an index in order to query these correctly?  I had originally thought to use the querant's time zone as part of the answer, but then you get different answers to the question based on where the querant is at the time (and since I travel quite a bit, I know how confusing that would be), or based on where the system the querant is using at the time (which may not be anywhere near where they are located ... consider someone like Graham Grieve updating the HL7 site stored on AWS servers in the US from Australia).

The answer is fairly straightforward, but not obvious.  A date and a dateTime are similar, but not identical data types.  The full state of a dateTime is captured in the date, time, precision, and time zone information. While the same information can capture the full state of a date, all you need is date, and precision because time and timezone don't matter.  And since two different dateTimes can resolve to different dates, they are different species of data (same genus, but one has some extra genes as it were).

That means that to index a DateTime or Instant, you need to capture the date alone in one index field, and the timestamp (with or without timezone) in another.  Only in this way can you correctly do comparisons between date (in the query) and dateTime (in the resource).  And if you do it this way, you will find me, and not my evil twin.

When the queried value is to the day or less, compare against the date field.  When more precise, compare against the dateTime field.

Later this week, I'll take up the other direction.  Is 1965-01-20 greater, less than, or equal to 1965-01-20T03:30:00-05:00?  What about 1965-01-19T23:30:00-09:00?

————
Thanks to Ryan Moehrke (yes, he’s John’s son) for the correction on my time stamps.