OpenLMIS v3 Timezone Spike

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 created date field of a requisition. This is the specific point in time where the requisition was created and stored.
2. Dates that do not represent an instant in time - timezone does not apply. In this document, these will be referred to as general dates.
   
   1. An example here would be the expiration date of a drug. This is a date, but it represents when this drug generally expires.

In v2, most dates in the system were not timezone-aware. As a result, the system is not able to support a country/region with multiple timezones. 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 general dates.

Java backend code

Java 8's ZonedDateTime should be used when dealing with instant dates, and LocalDate when dealing with general dates. Custom attribute converters should be defined to convert between Java and 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.

API Interface

During serialization (returning dates through the API), dates should be serialized into a String first. This is because when Jackson serializes date classes, it turns them into an array of values, which is not as useful or readable. The serialized String should be in an ISO-8601 format.

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

• Instant dates should have timezone information added. Because we cannot depend on clients (browsers) to understand complex timezone scenarios (sending a date in UTC that’s for a facility in UTC+1 for a browser that’s in Seattle running UTC-8), we should assume that instant dates are passed in without timezones and the backend code will do a timezone "translation". Rough steps for this translation:
  1. Get the timestamp from the client
  2. Ignore/strip any timezone information
  3. Make a "best guess" about the timezone to use
     1. If the API call is respective to a user, use the timezone in the user's profile
     2. If respective to a facility, use the facility's timezone
     3. If neither, may have an implementation default timezone (although this may not work with an implementation that spans multiple timezones)
  4. Append the "best guess" timezone to the timestamp and use that in the backend and database
• General dates should be in ISO-8601 format, deserialize into a LocalDate object properly and should be left alone.

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