BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)

[ossDataEngineer]

Hello Apache Hop Team,

Context:

I have a JSON dataset with datetime fields that have values falling between 9th March 2.00 am to 3.00am. Daylight Savings Time (EDT) was implemented during this time. I am using JSON Input shape to parse the fields and while doing so also setting the datatype to 'Date'.

The JSON Input shapes fails for the all values falling between

2025-03-09T02:00:00.000+0000 to 2025-03-09T02:59:59.999+0000

Using the Format(yyyy-MM-dd'T'HH:mm:ss.SSS) to parse string json fields

The JSON Input shapes succeeds for below values:

1. 2025-03-09T01:59:59.999+0000
2. 2025-03-09T03:00:00.000+0000

Ask:

1. Is this a bug with datetime library being used by Apache Hop to do transformations?
2. Is there additional consideration when handling datetimes falling between DST change periods

Thanks,
Apache Hop Newbie

[shlxue]

DST: Timezone Update Tool

[bamaer]

DST jumps from 2am to 3am, so the hour between 2 and 3 doesn't exist, so you'll need to add 1 hour to those values or convert them to UTC or something similar.

[hansva]

Java is quite picky about dates and times, you can't create a date that does not exist.

One way to solve this is to add a second hop from your json input and use the "error handling" hop type.
This way you can keep your flow from breaking, the error lines will go to another stream and you can handle them in a different way to solve the problem.

[ossDataEngineer]

@hansva - The dates mentioned above are UTC dates. Apache Hop fails to transform the UTC datetime '2025-03-09T02:31:50.000+0000'

Is this error happening because Apache Hop is by default casting the strings to local EDT timezone and not UTC? If the error is because of default conversion to EDT, how can I force the date to be read as UTC?

[hansva]

Did you add the time zone information to your format sting?
Eg format string yyyy-MM-dd'T'HH:mm:ss.SSSZ

[ossDataEngineer]

Hello @hansva, I used the format string 'yyyy-MM-dd'T'HH:mm:ss.SSS' to parse this date - which resulted in failure. When I use format 'yyyy-MM-dd'T'HH:mm:ss.SSSZ' Hop converts the original UTC date to datetime with appropriate timezone. My goal is to parse UTC date and save it in UTC format in target database tables.

Using 'yyyy-MM-dd'T'HH:mm:ss.SSSZ' converts the original UTC datetime into localised datetime, which I want to avoid

[hansva]

There is a difference between what you see in the UI and the actual data.
The actual data is UTC (as you have provided a timezone) if you are writing to a database field with timezone support it will write the data with the timezone.
If the database does not have timezone support and it is a regular date field you can use a select values transform and in the metadata tab change the Date Time Zone to UTC.

Alternatively, you can add the following parameter to the Hop startup commands to change the application time zone.
-Duser.timezone=UTC

[hansva]

Hmmm, after looking a bit more you might be right that it is converting the date to your Time Zone which might not always be what you want indeed.

[shlxue]

DST define file(in JDK): $JAVA_HOME/lib/tzdb.dat
and the file is not static data, it will be updated continuously.
List some of DST by api of jdk:

  static int timesLimit = 220;

  public static void main(String[] args) {
    // showDst("Asia/Shanghai", 1097, 9, 15, 2, 0);
    showDst("America/New_York", 1097, 9, 15, 2, 0);
  }

  private static void showDst(String zone, int year, int month, int dayOfMonth, int hour, int minute) {
    ZoneId zoneId = ZoneId.of(zone);
    // Get the ZoneRules for the specified ZoneId
    ZoneRules zoneRules = zoneId.getRules();

    ZonedDateTime now = ZonedDateTime.of(LocalDateTime.of(year, month, dayOfMonth, hour, minute), zoneId);

    System.out.println("Current time in Asia/Shanghai: " + now);

    // Check if DST is in effect for the current date
    boolean isDST = zoneRules.isDaylightSavings(now.toInstant());
    System.out.println("Is DST in effect? " + isDST);

    // Display the transition rules
    System.out.println("Daylight Saving Time offset: " + zoneRules.getDaylightSavings(now.toInstant()));

    // Display the next DST transition
    System.out.println("Next DST transition: " + zoneRules.nextTransition(now.toInstant()));

    // Display the previous DST transition
    System.out.println("Previous DST transition: " + zoneRules.previousTransition(now.toInstant()));

    ZoneOffsetTransition next = zoneRules.nextTransition(now.toInstant());
    if (next != null && timesLimit > 0) {
      timesLimit--;
      LocalDateTime dateTime = next.getDateTimeAfter();
      int nextYear = dateTime.getYear();
      int nextMonth = dateTime.getMonthValue();
      int nextDay = dateTime.getDayOfMonth();
      if (nextDay > 28) {
        nextDay =1;
        nextMonth++;
      } else {
        nextDay++;
      }
      if (nextMonth > 12) {
        nextMonth = 1;
        nextYear++;
      }
      showDst(zone, nextYear, nextMonth, nextDay, hour, minute);
    }
  }