Time Conversion from "C Time Construct"

[shinymcshires]

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 2574 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.

[Alan Kirk]

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.

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.

[shinymcshires]

Alan:

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

TIMST.JPG (26.11 KiB) Viewed 2544 times