topic Re: Unix Timestamp issue in QlikView

Unix Timestamp issue

waszcma1 — Mon, 08 Jun 2015 09:22:53 GMT

Hi,

QV is connected with ServiceNow (MySql database) via ODBC connector

There is some issue with timestamp conversion from ServiceNow into QV.

Example.

Row data fetched from My_sql into QVD file.

Field: sla_duration

25569.25

...

etc.

This is the unix format so all is mesuered from 01/01/1970 00:00:00

25569.25 ===> [DD:hh:mm] 00:06:00

But in ServiceNow this value is displayed as 4 hours not 6 hours

I use workaround for that

sla_duration - ('01/01/1970 00:00:00' -2/24) than I have 0.16666666666667 which I can easly convert into [DD:hh:mm] format with result 00:04:00 ===> 4 hours

But why I have exactly 2 hours difference between row data fetched into QVD and proper value displayed in SNC

SNC Server and QV server are exactly in the same time zone.

Any Ideas?

Re: Unix Timestamp issue

Peter_Cammaert — Mon, 08 Jun 2015 09:28:41 GMT

IIRC Unix timestamps are measured in seconds since epoch. QlikView uses Excel-timestamps, meaning that the fraction is the time value (0.25 = 1/4 of a day = 6 hours) and the integer part is the number of days since ...(I don't remember)

You may have to divide the Unix timestamp by 86400 to get to the Excel-timestamp.

Best,

Peter

Re: Unix Timestamp issue

jonathandienst — Mon, 08 Jun 2015 09:33:25 GMT

Probably your timestamp is based on GMT. You will need to add a time offset to get local time (+2h in your case?)

Re: Unix Timestamp issue

Peter_Cammaert — Mon, 08 Jun 2015 09:35:57 GMT

Now that I think of it, Unix timestamps may also be specified in UTC time, depending on where they come from. The fact that both servers are located in the same timezone doesn't matter, they may still have a different base clock that is converted to local time in a different way. Most Windows servers have a base clock in local time, while Unix machines have a base clock in UTC.

Just a guess, my unix knowledge isn't as fresh as it used to be...

Peter