Inconsistent time zone offsets for localized dates prior to 1937

Issue

When parsing and localizing dates from Excel data prior to 1937 using the Java Action from Community Commons Function Library, dates display inconsistent time zone offsets instead of the expected Coordinated Universal Time (UTC)+1 offset. For example:

• A date from 1900 shows a shift of 34 minutes and 8 seconds instead of 1 hour
• A date from 1850 shows a shift of 29 minutes and 46 seconds instead of 1 hour
• Dates from 1937 onwards display correctly with the expected UTC+1 offset

The dates are stored correctly in the database in UTC format, but the localization logic applies non-standard offsets for historical dates, causing display inconsistencies in the user interface.

Environment

Applications hosted in any deployment type

Cause

This behavior is due to historical changes in timekeeping practices and time zone definitions. Time zones and daylight saving rules were not standardized globally until the 20th century. Different regions had their own local times based on the position of the sun, which resulted in non-uniform offsets from UTC.

For example, the Netherlands standardized its civil time in 1909. Until then, each location in the country observed its own solar time. In 1909, all clocks in the Netherlands were synchronized with solar time in Amsterdam, which was 19 minutes and 32 seconds ahead of Greenwich Mean Time (GMT). In 1937, the country's standard time was adjusted by 28 seconds for simplicity's sake. Technically, there was no standardized time zone before 1937.

Mendix calculates localized date time the same way other systems do by respecting historical time zone data. Therefore, this behaviour is not a platform bug - it is expected behavior when handling historical dates.

Solution/Workaround

The following approaches address the issue when working with dates prior to 1937:

1. Research historical timekeeping: Research the historical timekeeping practices for the specific region in question. Understanding that the offset from UTC was not a neat hour increment can help explain the discrepancies.
2. Implement custom parsing logic: Create custom parsing logic for dates prior to 1937. Standard Java functions do not handle these historical discrepancies as expected. Create a custom Java action or microflow that adjusts the dates based on known historical offsets.
3. Store dates without time zone: Store dates prior to 1937 without any time zone information if the exact time is not critical. Store them as simple date strings or in a neutral time zone (for example, UTC), and apply any necessary conversions during display.
4. Use a historical time zone database: Utilize a historical time zone database that accurately reflects the timekeeping practices of the past. Libraries such as Joda-Time or java.time in Java include more accurate historical time zone data.
5. Manual adjustments: If the number of historical dates is manageable, manually adjust the dates based on the historical research conducted. This serves as a temporary solution until a more automated approach is developed.

Note: Assistance with the implementation of any of these options falls outside the scope of Mendix Support. For implementation help, contact the Mendix Community forum, contact Expert Services through the Customer Success Manager, or engage with a Mendix partner for a consultancy service.

Additional information

• Mendix documentation:
  • Community Commons Function Library - DateTime
  • Mendix Support

• External resources:
  • Time Zone History - Netherlands