The Trouble with Timezones, Yet Again

Development
,
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”.