Date String convert to JSON is failing

[sktechnet]

facing error when converting varchar to json with below error. This happens only when millisecond is missing

Error: (java.lang.IllegalArgumentException) Invalid format: “2019-10-11T23:13:19Z” is malformed at “Z”

Error row sample varchar column data: {"_id": “11111111”, “createdDate”: {"$date": “2019-10-11T23:13:19Z”}, “lastModifiedDate”: {"$date": “2019-10-11T23:13:19Z”}, “createdBy”: “john”}

success varchar: {"_id": “11111111”, “createdDate”: {"$date": “2019-10-11T23:13:19.11Z”}, “lastModifiedDate”: {"$date": “2019-10-11T23:13:19.11Z”}, “createdBy”: “john”}

query: SELECT conver_fromJSON(“column”) from dataset

[balaji.ramaswamy]

@sktechnet Below are the currently supported Date/Time formats

https://docs.dremio.com/sql-reference/datetime-formats/?parent=sql-reference

[sktechnet]

@balaji.ramaswamy how can we resolve the issue? we tried applying regexp_replace and modify the datetime which is missing millisecond. However convert_fromJSON gives error since it can not be applied on transform column

Varchar → update datetime column using regexp → convert_fromJSON → Error

[balaji.ramaswamy]

@sktechnet Are you able to provide a sample value of the field you are trying to convert to JSON?

[sktechnet]

@sktechnet

{“_id”: “11111111”, “createdDate”: {“$date”: “2019-10-11T23:13:19Z”}, “lastModifiedDate”: {“$date”: “2019-10-11T23:13:19Z”}, “createdBy”: “john”}

@balaji.ramaswamy

Sample field and value: {“$date”: “2019-10-11T23:13:19Z”}

Sample json: {“_id”: “11111111”, “createdDate”: {“$date”: “2019-10-11T23:13:19Z”}, “lastModifiedDate”: {“$date”: “2019-10-11T23:13:19Z”}, “createdBy”: “john”}

[sktechnet]

@balaji.ramaswamy any update

[balaji.ramaswamy]

@sktechnet Looks like this time format is not supported, see supported time formats

https://docs.dremio.com/sql-reference/datetime-formats/?parent=sql-reference

[lenoyjacob]

@balaji.ramaswamy Isn’t the T and Z just part of the ISO 8601 standard? Where Z is the timezone. The following works fine in Dremio if you specify what those are:

select to_timestamp('2019-10-11T23:13:19.123Z', 'YYYY-MM-DD"T"HH24:MI:SS.FFFTZD')

@sktechnet, my guess is that you should fix the millisecond issue outside of Dremio via ETL so that it follows some standard.

With Milliseconds - works:

SELECT CONVERT_FROMJSON('{"createdDate": {"$date": "2019-10-11T23:13:19.123Z"}}');

Without Milliseconds - does not work (not standard):

SELECT CONVERT_FROMJSON('{"createdDate": {"$date": "2019-10-11T23:13:19Z"}}');

Or maybe get rid of the data type specification ($date) in the JSON, in which case Dremio will recognize it as a varchar, and you can proceed to cast it to a timestamp specifying the format:

SELECT TO_TIMESTAMP(JSON.createdDate,'YYYY-MM-DD"T"HH24:MI:SSTZD') from (SELECT CONVERT_FROMJSON('{"createdDate": "2019-10-11T23:13:19Z"}') as JSON);