OpenLMIS v3 Timezone Spike

Owned by Chongsun Ahn (Unlicensed)
Last updated: Jan 18, 2017 by Nick Reid (Deactivated)

Problem Description

OpenLMIS v3 has certain data that stores dates (and times). There is a question about how that data should be stored in the database, used in the backend, exposed through the API and used by clients (UI). There are two main types of data that would be stored using dates (and times):

  1. Dates that represent an instant in time - timezone applies. In this document, these will be referred to as instant dates.
    1. An example here would be the submitted date field of a requisition. This is the specific point in time where the requisition was submitted for authorization/approval.
  2. Dates that do not represent an instant in time - timezone does not apply. In this document, these will be referred to as business dates.
    1. An example here would be the start date of a processing period. This is a date that represents when a processing period was started. As different facilities (in different timezones) enter into this start date, the processing period has started for them.

In v2, most dates in the system were not timezone-aware. As a result, assumptions about timezone would be made, usually some system default that would be different in different contexts (database, Java, browser). There could also be confusion about exactly "when" something occurred, without timezone data.

As v3 development is underway, this pattern continues; many fields in the system are not timezone aware, or system defaults are used, which could potentially create issues (database might have one timezone default, while a microservice Java default has another, which could be different from a client's timezone, etc.).

Dates with Timezone Proposal

Persistent Storage

Since we are currently using Postgres as our backend database, Postgres' timestamp with time zone data type should be used for instant dates, and date data type for business dates.

Java Backend Code

Java 8's ZonedDateTime should be used when dealing with instant dates (and generally be in UTC), and LocalDate should be used when dealing with business dates.

Custom attribute converters should be defined for both to convert between Java and the database. If necessary, these converters should "translate" dates into UTC timezone before persisting.

Translation details:

  • For an instant date, a UTC version of it would be created, which would be the one persisted to the database.
  • For a business date, it would simply be persisted to the database.

Note: a ZonedDateTimeAttributeConverter has already been implemented in the Reference Data Service for use. This converts between ZonedDateTime and java.sql.Timestamp. However, the instant date fields that are of type ZonedDateTime will also need a JPA annotation to explicitly define the column as "timestamp with time zone". See https://github.com/OpenLMIS/openlmis-referencedata/blob/master/src/main/java/org/openlmis/referencedata/domain/SupportedProgram.java's startDate field for an example.

When it is necessary to compare the business date to an instant in time (now for example), first convert the instant into a LocalDate (thereby extracting just the date part from the timestamp), making a "best guess" about the timezone to use, and then compare. Rough steps for what timezone to use:

  1. If the code is respective to a user, use the timezone in the user's profile.
  2. If respective to a facility, use the facility's timezone (Note: this would be a new feature, as facility timezone profiles were not in v2).
  3. If neither, use an implementation default timezone (some configuration setting in the system).

API Interface

During serialization (returning dates through the API):

  • Dates should be serialized into a ISO-8601 formatted string. This is because when Jackson serializes date classes, it turns them into an array of values, which is not as useful or readable.
  • Instant dates should return a timestamp string with timezone UTC.
  • Business dates should return a date string (with no time or timezone).

During deserialization (when dates are provided by a client to an API call):

  • Instant dates should be in timezone UTC, which would deserialize into a ZonedDateTime object. If it is not in UTC, it would be "translated" into UTC.
  • Business dates should not have time or timezone information, and would deserialize into a LocalDate object.

Frontend Client (i.e. AngularJS UI)

Clients using API calls to display timestamps to user:

  • For instant dates, the client would know the user (or browser's) timezone and would "translate" the UTC timestamp into a local time for the user (or browser).
  • For business dates, the client would simply display the date to the user.

Clients making API calls:

  • For instant dates, the client should send timestamps in UTC timezone.
  • For business dates, the client should send dates with no time or timezone information.

Avoid comparing a business date to an instant in time (now for example). The reason to avoid this comparison is that the implementation calendar might use a non-gregorian calendar, so processing comparing these values needs domain expertise. If there is a need to compare a business date to a instance date, the implementation default timezone should be used — but we currently don't have or recommend a pattern for how this would be done.

Example 1: Instant Date

The example used here is the submitted date of a requisition.

  • Persistent Storage - in the requisitions table, there would be a submitteddate column of type timestamp with time zone.
  • Java Backend Code - in the Requisition object, there would be a submittedDate field of class ZonedDateTime. This field would store a timestamp with UTC timezone. If somehow it was not in UTC timezone, it would be saved in UTC to the database (using the ZonedDateTimeAttributeConverter).
  • API Interface
    • Serialization: since it is already in UTC timezone, it would simply be converted into a String of ISO-8601 format, then returned in the response. (This conversion would probably be done in the DTO).
    • Deserialization: convert the String into a ZonedDateTime object. If it is not in UTC timezone, create a UTC version of it. This is what is assigned to submittedDate.
  • Frontend Client
    • When displaying to the user, change the UTC timestamp into "local" time.
    • When calling the APIs, make sure timestamp is in UTC timezone.

Example 2: Business Date

The example used here is the start date of a processing period.

  • Persistent Storage - in the processing_periods table, there would be a startdate column of type date.
  • Java Backend Code - in the ProcessingPeriod object, there would be a startDate field of class LocalDate. When persisting, startDate would be stored as date. When retrieving, the a LocalDate object would be created based on the date, which is then used for startDate.
    • If the startDate needed to be compared to now, to see if the processing period had already started, LocalDate.now(timezone) would be used to get a LocalDate version of now. The timezone specified would be the implementation default configuration setting (or facility timezone setting if there was one). Then the now LocalDate would be compared to startDate to determine if the startDate was before (or at) the now date.
  • API Interface
    • Serialization: convert the LocalDate into a String of ISO-8601 format, then return in the response.
    • Deserialization: convert the String into a LocalDate object. This is what is assigned to startDate.
  • Frontend Client
    • When displaying to the user, use the date as-is.
    • When calling the APIs, make sure the date does not have time or timezone information.
    • Don't compare a business date with an instant date.

Appendix: Survey of Usage

Places where dates and times are being used currently (as of 2016-12-19 / 2016-12-20):

  • Reference Data
    • Facility
      • goLiveDate - Date
      • goDownDate - Date
    • RefDataErrorHandling - uses Date
    • ProcessingPeriod
      • startDate - LocalDate
      • endDate - LocalDate
      • controller (search), validator, service, repository, custom, impl classes use these
    • LocalDatePersistenceConverter, LocalDateTimePersistenceConverter - uses LocalDate and java.sql.Date
    • SupportedProgram
      • startDate - ZonedDateTime
    • SupportedProgramDto
      • This is an unusual situation because is being serialized to a string, with no time or timezone information and therefore when deserialized, it is essentially a LocalDateTime, which needs to be converted to a ZonedDateTime. We might need to serialize it to a timezone-based string.
    • ProcessingSchedule
      • modifiedDate - LocalDateTime
  • Auth
    • CustomTokenServices - uses Date to get now + validitySeconds to determine when token expires
    • PasswordResetToken
      • expiryDate - LocalDateTime
  • Requisition
    • FacilityDto, ProcessingPeriodDto, SupportedProgramDto, CommentDto, ProcessingScheduleDto, RequisitionDto
    • LocalDatePersistenceConverter, LocalDateTimePersistenceConverter - uses LocalDate and java.sql.Date
    • BaseTimestampedEntity (Comment, Requisition, RequisitionTemplate)
      • createdDate - LocalDateTime
      • Requisition controller (search), service, repository, custom, impl classes
    • FacilitySupportsProgramHelper - uses ZonedDateTime
  • Fulfillment
    • FacilityDto, ProcessingPeriodDto, ProcessingScheduleDto
    • LocalDatePersistenceConverter, LocalDateTimePersistenceConverter - uses LocalDate and java.sql.Date
    • ProofOfDelivery
      • receivedDate - LocalDate
    • OrderCsvHelper - uses LocalDate and LocalDateTime for order file columns
    • Order
      • createdDate - LocalDateTime
    • OrderService - uses LocalDateTime from order
  • Notification
    • N/A

OpenLMIS: the global initiative for powerful LMIS software