Matt Forrester updated NIFI-5081:
    Priority: Minor  (was: Major)

> 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
>            Priority: Minor
>         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

Reply via email to