Happy Birthday from Auckland, or, no more time zone nightmares!
What could be simpler than entering your birthday into a form and having it saved and retrieved later as needed? Sure it looks easy. But maybe not.
Here is why just saving and displaying back a simple date of birth can be a nightmare for a geographically diverse organization.
Consider a Bonita BPM application deployed worldwide, with 3 different environments, each one in a different time zone:
- the end user’s computer, tablet, or phone
- the Java EE application server that hosts the Bonita BPM web application
- the RDBMS that stores your business data
When date-of-birth information makes its way from the end user web browser to the database, it will go through a series of conversions:
- from JSON text to Java java.util.Date (if you are using a Bonita version earlier than 7.5)
- from java.util.Date to java.sql.Date
- from java.sql.Date to the SQL TIMESTAMP format
- from SQL TIMESTAMP to the specific format defined by the database vendor
In each conversion from one type to another, there may be issues related to time zones.
Let’s take an example. Walter, who lives in San Francisco, is filling in a form on May 23, 2017 at 22:00 PDT - and it asks him to fill in his birth date (which is June 18, 1980).
But it is an issue for Hèlene. Hèlene is Walter’s colleague who is in France and needs to access Walter’s birth date information.When her system tries to render the date-and-time data from Walter, it converts from UTC to her local time zone and displays only the day-month-and-year, which is no longer Walter’s birth date! In her time zone (CEST, UTC +2) the date-and-time becomes June 19, 1980 07:00 CEST, which is displayed as June 19, 1980. (Note: we assume that Date objects are shared across web browsers using a neutral backend.)
In the same way, when the date is displayed it is always processed as a date in UTC. This correctly displays dates in forms - as long as the storage on the server side does not introduce any time zone conversion.
So let’s move over to the server side.
The date is received as a JSON string (e.g. 1980-06-18T00:00:00.000Z) in UTC. (This is the date widget behavior enforced by the contract in Bonita). The JSON string to java.util.Date conversion works fine as both are using UTC.
The next step is to store the java.util.Date in the database. As a java.util.Date includes date and time, it gets converted to an SQL timestamp type.
This conversion will again lead to unexpected behavior if the Java Virtual Machine does not use UTC. Let’s say that the Java application server is in the EDT time zone (UTC -4). In this case June 18, 1980 00:00 UTC will be saved as June 17, 1980 20:00 in the database. So once again we have a mismatch between the user input and what is stored. If we query the database with any standard tools we will get a different date from Walter’s input.
This is where nightmares come from: a simple use case like saving a birth date is much more complex that it appears.
We wanted to address this and create fewer nightmares for our end users (and application developers!) So let’s take a look at the solution available with Bonita BPM 7.5.
If you want to store a birth date, you really only want to store a date with no need to store time or time zone information. It’s ok if your friend wishes you a happy birthday one day too early because they are in Auckland and you are in Anchorage!
In Bonita BPM 7.5, you can select the “date only” type for your business object attributes. This will use the new Java 8 java.time.LocalDate type which is stored as a ISO 8601 text representation in the database, so there is no unwanted shift due to mismatched time zones. The date picker widget itself remains unmodified as it’s already correctly sending the date information as JSON text. The parsing on the server side has been updated to have the capability to convert from JSON to LocalDate.
Now there’s a solution to store simple dates like “date of birth” and display them independently of the time zone used by your servers and the time zones of your users.
For other use cases, Bonita BPM supports date and time with time zones (java.time.OffsetDateTime) and data and time without time zones (java.time.LocalDateTime), both stored as ISO 8601 text in the database.