Convert Millisecond timeStamp to date time (yyyy/mm/dd hh:mm:ss)

[rajan]

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.

[JuanGerardo · Accepted solution]

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

[jwjackso · Accepted solution]

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

[rajan]

Thank You @jwjackso and @JuanGerardo , both of your solutions work.