The Trouble with Timezones, Yet Again

Development
,
1

I feel like I’m writing a Talk post every year or so dealing with the problems of time zones :slight_smile: and as I’ve been going through the O3 app I’m running into some new issues, but they are generally caused by one issue that seems tricky to solve:

How do we represent a simple date (with no time) within a timezoned datetime data structure?

That is, how do we represent “May 30th, 2023” using a JavaScript Date object in a way that can be consistently interpreted across time zones?

The most common examples of this problem are when entering an encounter dates, observation of type date, or a program enrollment/completion dates.

For example, say that we are entering a retrospective encounter and use a datepicker to set the encounter date. No time component is set, and so our general way of handling that is to set the time component of the encounter date time to 00:00.

But then, if you view that data in a time zone that is behind the entry time zone, the date is shifted to the day before (ie … if you enter in EDT and view in PDT, then 2023-05-31T00:00:00.000-0400 gets converted to 2023-05-30T21:00:00.000-0700) and f you view the data in the time zone that is ahead of the entry time zone, you get the correct date, but there’s now a time component so it’s unclear if you really mean the encounter happened at that specific time, or are just specifying that it happens on that day (ie … if you enter in EDT and view in UTC, then 2023-05-31T00:00:00.000-0400 gets converted to 2023-05-31T04:00:00.000-0000, which could be interpreted as an encounter that actually happened at 4 in the morning UTC).

I’m not sure what the answer is, and we may just have a fundamental problem that we have fields like “encounter_datetime” that we sometime want to have a time component (generally when entering encounters point-of-care, real-time) and sometimes don’t (generally when doing retrospective entry where we usually only know the date an encounter occurred, not the time).

We’ve hacked around this on the server in O2 (generally by having the client strip off the timezone it receives from the server when the time is “00:00”, thereby converting it from “00:00” in the server timezone to “00:00” the client one), but I don’t think we’ve ever had a satisfactory solution to this (and I’m not sure if there actually is one).

In the React Form Engine, in an attempt to work around this, it appears we are converting a date selected with a datepicker to a local datetime that would map to 00:00 UTC for that specific date. For instance, if you you are running in EDT and pick a date of May 31st, it converts it to 2023-05-30T20:00:00.000-0400, which would map to 2023-05-31T00:00.000-0000, but it’s unclear to me this will fix things, and specifically it is causing an issue I’ve documented here: [O3-2154] - OpenMRS Issues

I’ll try to dive into this more and and add what I find, but @dkigen @dkayiwa @mksd @mksrom @ddesimone @ibacher @samuel34 @mseaton @burke and others, interested in your take…

Take care, Mark

2

So ChatGPT recommmends:

“If you want to ignore timezones and ensure consistency across different timezones, you can extract the date components and construct a string representation of the date.”

3

Some dates are meant to be timezoneless (or absolute), for instance - simply - the good old date of birth. Those IMO should be handled differently. Strings, maybe, it depends what is anticipated to happen with them (such as calculations or not for example).

In a way it’s a BA task. Each time one involves a date in their data modeling they should ask themselves the question: is this an absolute date or is this a plain datetime? If the former they should be recorded appropriately (maybe as strings or as a triple date point date + month + year), if the latter they should be recorded as full datetime plus timezone info (ISO 8601).

Your example of the retrospective encounter is precisely the tricky case. Looks like there should have been two members to the encounter model:

  1. Actual datetime
  2. Respective (or past) date, when the former one is unknown (null)
4

This is one of those things I’ve been planning on posting about (improving date & time support in the Platform). You beat me to the topic this time, @mogoodrich. :slight_smile:

I believe we want to work toward:

  • Avoid record date and time without a timezone. Anytime we record a date and time, we store precise time (i.e., timestamp, including timezone). This way, we can minimize assumptions about timezone to those cases where we receive a date & time without a timezone, make explicit rules about how these cases are handled, and avoid any ambiguity once timestamps are stored.
  • Adopt a system-wide convention for handling dates & times where partial or non-absolute values are needed. FHIR’s approach is to use strings (e.g., allowing partial date as “YYYY”, “YYYYMM”, or “YYYYMMDD”).

FHIR avoids some of the reality of implementation by allowing partial dates everywhere. For example, the encounter datetime in FHIR (Encounter.actualPeriod.start) is a dateTime, which allows partial dates. I don’t think we want to spend time coding against the possibility of an encounter happening “sometime in 2023”. :slight_smile:

I don’t think prospective vs. retrospective is the issue here; rather, it’s a matter of whether or not time is required for an encounter. So, what should be our convention for partial date support?

                                                            
  • Store date & time separately
 Breaks the rule of having date and time without a timestamp
  • dateTime (timestamp)
  • dateTimePartial (string)
  • dateTime = null when partial datetime used
 Would hamper queries (e.g., querying encounters by date would be slow or might miss data)
  • dateTime (timestamp)
  • dateTimePartial (string)
  • dateTimePartial = null when time is precise
 This would have the benefit of supporting both precise & partial date ± time while still allowing for most queries use dateTime (i.e., only needing to go to partial date for “truth” when timezone issues like the one @mogoodrich describes arise).

If we like this last option, we could start adopting it as a convention for supporting partial dates in a way that allows for precise or partial while constraining the business of dealing with partial dates only to where it’s needed. It would also take another small step toward alignment with FHIR.

5

Thanks @burke !

