How To Convert Cisco CDR Date and Time

The call detail records (CDR) in Cisco Unified Communications Manager (Cisco Call Manager) have many fields for date and time. The date and time fields in CDR files are as follows:

To better understand each field, take a look at the following image:

As mentioned in the Cisco document, the date and time fields are based on the UTC zone, and it is the number of seconds elapsed since January 1st, midnight, 1970; 00:00:00 1/1/1970.
This is called the Unix epoch.

What is epoch time?

The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking, the epoch is Unix time 0 (midnight 1/1/1970), but ‘epoch’ is often used as a synonym for ‘Unix time.’ Many Unix systems store epoch dates as a signed 32-bit integer, which might cause problems on January 19, 2038 (known as the Year 2038 problem or Y2038).

So, to convert date and time fields to the human-readable format, a few tweaks are required.

If you are looking for Cisco CDR date and time conversion, you have come to the right place.

In this article, you can find many ways to convert Cisco CDR date and time fields to a human-readable format or date object to be used in another software.

Convert Cisco CDR Date Time in Microsoft Excel/Google Sheet:

Step 1 – Import your CDR file to a sheet.

STEP 2 – Add one column on the right side of the DateTime field.

As you can see in the image, the new column is labeled “F,” which is the column where we should enter the formula to convert CDR date and time.

STEP 3 – Click on the first cell and enter the following formula:

=(((E3+(8*3600))/86400)+25569)

Where;

E3 => is our first cell of the original CDR Date time

8 => Time Offset I entered 8 to convert to PT (Pacific Time)

3600 => seconds in each hour

86400 => seconds in each day

25569 => because spreadsheet counts epoch from 1/1/1900, and most others start at 1/1/1970.

And then press the ENTER key. Cell value should be changed to something like this:

42488.12406

STEP 4 – Now, you should select the bottom right column and drag it to cover the whole of column F. After you have applied the formula for all rows in column F, you should select the column F and change cell format to DateTime format.

STEP 5 – You should now be able to see the human-readable date and time for each row.

So, you now know the formula and can convert it to any other software programming language, but wait, I have more stuff for you.

Convert Cisco CDR DateTime in Javascript:

There are times when you need to convert CDR date time format in Javascript web app or application; and for that, I wrote the following function:

function convertCiscoCDRDateTimetoUTC(CDRDateTime)
{
var dateTimeInput = new Date(CDRDateTime*1000);
return dateTimeInput.toISOString();
}

It’s really that simple! We create a new Date object and return the value to ISO date-time format.

You can use that function like this:

Var HumanReadableDateTime=convertCiscoCDRDateTimetoUTC(1462375534);
console.log(HumanReadableDateTime);

The output is generated in the following format:

2016-05-04T15:25:34.000Z

Convert Cisco CDR Date Time in PHP:
We can convert CDR date time in two methods in PHP script; the first method uses the date function:

$epoch = 1483228800;
echo date('r', $epoch); //output as RFC 2822 date - returns local time
echo gmdate('r', $epoch); // returns GMT/UTC time: Sun, 01 Jan 2017 00:00:00 +0000

$epoch = 1483228800;
$dt = new DateTime("@$epoch"); // convert UNIX timestamp to PHP DateTime
echo $dt->format('Y-m-d H:i:s'); // output = 2017-01-01 00:00:00

Convert Cisco CDR Date Time in Perl:
You can use the following code in Perl for converting Cisco CDR date time to a human-readable format.

use DateTime;
$epoch=1483228800;
$dt = DateTime->from_epoch( epoch => $epoch );

$year = $dt->year;
$month = $dt->month; # 1-12 - you can also use '$dt->mon'
$day = $dt->day; # 1-31 - also 'day_of_month', 'mday'
$dow = $dt->day_of_week; # 1-7 (Monday is 1) - also 'dow', 'wday'
$hour = $dt->hour; # 0-23
$minute = $dt->minute; # 0-59 - also 'min'
$second = $dt->second; # 0-61 (leap seconds!) - also 'sec'
$doy = $dt->day_of_year; # 1-366 (leap years) - also 'doy'
$doq = $dt->day_of_quarter; # 1.. - also 'doq'
$qtr = $dt->quarter; # 1-4
$ymd = $dt->ymd; # 1974-11-30
$ymd = $dt->ymd('/'); # 1974/11/30 - also 'date'
$hms = $dt->hms; # 13:30:00
$hms = $dt->hms('|'); # 13|30|00 - also 'time'

