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)
