16 digit # (exp:1170410000000000) from DB2 table - convert to time

[mp802377]

Hello,

I have this 16 digit number (example: 1170410000000000) from a DB2 table. I need to make it a time. I have searched the forums and tried this: Date(MakeDate(1970,01,01) + YourDate / 24 / 60 / 60). That didn't work. I also tried TIMESTAMP(Timestamp,'DD-MM-YYYY HH:MM:SS') as Date, and that didn't work either. This isn't a hexadecimal. I looked through the data and don't see any letters.

How do I make this a date?

Below is an example of my data. I just pulled this qvd into the Qlikview application. I changed the field names for the most part. I don't have anything else in the file.

value one	value 2	value3	value 4	value 5	value 6	value7	size	Timestamp	TMZDIFF	WRITETIME
0	0	0	0	0	0	0	0	1170410000001000	0	1170410000000000
0	0	0	0	0	0	0	0	1170410000001000	0	1170410000000000
0	0	0	0	0	0	0	0	1170410000001000	0	1170410000000000
7.97	7.97	8.11	7.97	8.11	56400	8.11	1	1170410000001000	0	1170410000000000
39.99	39.99	0	39.99	0	307	0	1	1170410000001000	0	1170410000000000
180.76	180.76	178.32	180.76	178.32	11902	178.32	5	1170410000001000	0	1170410000000000
203.26	203.26	0	203.26	0	32	0	7	1170410000001000	0	1170410000000000
335.31	335.31	0	335.31	0	135	0	7	1170410000001000	0	1170410000000000
599.24	599.24	592.26	599.24	592.26	5271	592.26	5	1170410000001000	0	1170410000000000

Thank you,

Martha

[Anil_Babu_Samineni]

Try this

=Timestamp(25569 + Round(WRITETIME/1000) / 86400)

[Anil_Babu_Samineni]

Or this

=Timestamp(25569 + Round(WRITETIME/1000000) / 86400)

It returns as -- 02-02-2007 09:53:20

[mp802377]

No this didn't work. On both examples I get a - .

[Anil_Babu_Samineni · Accepted solution]

IS this, you are expecting?

Timestamp(25569 + Round(Evaluate(Timestamp)/1000000)/86400)

[mp802377]

That worked. This is interesting. It works in the load script but not as an expression. Thank you so much for your help on this.

Martha

[mp802377]

I thought it worked, but then I looked at the date. This data is from last week. The dates should be June, not February.

Any ideas? This is from a DB2 table.

Martha

[Anil_Babu_Samineni]

Glad i was able to offer you better than earlier

Q) Can you tell us one thing, Whether which date and Year and Time period for this 1170410000001000 And this will make lot better if you know the answer

[mp802377]

I figured it out. I don't know why the person set the date up this way, be they did. There are a lot of rows and it is minute data. So 1170410000001000  = 04/10/2017 00:00:01. Once I realized he took of the 1 in front of the number it became easier to read. And us storing less than two months of data sounds about right.

Thank you for your help on this. I am sorry it was created this way. I think I have it from here. Again, thank you so much. I hope this didn't waste too much of your time.

Martha

[Anil_Babu_Samineni]

Hmmm. I thought QlikView has IEEE double-precision floating point as its internal format. That gives you a 1 bit sign, 11 bit exponent, and 52 binary digits. 2^53 = 1,170,410,000,001,000 so you should have 15 to 16 decimal digits available.

Unfortunately, testing seems to indicate that there is a 14 decimal digit maximum. I'm not sure why that would be. In any case, 12 digits should be fine. On further testing, I would suggest to check with AB international (QlikTech) Team.