Convert Unix epoch date time into normal date and vice versa

[qliklearnervir]

Hi ,

I have a one of the requirement where we have to convert epoch time into normal date and while calling apis again need to convert normal date time into epoch time.

Epoch time is like : 1584202999000 and 1584288399000  and its in milliseconds.

another query how to convert today's date into similar 13 digit epoch time including millisencond.

[phillipmacd]

Hi, I can help with getting the Unix Time to normal timestamp value, but haven't done it the other way around.

hope this helps, try this in you load script;

"TimeStamp(Makedate(1970,1,1)+ [Unix Time Field] /24/60/60 + Date((120/24/60) , 'YYYY-MM-DD hh:mm:ss')) as Timestamp"

***

in my case the "Unix Time" was GMT, I just added 2 hours for my required time zone { + Date((120/24/60) }

[Brett_Bleess]

If Phillip's post got you what you needed, please be sure to close out the thread by using the Accept as Solution button on his post.  

I did find a Document post that might be helpful too:

https://community.qlik.com/t5/Qlik-Sense-Documents-Videos/Dealing-with-date-formats-in-QlikView-and-...

Regards,
Brett

[thuaume]

Hello, 

did you find a way to convert from normal date to unix date ?

It would be really helpfull also, especially for an Eloqua integration where the endpoints are using Unix format to be filtered. 

Thank you for your support. 

Best regards, 

Thibaut

[Kushal_Chawda]

Probably to convert the epoch date time to normal date time

you can use below to convert epoch time to normal date and time

=timestamp((YourEpochDateField/1000/ 86400) + 25569) 

Note  : /1000 is used to remove extra '0' appended. You can divide by 10,100 based on data. Here, there is three '0' '1584202999000.  If there is no extra '0', you can simply use below formula

=timestamp((YourDateField/ 86400) + 25569)  

To convert normal date and time to epoch time

=num((now()- MakeDate(1970,1,1))*86400)  ----- This is used to convert current date and time to epoch

=num((YourDateField- MakeDate(1970,1,1))*86400)  ----- This is used to convert actual date field  to epoch