How To Convert Cisco CDR Date and Time

December 8th, 2017
Print Friendly, PDF & Email


Cisco CDR Date 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:

Cisco-CDR-Date-Time-Fields
 

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

Cisco CDR date and time fields

 

As mentioned in Cisco document, the date and time fields are based on UTC time zone and it is the number of seconds elapsed since January 1st, midnight, 1970; 00:00:00 1/1/1970.
This is called 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).

 

Human readable time Seconds
1 hour3600 seconds
1 day86400 seconds
1 week604800 seconds
1 month (30.44 days) 2629743 seconds
1 year (365.24 days) 31556926 seconds

 

So, in order 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 1 - Import your CDR file to sheet.

 

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

STEP 2 - Add one column on the right side of Datetime field.

As you can see in the image, the new column is labeled “F” and this 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 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

 

Cisco CDR Date Time Convert

 

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.

Cisco CDR Date Time

 

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

 

Cisco CDR

So, you now know the formula and can convert in 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 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

The second method uses the DateTime class in PHP 5+.

$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 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 human readable format.

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

Convert Cisco CDR Date Time in C:
You can use the following code in C for converting Cisco CDR date time to human readable format.
Example C routine using STRFTIME. STRFTIME converts information from a time structure to a string form, and writes the string into the memory area pointed to by “string”.

#include <stdio.h>
#include <time.h>

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 Linux shell for convert Cisco CDR date time to human readable format.


GeSHi Error: GeSHi could not find the language shell (using path /var/www/wordpress/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

Convert Cisco CDR Date Time in Microsoft Windows Power Shell:
You can use the following code in PowerShell for converting Cisco CDR date time to human readable format.

$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')

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

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 human readable format.

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

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

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 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)
}

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

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 human readable format.

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

Replace time.localtime with time.gmtime for GMT time. 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 human readable format.

Time.at(epoch)

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

Reza Mousavi

Reza Mousavi

Serial Entrepreneur, Founder & CEO at PBXDom, Software Architect, Frequent reader, Fan of adventure travel
Reza Mousavi
Print Friendly, PDF & Email

Share this article on social networks:


Related Posts



Are you ready to start monitoring and analysis your phone systems?

Try PBXDOM for 14 days free
no credit card required.


Try free for 14 days 

Follow us

Follow us and get the latest news through your favorite Social Network