Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

understanding the datetimeconnect # in the SQL CDR's

I am currently trying to export and make sence of the data in the SQL database when i look at the datetimeconnect field i recieve a 10 digit # is their some way of deciphering this #. This would really be helpful . Please let me know ..

jim koniecki

4 REPLIES
New Member

Re: understanding the datetimeconnect # in the SQL CDR's

If I recall right it is number of seconds since Jan 1, 1980. Another developer might be able to provide more details, etc.

If they were using Win32 time values;

The Win32 time format is used by the Win32 APIs. The date and time is stored in a 64-bit value that represents the number of 100 nanoseconds since January 1st, 1601. This date was chosen because it is 400 years prior to the start of a new century. At 100ns resolution 32 bits is good for about 429 seconds (or 7 minutes) and a 63-bit integer is good for about 29,247 years.

Cisco Employee

Re: understanding the datetimeconnect # in the SQL CDR's

Actually, it's the number of seconds since Midnight UTC time on January 1, 1970 (a.k.a. UNIX Epoch Time).

Anyway, if you just have one number you want to translate to a readable time, then use Excel.

Put the number in A1 and then in B1 type:

=(A1/86400)+DATE(1970,1,1)

And then format the B1 cell to a date/time format that you can read. Keep in mind that the result will be in UTC (Greenwich) time zone format, so you can modify the formula to fix that if you like.

If you really want to make it easy on yourself then you can install ART.

New Member

Re: understanding the datetimeconnect # in the SQL CDR's

david.

thank you so much

life saver

jim k

Cisco Employee

Re: understanding the datetimeconnect # in the SQL CDR's

In case anyone cares, if you want the Excel formula to automatically adjust the time displayed to account for Eastern Time (-0500) then you can use this formula:

=((A1-18000)/86400)+DATE(1970,1,1)

18000 = number of seconds in 5 hours

86400 = number of seconds in 1 day

1310
Views
0
Helpful
4
Replies