Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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

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

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

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