×

Warning message

  • Cisco Support Forums is in Read Only mode while the site is being migrated.
  • Cisco Support Forums is in Read Only mode while the site is being migrated.

How to parse date/time recorded by CDR

Unanswered Question
Nov 14th, 2001
User Badges:

Hi,


I want to make my own reporting tool instead of using Cisco ART. But my problem is, I dont know how to parse or to convert the date/time format on the CDR. Is there anyone who can help with this problem?


Thanks

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
Tom Dillon Wed, 11/14/2001 - 19:39
User Badges:

Here's the info from Cisco document:

http://www.cisco.com/warp/public/788/AVVID/ts_ccm_301_sec7.htm


Time Values

All time values are represented as unsigned 32 bit integers. This unsigned integer value is displayed from the database as a signed integer.


This field is a time_t value that is obtained from the Windows NT (2000) system routines. The value is a coordinated universal time (UTC) value and represents the number of seconds since Midnight (00:00:00) Jan. 1, 1970.


Deciphering the Time Stamp

Using Microsoft Excel, you can write a formula to make converting this time stamp a little easier. If the value is in cell A1, you can make another cell:


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


There are 86400 seconds in a day.


Then, format the resulting cell as a date/time field in Excel.


The date time format in the CDR within the database table is a number that equals the number of seconds that have passed since 1/1/1970 12:00:00 am. You can convert from the value they have in the table to a useable date time value by using the DateAdd function in T-SQL.


I've been doing some of the same thing you are doing. In my case, I wanted the date times in local time (which is GMT - 7 hours). My function to convert this looks like this:

dateadd(hh,-7, dateadd(s, datetimeorigination, '1/1/1970 00:00:00.000'))


Notice that there are two dateadds in there. The first converts from Cisco's stored value to a human readable GMT date/time value. The outer one converts it to MDST.


Hope this helps.

Actions

This Discussion