How to get timestamp attribute from a python data set, using Pandas, with source dateTime attributes in ISO 8601

Question

Hello, 

We use Pandas.read_json(url), to retrieve data from a python data set, we can get json data to be returned in a dataFrame, and usable in Knowage.

But we can't manage to get timestamp recognized in some attributes in ISO 8601 format (yyyy-MM-ddTHH:mm:ss:msz)

Which dataType should we use in the output Pandas dataFrame, to be recognized as a timestamp field by Knowage, instead of string ?

thanks in advance for any help

Comments

I suspect an issue in knowage-python or knowage, as soon as you try to set a field as dateTime, with 

df['dateModified'] = pd.to_datetime(df['dateModified'])

an error is sent back at script PREVIEW : "Data is neither a JSON object nor a JSON array"

In knowage-python container logs we can see that the column is correctly set to 'datetime64[ns, UTC]'

but when dataFrame is processed by knowage-python this above error occurs...

this error is sent from knowageutils/src/main/java/it/eng/spagobi/tools/dataset/common/datareader/JSONPathDataReader.java class, which is used by REST data set...

it seems there's no jsonPathType allowing to tell an attribute is a timestamp or datetime...

Answer 1

Obviously, it seems there's missing piece to manage date and timestamp with python dataset, using the Pandas dataFrame...

I tried many tricks to workaround the issue, without success, and look at the code

Something should be done to propagate correctly date and timestamp data types, 

and something to prepare correctly the metadata for dates and timestamps, in case of python data set....

Most probably it's worth an issue in Knowage-Server project !

Answer 2

Hi alaing,

thank you for your report!
We will work on the GitHub issue as soon as possible!

Bye,
Marco

Comments

Hello @mbalestri, I made an issue : python data set : to be able to recognize date and timestamp attributes from Pandas dataFrame #227