Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
polisetti
Contributor

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
Employee
Employee

Re: To_Timestamp and BIGINT in Qlik Sense

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

 

2 Replies
Employee
Employee

Re: To_Timestamp and BIGINT in Qlik Sense

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
Contributor

Re: To_Timestamp and BIGINT in Qlik Sense

Toni, It worked and thanks for sharing the explanation.

Regards,

Polisetti