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

How to parse date/time recorded by CDR


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?


Community Member

Re: How to parse date/time recorded by CDR

Here's the info from Cisco document:

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:


There are 86400 seconds in a day.

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

Community Member

Re: How to parse date/time recorded by CDR

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.

CreatePlease to create content