Time Stamp Analysis

[#1]

Hi,

Anybody knows how to read the timeStamp.

I have the attached document. It loads phone log files from a directory.

I am trying to have the date, time shown on a table next to the Original Party and Destination party.

I tried the Time Stamp option on a list box just for testing..It doesn't seem to be correct..

Any Advice?

Thxs,

Badr

[#2]

BTW, the date should be today's date and the time is between 12 and 2.00 PM

[swuehl]

Hm, that's pretty hard since you don't give any information about the source system you get your data from. The source data will define how the integer number called TimeStamp needs to be interpreted.

I just make a guess that you are using a format that is compatible to a Cisco cdr (number of seconds since Jan 1st 1970).

Try this as additional expression in your TimeStamp listbox (expression tab in list box properties):

=timestamp(TimeStamp/86400+makedate(1970,1,1))

This gives slightly different timestamps than you posted in your second post, but close.

But maybe my assumptions are wrong, so please double check the source format definition.

If the transformation is ok, you could the transformation in the script like

LOAD

...

timestamp(TimeStamp/86400+makedate(1970,1,1)) as TimeStamp

...

from ...;

Another idea is to get rid of the format strings that appear on some lines (like 'INTEGER'), maybe by using a where clause :

LOAD

...

from Table.xls where isnumber(TimeStamp);

Hope this helps,

Stefan

[#4]

This is great but the time that showing there is wrong like it shows the time +5 hours, is there anyway to fix it?

I am loading from Cisco log files.

the second thing is the were statement is not working but it will be great if I can get rid of them.

Thank you so much!

[swuehl · Accepted solution]

Is -5 hours maybe your TimeZone offset?

You could probably correct the timestamp like this

=timestamp(TimeStamp/86400+makedate(1970,1,1)+interval#(-5,'h'))

(please check if the sign of the first argument to interval# function is the way you need it).

Regarding your other question, I misspelled the function name, try

LOAD

...

from Table.xls where isnum(TimeStamp);

[#6]

Thank you so much,

You helped me so many times..

How did you get all this experience..Great Job!

[swuehl]

You're welcome.

One more thought. If it's really a time zone issue that we need to correct for the -5 hours, it's probably better to use converttolocaltime function, that will also consider DST.

=converttolocaltime(timestamp(TimeStamp/86400+makedate(1970,1,1)),'Indiana (Ost)')

Instead of 'Indiana (Ost)', use your place string as defined in your OS for your time zone, or you could also use 'UTC-05:00'

Regards,

Stefan