To_Timestamp and BIGINT in Qlik Sense

[polisetti]

HI Team,

I need similar functionality of converting an integer to To_Timestamp function in Qlik Sense. Below query is being used in SQL to get the required format of time_stamp in output on "Value" Field. 

Query: to_timestamp(CAST(Value AS BIGINT)/1000) AS "Time Stamp"

Input Value: 1554785760370

Output Value: 2019-04-08 21:56:00

I tried multiple functions but not able to get the same results. Could someone help with this?

Regards,

Polisetti

[ToniKautto · Accepted solution]

My assumption is your data point is stored as a UNIX timestamp. 

Looking at for example https://www.epochconverter.com/  the value 1554785760370 returns Tuesday, April 9, 2019 4:56:00.370 AM GMT.  I can get the same value in Qlik by applying the below expression;

Timestamp((UNIX / 86400000)+25569)

UNIX time is in milliseconds, so it has to be divided with 24*60*60*1000 = 86,400,000 to become number of days. 
UNIX time starts on 1 Jan 1970, while Qlik time starts on 30 Dec 1899. There are 25,569 days difference between theses two dates, which have to be added to adjust the timestamps. 

Your expectation of 2019-04-08 21:56:00 implies that you are in GMT-7, which can be adjusted for this way;

ConvertToLocalTime(Timestamp((UNIX / 86400000)+25569), 'GMT-07:00')

[polisetti]

Toni, It worked and thanks for sharing the explanation.

Regards,

Polisetti