Matt Forrester created NIFI-5081:

             Summary: Lack of guidance and inability to deal with ISO-8601 dates
                 Key: NIFI-5081
             Project: Apache NiFi
          Issue Type: Improvement
          Components: Documentation & Website
    Affects Versions: 1.6.0
         Environment: Ubuntu / Chromeium
            Reporter: Matt Forrester
         Attachments: y.xml

I want to apologise for this, it probably seems very rant like and I've tried 
quite hard to make it not sound that way but I think there's some bugs in there 
and probably the need for some documentation too. I figure it is best to give 
you the problem rather than me try to prescribe a solution:

I've got a Node process that outputs in JSON onto an SQS queue. The dates it 
spits out are ISO-8601 dates within a string, which is the normal, default and 
best way to do this in JSON.

I tried putting them into MongoDB with PutMongo and they go in as strings, 
which is not good.

I then learnt all about AVRO and it's logicalTypes because I thought that would 
give enough metadata for PutMongoRecord to know that my ISO-8601 string is 
actually a date. I seem to remember them still going, but still as strings. 
This was now so long ago I cannot be sure...

Gave up on Mongo and tried PostgreSQL...

Figuring I was in Java land I used an esoteric path of GetSQS -> 
EvaluateJsonPath -> UpdateAttribute [ 
"$\{time:toDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", "GMT") ] -> PutSQL to get it 
into what I assume is a java.lang.Date, it took me forever to find the 
sql.args.N.type's required but for some reason PutSQL does not like 

Tried formatting them as strings in DD-MM-YYYY HH:MM:SS and then putting then 
keeping it in ISO-8601 format as that is also valid in PostgreSQL but no luck.

Eventually found the ConvertJSONToSQL processor and this created my SQL for me, 
but it doesn't work as it leaves ISO-8601 dates as ISO-8601 dates, which don't 
seem to work.

Eventually found this 
 and now I have something working, but I'm using my esoteric GetSQS -> 
EvaluateJsonPath -> UpdateAttribute -> PutSQL path again.

In any case it's still rubbish because my format 
"${time:toDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", "GMT"):toNumber()}" within 
UpdateAttribute includes a 'Z' to denote UTC but who is to say it's not in the 
"+00:00" style, or indeed from a different time zone.

Surely the only good/correct way to deal with this is to properly parse an 
ISO-8601 date and surely ConvertJSONToSQL should pretty much just work?

This message was sent by Atlassian JIRA

Reply via email to