Convert Cisco CDR Date Time in Delphi:
You can use the following code in Borland Delphi for converting Cisco CDR date time to a human-readable format.

myString := DateTimeToStr(UnixToDateTime(Epoch)); Where Epoch is a signed integer.

#include
#include

int main(void)
{
time_t now;
struct tm ts;
char buf[80];

// Get current time
time(&now);

// Format time, "ddd yyyy-mm-dd hh:mm:ss zzz"
ts = *localtime(&now);
strftime(buf, sizeof(buf), "%a %Y-%m-%d %H:%M:%S %Z", &ts);
printf("%s\n", buf);
return 0;
}

Convert Cisco CDR Date Time in MySQL:
In MySQL, you should use the FROM_UNIXTIME function in your SQL query.

FROM_UNIXTIME(epoch, optional output format) Default output format is YYY-MM-DD HH:MM:SS

Convert Cisco CDR Date Time in Linux Shell:
You can use the following command in the Linux shell to convert Cisco CDR date time to a human-readable format.

date -ud @1483228800
Change 1483228800 with your original CDR date-time value.

$CDRTime= 1483228800
$origin = New-Object -Type DateTime -ArgumentList 1970, 1, 1, 0, 0, 0, 0 $whatIWant = $origin.AddSeconds($CDRTime)

Convert Cisco CDR Date Time in Microsoft SQL Server:
In SQL Server, you should use the DATEADD function in your SQL query.

DATEADD(s, epoch, '1970-01-01 00:00:00')

SELECT datetime(epoch_to_convert, 'unixepoch');

or local timezone:

SELECT datetime(epoch_to_convert, 'unixepoch', 'localtime');

Convert Cisco CDR Date Time in Java:
You can use the following code in Java for converting Cisco CDR date time to a human-readable format.

String date = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000));

function epoch2date(myEpoch)
epoch2date = DateAdd("s", myEpoch, "01/01/1970 00:00:00")
end function

Convert Cisco CDR Date Time in Go:
You can use the following code in Go for converting Cisco CDR date time to a human-readable format.

package main

import(
"fmt"
"time"
)

func main(){
// Current epoch time
fmt.Printf("Current epoch time is:\t\t\t%d\n\n", currentEpochTime())

// Convert from human readable date to epoch
humanReadable := time.Now()
fmt.Printf("Human readable time is:\t\t\t%s\n", humanReadable)
fmt.Printf("Human readable to epoch time is:\t%d\n\n", humanReadableToEpoch(humanReadable))

// Convert from epoch to human readable date
epoch := currentEpochTime()
fmt.Printf("Epoch to human readable time is:\t%s\n", epochToHumanReadable(epoch))

}

func currentEpochTime() int64 {
return time.Now().Unix()
}

func humanReadableToEpoch(date time.Time) int64 {
return date.Unix()
}

func epochToHumanReadable(epoch int64) time.Time {
return time.Unix(epoch, 0)
}

private string epoch2string(int epoch) {
return new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddSeconds(epoch).ToShortDateString(); }

Convert Cisco CDR Date Time in Python:
You can use the following code in Python for converting Cisco CDR date time to a human-readable format.

import time; time.strftime("%a, %d %b %Y %H:%M:%S +0000", time.localtime(epoch))

Replace time. Local time with time. gmtime for GMT. Or using DateTime:

import datetime; datetime.datetime.utcfromtimestamp(epoch).replace(tzinfo=datetime.timezone.utc)

Convert Cisco CDR Date Time in Ruby:
You can use the following code in Ruby for converting Cisco CDR date time to a human-readable format.

Time.at(epoch)

Finally, if you want to convert just one or two CDR dates, try Cisco CDR Date Time Conversion Online Tools.