DATE in JSON Extract Does Not Import as DATE column type

[ahatisTW]

When we export a dataset from Paxata into JSON, the date is formatted as:

"TIMESTAMP" : "2019-03-25T04:38:46.449Z",

If we use that JSON as a data source, Paxata does not automatically recognize the format as a date value so it imports it as a text value. As a workaround, we are using a computed column with the DATEVALUE formula but it doesn't accept that string format. We're using the following:

DATEVALUE(@TIMESTAMP@ ,"YYYY-MM-dd'T'hh:mm:ss.ZZZ'Z'")

Is there a way to address this either on the export or in the formula after import?

[ebarre1 · Accepted solution]

Hello @ahatisTW

The date within the JSON file is represented as a String (" around the value) and as such will be interpreted as a String when importing the file in Paxata.

Your approach of using a computed column is correct, however the formula needs to be tweaked a bit:

DATEVALUE(@TIMESTAMP@ ,"YYYY-MM-dd'T'hh:mm:ss.SSS'Z'")

Notice that the milliseconds are presented as SSS no ZZZ.

[bstephens]

@ahatisTW

JSON data files do not support Dates as native types. It does support

• string
• number
• object
• array
• true
• false
• null

Our parser follows this standard and, as @ebarre states, we import the data as Strings.  

reference: https://www.json.org/

Thanks, 
Bill