Date conversion from dataset to JSON messages
![Ankur5289](https://us.v-cdn.net/6038231/uploads/Dataiku/nAvatar8.png)
I have a dataset in dataiku that has date column in date format and in unix timestamp format.
For example the date is like 2020-06-16T13:08:05.205Z
And when i convert this dataset to individual json file format , this same date column gets transformed to some numerical format.
for example this same date gets transformed as below as shown in bolds.
[
{
"legalEntity":IND,
"docId":181cb33d4c0b97dfbef678ac47c255bd,
"user":BLUE YONDER,
"timestamp":1592312885205,
We want the timestamp to be in same format as in dataset. Can anyone please throw light on this and help us to resolve this ?
Answers
-
Ankur5289 Partner, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered Posts: 27 Partner
i now understand this value means number of mili seconds passed since 01 jan 1970. But what if we do not want this to be present in the json file? we want the same date to be displayed as per the data set.
-
Hi @Ankur5289
,Assuming you are using pandas to convert the dataset to JSON, you can use mydataset_df.to_json(date_format = 'iso') to convert "epoch" to "iso" time. Please refer to the code sample and the screenshot below:
mydataset = dataiku.Dataset("json_test") mydataset_df = mydataset.get_dataframe() #defauld "epoch time" input_json_epoch = mydataset_df.to_json() print("epoch formated: {}".format(input_json_epoch)) #convert to "iso time" input_json_iso = mydataset_df.to_json(date_format = 'iso') print("iso formated: {}".format(input_json_iso))
Hope this helps.
Regards,
Vitaliy
-
JSON does not know anything about dates. What .NET does is a non-standard hack/extension. The problem with dates in JSON and really JavaScript in general – is that there's no equivalent literal representation for dates. In JavaScript following Date constructor straight away converts the milliseconds since 1970 to Date as follows:
var jsonDate = new Date(1297246301973);
Then let's convert it to js format:
var date = new Date(parseInt(jsonDate.substr(6)));
The substr() function takes out the /Date( part, and the parseInt() function gets the integer and ignores the )/ at the end. The resulting number is passed into the Date constructor .