13 digit time Stamp to Date and Time

[Anonymous]

How do I convert a 13 digit timeStamp to Date (DD/MM/YYYY) and Time (HH:MM:SS).

For example: 1427345947170.

How do I convert this to Date and time format.

Thanks.

[ToniKautto]

What does the value 1427345947170 represent? Is it number of seconds, minutes, hours or something else? What date and time does 0 represent?

It will be possible to help you define a conversion when you have answered the questions above.

[crusader_]

Hi,

date(date('1/1/1970')+floor(A/1000/3600/24),'MM/DD/YYYY') as Date

time(time('00:00:00')+frac(A/1000/3600/24),'hh:mm:ss.fff') as Time

Hope this helps.

PFA

Regards,

Andrei

[Anonymous]

Looks like Unix epoch date timestamp, have a look at this thread.

Re: UNIX DATE to Qlikview DATE

[ganeshreddy]

Hi try this,

time(Timestamp(1427345947170)) as time,

     date(Timestamp(1427345947170)) as date,

cheers,

Ganesh

[fvelascog72]

Hi,

Try

Timestamp((Date/86400)+25569)

[Anonymous · Accepted solution]

Worked around it with the help of your replies and I got the answer.

Thank you guys.

Timestamp(25569 + Round(Parse_Timestamp/1000) / 86400) as Timestamp,

Date((25569 + Round(Parse_Timestamp/1000) / 86400), 'DD/MM/YYYY') as Date,

Time((25569 + Round(Parse_Timestamp/1000) / 86400), 'hh:mm:ss TT') as Time

[wow321654987]

This function is very helpful to anyone who has the date in number format like this: 

1563123600000.

I still dont know what is the format of this, but the function his helpful.