[ 
https://issues.apache.org/jira/browse/NIFI-5081?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Matt Forrester updated NIFI-5081:
---------------------------------
    Description: 
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?

  was:
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?


> 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: Major
>         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)

Reply via email to