topic Re: Convert JSON Date to Real Date in QlikView

Convert JSON Date to Real Date

tmumaw — Wed, 10 Apr 2019 11:25:29 GMT

how can I convert this json date to a real date? Thanks

/Date(1429192143000+0000)/

Re: Convert JSON Date to Real Date

hectormunoz — Thu, 04 Jul 2019 19:36:54 GMT

if I'm not wrong, that value is 04/16/2015 @ 1:49pm (UTC) (from https://www.unixtimestamp.com/index.php)

If this is correct, you can use this

Timestamp(MakeDate(1970) + subfield(vDateField,'+',1)/1e3 / (24 * 60 * 60))

Regards

Re: Convert JSON Date to Real Date

georgio_hb — Fri, 03 Jan 2020 12:48:59 GMT

What's the "1e3" part ?

Qliksense is not recognizing it neither on the front or the back end.

Did you manage to get it to work ?

Re: Convert JSON Date to Real Date

hector — Fri, 03 Jan 2020 12:56:07 GMT

1e3 = 1000 cause is in scientific notation (1 x 10^3). Because Unix timestamps are in milliseconds, so milliseconds/1e3=seconds

regards

Re: Convert JSON Date to Real Date

fostercarly — Mon, 02 Aug 2021 05:30:53 GMT

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 .