topic 13 digit time Stamp to Date and Time in QlikView

13 digit time Stamp to Date and Time

Anonymous — Tue, 19 May 2015 08:26:44 GMT

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 — Tue, 19 May 2015 08:39:54 GMT

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_ — Tue, 19 May 2015 08:39:58 GMT

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 — Tue, 19 May 2015 08:42:30 GMT

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

ganeshreddy — Tue, 19 May 2015 08:42:31 GMT

Hi try this,

time(Timestamp(1427345947170)) as time,

date(Timestamp(1427345947170)) as date,

cheers,

Ganesh

fvelascog72 — Tue, 19 May 2015 08:46:57 GMT

Hi,

Try

Timestamp((Date/86400)+25569)

Anonymous — Tue, 19 May 2015 09:13:54 GMT

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 — Fri, 26 Jul 2019 03:14:24 GMT

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.