Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to display the timeStamp in milliseconds as date-time in the tooltip.
For example, 1606746957927 should be displayed as "2020/11/30 14:35:57". I have looked at various solutions but nothing seems to provide the output that I require.
Also, I intend to provide filters on data based on Unix timestamp, but I am not sure how to use the Capability APIs to perform Range queries on data. Any help on this too would be appreciated.
Hi @rajan, based on your example it seems you want to add milliseconds to a date that starts at year 1970. In that case I suggest to use some maths:
Timestamp(Date#('1/1/1970', 'D/M/YYYY') + (1606746957927 / 1000 / 60 / 60 / 24))
So you will convert your milliseconds to a timestamp by converting them in a number with integer part as number of days from 1970 and the decimals as the fraction of a day, this is why I divide by 1000 (milliseconds --> seconds) and by 60 (to minutes) and by 60 (to hours) and by 24 (to days).
JG
Unix date is based off Jan 1, 1970. Qlik date is based of Dec 31, 1899.
=TimeStamp(Makedate(1970,1,1)+1606746957927/24/60/60/1000,'YYYY/MM/DD hh:mm:ss')
Hi @rajan, based on your example it seems you want to add milliseconds to a date that starts at year 1970. In that case I suggest to use some maths:
Timestamp(Date#('1/1/1970', 'D/M/YYYY') + (1606746957927 / 1000 / 60 / 60 / 24))
So you will convert your milliseconds to a timestamp by converting them in a number with integer part as number of days from 1970 and the decimals as the fraction of a day, this is why I divide by 1000 (milliseconds --> seconds) and by 60 (to minutes) and by 60 (to hours) and by 24 (to days).
JG
Unix date is based off Jan 1, 1970. Qlik date is based of Dec 31, 1899.
=TimeStamp(Makedate(1970,1,1)+1606746957927/24/60/60/1000,'YYYY/MM/DD hh:mm:ss')
Thank You @jwjackso and @JuanGerardo , both of your solutions work.