Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
waszcma1
Creator
Creator

Unix Timestamp issue

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?

3 Replies
Peter_Cammaert
Partner
Partner

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

jonathandienst
Partner
Partner

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Peter_Cammaert
Partner
Partner

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