Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Can not convert 6 digit milliseconds in NiFi. String to toDate() conversion problem in NiFi.

avatar
Explorer

Hello All and @Shu_ashu,@MattWho

I have a CSV file with a time column "Apr 15, 2020 02:02:16.388267000 Paris, Madrid (heure d’été)".... "Apr 15, 2020 02:02:16.986879000 Paris, Madrid (heure d’été)"...etc.
Then I to convert the field time using UpdateRecord processor by the following code:

insert_photo.JPG${field.value:substring(0,28):toDate("MMM dd,yyyy HH:mm:ss.SSSSSS"):format("yyyy-MM-dd HH:mm:ss.SSSSSS")}


But after conversion, the output I get is:

nbtimedelta
12020-04-15 02:08:44.0002670.0
22020-04-15 02:18:42.0008790.598612

 

But the output should be:

nbtimedelta
1Apr 15, 2020 02:02:16.3882670.0
2Apr 15, 2020 02:02:16.9868790.598612

 

So, I always get 000 before the milliseconds. As per the example above, I got 000267 instead of 388267 and 000879 instead of 986879.


Why I am getting the 000 instead of the real value? How can I modify me code to resolve this problem? Any help on this issue will be highly appreciated.

2 ACCEPTED SOLUTIONS

avatar
Expert Contributor

Hi @DarkStar 
Your Expression Language(EL) should be

${field.value:substring(0,28):toDate("MMM dd,yyyy HH:mm:ss.SSSSSSSSS"):format("yyyy-MM-dd HH:mm:ss.SSSSSS")}

In your  EL, the pattern you used has 6 "S". But, the input has precision upto 9. Since, you gave 6, it is reading the last 6 characters, i.e., 388267000. I think, that must be the reason.

View solution in original post

avatar
Explorer

Used below formula to resolve my problem

${field.value:substring(0,21):toDate("MMM dd,yyyy HH:mm:ss"):format("yyyy-MM-dd HH:mm:ss")}.${field.value:substring(22,28)}

 

We separately added last 6 digit using .${field.value:substring(22,28)} after the time format.Otherwise, nifi can not process 6 digit microsecond.

View solution in original post

3 REPLIES 3

avatar
Expert Contributor

Hi @DarkStar 
Your Expression Language(EL) should be

${field.value:substring(0,28):toDate("MMM dd,yyyy HH:mm:ss.SSSSSSSSS"):format("yyyy-MM-dd HH:mm:ss.SSSSSS")}

In your  EL, the pattern you used has 6 "S". But, the input has precision upto 9. Since, you gave 6, it is reading the last 6 characters, i.e., 388267000. I think, that must be the reason.

avatar
Explorer

Hi @PVVK, thanks for your feedback. I trued your approach but its giving me the same result. Is NiFi (java) only support 3 digit in milliseconds? Do you have any idea? Is there any other alternative that I should try?

 

DarkStar_0-1600716443925.png

avatar
Explorer

Used below formula to resolve my problem

${field.value:substring(0,21):toDate("MMM dd,yyyy HH:mm:ss"):format("yyyy-MM-dd HH:mm:ss")}.${field.value:substring(22,28)}

 

We separately added last 6 digit using .${field.value:substring(22,28)} after the time format.Otherwise, nifi can not process 6 digit microsecond.