[Bug]: Select values (and JSON input) - unable to convert string timestamps

[dave-csc]

Apache Hop version?

2.11.0

Java version?

17.0.2

Operating system

Linux

What happened?

Set up a Select values transform in order to convert a String in the form 2025-02-13T02:10:14.746582 in a Timestamp or Date object with format yyyy-MM-dd'T'HH:mm:ss.SSSXXX.

When trying to convert it to a Timestamp, the error reported is:

2025/02/14 11:04:53 - Formato output.0 - ERROR: Unexpected error
2025/02/14 11:04:53 - Formato output.0 - ERROR: java.lang.NullPointerException: Cannot invoke "java.util.Date.getTime()" because "tempDate" is null
2025/02/14 11:04:53 - Formato output.0 - 	at org.apache.hop.core.row.value.timestamp.SimpleTimestampFormat.parse(SimpleTimestampFormat.java:341)
2025/02/14 11:04:53 - Formato output.0 - 	at java.base/java.text.DateFormat.parse(DateFormat.java:397)
2025/02/14 11:04:53 - Formato output.0 - 	at org.apache.hop.core.row.value.timestamp.SimpleTimestampFormat.parse(SimpleTimestampFormat.java:448)
2025/02/14 11:04:53 - Formato output.0 - 	at org.apache.hop.core.row.value.ValueMetaTimestamp.convertStringToTimestamp(ValueMetaTimestamp.java:262)
2025/02/14 11:04:53 - Formato output.0 - 	at org.apache.hop.core.row.value.ValueMetaTimestamp.convertData(ValueMetaTimestamp.java:424)
2025/02/14 11:04:53 - Formato output.0 - 	at org.apache.hop.pipeline.transforms.selectvalues.SelectValues.metadataValues(SelectValues.java:347)
2025/02/14 11:04:53 - Formato output.0 - 	at org.apache.hop.pipeline.transforms.selectvalues.SelectValues.processRow(SelectValues.java:402)
2025/02/14 11:04:53 - Formato output.0 - 	at org.apache.hop.pipeline.transform.RunThread.run(RunThread.java:54)
2025/02/14 11:04:53 - Formato output.0 - 	at java.base/java.lang.Thread.run(Thread.java:833)

When trying to convert it into a Date, this is the error reported:

2025/02/14 11:08:39 - Formato output.0 - ERROR: Unexpected error
2025/02/14 11:08:39 - Formato output.0 - ERROR: org.apache.hop.core.exception.HopConversionException: 
2025/02/14 11:08:39 - Formato output.0 - 
2025/02/14 11:08:39 - Formato output.0 - create_date String : couldn't convert string [2025-02-13T02:10:14.746582] to a date using format [yyyy-MM-dd'T'HH:mm:ss.SSSXXX] on offset location 26
2025/02/14 11:08:39 - Formato output.0 - 2025-02-13T02:10:14.746582
2025/02/14 11:08:39 - Formato output.0 - 
2025/02/14 11:08:39 - Formato output.0 - 
2025/02/14 11:08:39 - Formato output.0 - 	at org.apache.hop.pipeline.transforms.selectvalues.SelectValues.metadataValues(SelectValues.java:353)
2025/02/14 11:08:39 - Formato output.0 - 	at org.apache.hop.pipeline.transforms.selectvalues.SelectValues.processRow(SelectValues.java:402)
2025/02/14 11:08:39 - Formato output.0 - 	at org.apache.hop.pipeline.transform.RunThread.run(RunThread.java:54)
2025/02/14 11:08:39 - Formato output.0 - 	at java.base/java.lang.Thread.run(Thread.java:833)

Similar issues happen with the JSON Input transform, when trying to read those string timestamps directly as Timestamp or Date.

Issue Priority

Priority: 2

Issue Component

Component: Transforms

[hansva]

Do you need the nanosecond information?
If you don't you can use yyyy-MM-dd'T'HH:mm:ss to get the information up to second level.
And the lowest you could go is milliseconds by removing the final 3 characters and then using yyyy-MM-dd'T'HH:mm:ss.SSS

The main issue is that the formatter we use does not support nanoseconds.

[hansva]

I will keep this issue open as the error is not propagating properly and the UI freezes in some cases

[dave-csc]

Hello @hansva,

for my project I don't need the microseconds (not nanoseconds...) info, so I solved by choosing the seconds-limited format.

However, it is still an issue for those who need this information, hence I filed this as a bug...

[wol-win]

I wanted to move from Pentaho DI to Apache Hop but this currently is a showstopper, as I have data with milliseconds and I need that granularity. So I need to stay with Pentaho DI for the time being.

[hansva]

I don't think anything has changed on that level. It's the same as what it was.

[sramazzina]

@wol-win Hop leverages on Java mechanisms to convert datatypes. In your case, where timestamps are involved, Java converts strings to timestamp up to milliseconds as @hansva correctly said. That is in Hop and the same is in Kettle of course. It is not an Hop related issue. Sounds strange to me that this is possible in PDI.