Not sure why I didn’t think of WWFD (what would FHIR do) because that has been my default approach for everthing else. Thanks for pointing to the dateTime FHIR data structure (Datatypes - FHIR v5.0.0).

I started writing up some thoughts but then realized they needed a little more rumination first, so I will follow up in the coming days… :slight_smile:

Take care, Mark

6

@mogoodrich, the more I ponder the way to improve timezones & partial date support, the more I think my last proposed approach is the right direction, since it both solves our problems and aligns us with FHIR. Put simply:

                                                        
  • dateTime (timestamp)
 Any resource that needs a timestamp gets a fully specified timestamp, including timezone. We actively work to eliminate any cases where date & time are stored without a timestamp (i.e., the current approach in the OpenMRS model).
  • dateTime (timestamp)
  • dateTimePartial (string)
 Any resource that needs to support partial dates (i.e., any cases where a date might not include everything down to specific timezone), we store the official “truth” in dateTimePartial following FHIR’s dateTime string format rules and dateTime becomes the “effective” timestamp (i.e., a suitable proxy inferred from the partial date). This allows the dateTime as a suitable proxy for queries & coding (without every bit of code having to think about how to handle partial dates) while dateTimePartial can be displayed to the user and represent the truth of what is known/recorded.

In a case where we only have a partial date (i.e., don’t expect to ever have an exact time, like birthdate), this pattern could still be useful. For example, consider how this approach could be applied (and benefit) our approach to birthdate:

Current State                                                                Future State
  • birthdate (date)
  • birthdate_estimated (boolean)
  • Cannot distinguish between birthdate known to year vs. known to year+month
  • All code using birthdate must be aligned in how to consistently interpret estimated birthdates
  • birthdate (timestamp)
  • birthdatePartial (string)
  • Allows birthdate to be recorded with as much precision as desired (including down to the hour or minute)
  • Birthdate can be estimated explicitly to YYYY or YYYYMM
  • The rule for how partial birthdates should be interpreted is in one place (i.e., converting birthdatePartial into birthdate at the time of recording it) and all queries/code can simply use birthdate and, if ever needed should any cases arise where timestamp is a problem, can refer to birthdatePartial to work around any “inferred timezone” issues)

The more widely we adopt this pattern, the more we eliminate use of MySQL’s timezone-less (and thereby ambiguous) datetime in the database and the more closely we align with FHIR. :slight_smile:

7

Thanks Burke!

I had hoped to get back into this week, but that didn’t happen, and now I’m off for a couple weeks, so I’ll need to pick it up when I get back… I do think what you said might work, but I want to figure out/prototype how that would work on the front end, and potentially come up with a shorter-term approach that allows us to move forward before all the backend changes get committed and released…

Take care, Mark

8

No worries @mogoodrich. Makes sense. My hope is agreement on the vision/destination will make it easier to come up with short term solutions, since it’s easier to judge whether they are taking us in the right direction.

9

Oh, absolutely, agreed.

10

Coming back to this with a proposal/vision for a destination for date & time handling within the OpenMRS Platform…

To date (pun intended), OpenMRS has used MySQL’s datetime data type to store dates & times and we use a boolean “estimated” workaround (e.g., birthdate_estimated flag) to indicate when a date ± time is an estimated value. This has failed us on multiple fronts:

  • Fails to support partial dates/times in many cases where data model doesn’t have “estimated” workaround
  • Estimated dates/times are ambiguous (our boolean workaround fails to distinguish between estimates to day, month, year, etc.
  • All of our time data are ambiguous (no timezone information stored in the database)
  • Not aligned with FHIR

Proposal for better date/time support

Scenario Approach
Date & time with partial date/time support
  • dateTimeValue (string) in FHIR format to explicitly support partial data (to year, to month, to date, etc.) as well as precise data, including timezone
  • dateTime (timestamp) stores “effective” timestamp, including timezone
Date & time without need for partial date/time support
  • dateTime (timestamp) stores date/time, including timezone
Date with partial date support
  • dateValue (string) in FHIR format to explicitly support partial data (to year, to month, to date)
  • date (date) stores “effective” date, interpreting partial data into suitable proxy (e.g., “2023” → 2023-01-01)
Date without partial date support
  • date (date)
Time with partial time support
  • timeValue (string) in FHIR format to explicitly support partial data (to hour, to minute, to second, etc.)
  • time (time) stores “effective” time, interpreting partial data into suitable proxy
Time without partial time support
  • time (time)

Assumptions

  • Changes the data model could be rolled out over time or we could have a date/time hackathon to refactor across the board.
  • During the transition of datetime data, server timezone would be assumed when converting data from datetime to timestamp
  • Where we add/refactor support for partial data, any existing attribute would become the interpreted value and the string “value” attribute would be added to the data model for backwards compatibility (any code dealing with partial data would be refactored to use the new string attribute and place the suitable proxy date/time value into the existing attribute)

Example

  • person.birthdate_estimated (boolean) is deprecated
  • person.birthdateValue (string) is added to contain actual data collected (full or partial) in FHIR format
  • person.birthdate (date) remains as an interpretation of birthdateValue (e.g., “2023” → 2023-01-01 just as we do now)

All code reading person.birthdate could continue to hum along unchanged; however, we would now be able to explicitly store and faithfully reproduce estimated birthdates like “1980” or “May 1980”.

11

Just wanted to say thanks @burke ! This has been sitting in my inbox for about a week to review, and I still haven’t gotten to it… :slight_smile: