You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@hop.apache.org by "ossDataEngineer (via GitHub)" <gi...@apache.org> on 2025/03/14 21:53:07 UTC
[D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
GitHub user ossDataEngineer created a discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
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
GitHub link: https://github.com/apache/hop/discussions/5033
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
Posted by "ossDataEngineer (via GitHub)" <gi...@apache.org>.
GitHub user ossDataEngineer added a comment to the discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
@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?
GitHub link: https://github.com/apache/hop/discussions/5033#discussioncomment-12530039
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
GitHub user hansva added a comment to the discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
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.
GitHub link: https://github.com/apache/hop/discussions/5033#discussioncomment-12511468
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
Posted by "bamaer (via GitHub)" <gi...@apache.org>.
GitHub user bamaer added a comment to the discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
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.
GitHub link: https://github.com/apache/hop/discussions/5033#discussioncomment-12508349
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
Posted by "shlxue (via GitHub)" <gi...@apache.org>.
GitHub user shlxue added a comment to the discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
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:
```java
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);
}
}
```
GitHub link: https://github.com/apache/hop/discussions/5033#discussioncomment-12514386
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
GitHub user hansva added a comment to the discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
Did you add the time zone information to your format sting?
Eg format string yyyy-MM-dd'T'HH:mm:ss.SSSZ
GitHub link: https://github.com/apache/hop/discussions/5033#discussioncomment-12530237
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
GitHub user hansva added a comment to the discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
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
GitHub link: https://github.com/apache/hop/discussions/5033#discussioncomment-12548765
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
Posted by "shlxue (via GitHub)" <gi...@apache.org>.
GitHub user shlxue added a comment to the discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
DST: [Timezone Update Tool](https://www.oracle.com/java/technologies/javase/tzupdater-readme.html)
GitHub link: https://github.com/apache/hop/discussions/5033#discussioncomment-12507001
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
GitHub user hansva added a comment to the discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
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.
GitHub link: https://github.com/apache/hop/discussions/5033#discussioncomment-12548824
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am) (hop)
Posted by "ossDataEngineer (via GitHub)" <gi...@apache.org>.
GitHub user ossDataEngineer added a comment to the discussion: BUG : Apache Hop unable to convert strings to date (for DST change 9 Mar 2025 2am to 3am)
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
GitHub link: https://github.com/apache/hop/discussions/5033#discussioncomment-12544352
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org