Matt Forrester created NIFI-5081: ------------------------------------ Summary: Lack of guidance and inability to deal with ISO-8601 dates Key: NIFI-5081 URL: https://issues.apache.org/jira/browse/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 java.util.Dates. 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 [https://community.hortonworks.com/questions/84772/putsql-with-date-as-argument.html] 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 (v7.6.3#76005)