DATE in JSON Extract Does Not Import as DATE column type

Highlighted
NiCd Battery
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?
Labels (1)
0 Kudos
2 Replies
Highlighted
NiCd Battery
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.

Image: https://us.v-cdn.net/6030933/uploads/editor/69/if072ofnrlyz.jpg
0 Kudos
Highlighted
Linear Actuator
@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
0 Kudos