TM1 Forum

Posted: **Thu Oct 15, 2009 12:24 am**

Fellow forum members:

One of my "backburner" items on my to do list (i.e. when I have spare time) is to mine the data from our Cisco Call Manager. I'll get right to the point:

The DateTimeConnection and DateTimeDisconnection values are in what my IT guy told me was "C Construct Time", the number of seconds since 1/1/1970. I was able to figure how to convert the value in Excel using the following formula:

= DATE(1970,1,1) + (DateTimeDisconnection/24/60/60) - TIME(7,0,0)

Below is what I have tried for the TI formula:

: DATE.JPG (25.2 KiB) Viewed 2578 times

You can see the result. After looking at the DATE() function help file, it looks like the function only returns the date; it does not include the time of day. Ideally, I'd like the data to be in MM/DD/YYYY HH:MM format. Has anyone tried anything in a similar manner, or have any suggestions?

I'd appreciate any feedback or suggestions you may help me with. Thank you for your time.

Posted: **Thu Oct 15, 2009 1:00 am**

shinymcshires wrote: The DateTimeConnection and DateTimeDisconnection values are in what my IT guy told me was "C Construct Time", the number of seconds since 1/1/1970.

Wow. That takes me back. It's been years since I've even thought about that one.

shinymcshires wrote: After looking at the DATE() function help file, it looks like the function only returns the date; it does not include the time of day. Ideally, I'd like the data to be in MM/DD/YYYY HH:MM format. Has anyone tried anything in a similar manner, or have any suggestions?

I'm not 100% clear on what value you're trying to return but the TIMST function would seem to be the solution to your problem. For example:

Code: Select all

# Equivalent to the C timestamp, using local time. (The number of seconds since 1/1/70)
l = 1255607635.56;

# 3653 is the TI serial date of 1/1/70
s = TIMST( 3653 + ( l / 24 / 60 / 60 ) , '\m-\d-\Y \h:\i');

AsciiOutput ('Y:\Temp\s.txt', s );

This yields the output:

Code: Select all

"10-15-2009 11:53"

Which is what the local time was here when I wrote that code.

Posted: **Thu Oct 15, 2009 3:38 pm**

Alan:

Thank you for the quick reply. That worked like a charm! Thank you for the great suggestion!

: TIMST.JPG (26.11 KiB) Viewed 2548 times

TM1 Forum

Time Conversion from "C Time Construct"

Time Conversion from "C Time Construct"

Re: Time Conversion from "C Time Construct"

Re: Time Conversion from "C Time Construct"