Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
polisetti
Creator
Creator

To_Timestamp and BIGINT in Qlik Sense

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

Labels (2)
1 Solution

Accepted Solutions
ToniKautto
Employee
Employee

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

 

View solution in original post

2 Replies
ToniKautto
Employee
Employee

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
Creator
Creator
Author

Toni, It worked and thanks for sharing the explanation.

Regards,

Polisetti