Unexpected duplicate timestamps and missing hour around daylight-saving time

Resolved Cases
1

We’re seeing unexpected behavior when querying datetime values from a SQL Server database through CloverDX—specifically around the daylight-saving time transition.

Instead of receiving a record with a timestamp for 2:00 AM, the result includes no 2:00 AM record, and instead shows two records for 3:00 AM. This behavior only appears when running the query within CloverDX; running the same query directly in another SQL client returns the expected result, including the 2:00 AM timestamp.

2

What you’re observing is related to how Java, and by extension CloverDX, handles datetime values.

In Java-based systems like CloverDX, date and time values are internally represented as numeric timestamps (milliseconds since 1970-01-01T00:00:00Z). These numeric values are then converted to human-readable date formats using the locale and regional settings configured in the environment.

So while your database (e.g., MSSQL) stores the specific datetime value, CloverDX may display that value differently based on:

  • The locale and region settings of the CloverDX Server or Designer
  • The metadata configuration in the graph (especially the date format and data type)
  • The JVM locale settings, which can affect how dates are interpreted and formatted

This is especially noticeable around daylight-saving time transitions, where the conversion logic may shift or duplicate timestamps depending on the configured locale and how the time boundary is interpreted.

How to address it:

  1. Clarify display vs. data:
    It’s important to note that the query results are not being altered—only how those results are rendered in CloverDX. If you want to preserve the raw database format visually, you may need to work with string formats or carefully control the metadata used for display.

  2. Adjust metadata:
    In CloverDX Designer, check the metadata used on the output port of your DatabaseReader. Make sure the format and locale are explicitly defined and appropriate for how you want the values to be displayed.

    image
    image1170×404 24 KB

  3. Review locale settings:
    CloverDX inherits locale settings from the OS or JVM.

  • For more information on how locale affects behavior in CloverDX Designer, refer to the documentation on Locale and locale sensitivity.

  • To customize the locale in the CloverDX Server environment:

    • Add the -Duser.language=<language code>and -Duser.region=<country code> Java system properties to your CloverDX Server startup configuration (i.e., add them to the setenv.sh or setenv.bat file, or, if you’re running Tomcat as a Windows service, add them to the Java Options section in the Tomcat service configuration UI).