Help Parsing JSON Dates

[iamfainny]

I am struggling to convert created_at and end_time to an actual date. This data was originally in json format but was able to parse out the majority of the data except for the dates. The date fields originally came in vstring format and I cannot seem to find a way to actually make it a date. Any insight would be greatly appreciated. I can include a copy of the workflow, if needed! 

Thanks, 
Michael

[Qiu]

@iamfainny 

Would be you able to get the original format?

[BretCarr · Accepted solution]

The time fields are in EPOCH time. This is number of seconds since 1970-01-01.

Apply this on those fields:

DATETIMEADD('1970-01-01 00:00:00',[created_at],'seconds')

I’m not on my computer so give the formula a shot and let me know! 

[Qiu]

@iamfainny 

After some research on the internet, I think this format of time is called Epoch & Unix Timestamp.

https://www.epochconverter.com/

[Qiu · Accepted solution]

@BretCarr 
OMG, look at what I have done...🙄

Anyway, you are correct

[iamfainny]

@Qiu @BretCarr  You guys rock, thank you!  Epoch & Unix Time... I was trying to figure out what format it was in. Thanks again!!

[BretCarr]

Happy to help!

[arpit_tyagi]

@Qiu 

wonderful Explanation and it is really helpful.

Was wondering how json can be converted to normal dates and you just gave not only date but even minutes , seconds too.

Kudos to you!

Regards

[fostercarly]

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 .