topic Convert Unix epoch date time into normal date and vice versa in QlikView

Convert Unix epoch date time into normal date and vice versa

qliklearnervir — Sat, 16 Nov 2024 18:54:24 GMT

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.

Re: Convert Unix epoch date time into normal date and vice versa

phillipmacd — Mon, 23 Mar 2020 08:37:27 GMT

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) }

Re: Convert Unix epoch date time into normal date and vice versa

Brett_Bleess — Tue, 14 Apr 2020 20:49:32 GMT

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-QlikSense/ta-p/1484207

Regards,
Brett

Re: Convert Unix epoch date time into normal date and vice versa

thuaume — Mon, 15 Jun 2020 09:46:31 GMT

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

Re: Convert Unix epoch date time into normal date and vice versa

Kushal_Chawda — Mon, 15 Jun 2020 11:03:28 GMT

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