cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
26900
Views
29
Helpful
5
Replies
Beginner

CDR "dateTimeOrigination" conversion formula in Excel?

Can someone point me to some documentation or an answer for converting the "dateTimeOrigination" entry in the CDR database into a real Date/Time format. The timestamps are in UTC format and we would like to make the conversion within an Excel spreadsheet.

Thanks

5 REPLIES
Beginner

Re: CDR "dateTimeOrigination" conversion formula in Excel?

from

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a00801d71c5.shtml

Understanding the Date Format in the CDR Database

To select all calls after a certain date, you need to convert the date you want into a value in universal time and in seconds since January 1st, 1970.

For example, 973995954 translates to 11/12/00 2:25 AM. Use the following procedure to decipher the time stamp.

Go to Microsoft Excel.

In cell A1 type the number that is found in the last record for dateTimeOrigination.

In cell A2, paste the formula =A1/86400+DATE(1970,1,1).

Right-click on cell A2 and select format cells.

Under the Number tab select Time where the format is 3/14/98 130 PM.

The result is the actual time in readable format.

Use the following procedure to convert the day you want into the date.

Enter the date in cell B2 in the format MM/DD/YYYY 00:00 AM.

In Cell A enter the formula =(B2-DATE(1970,1,1))*86400.

Right-click on cell B1 and select format cells.

Under the Number tab, select Number.

This number is the universal time in seconds that is used in the formula.

Beginner

Re: CDR "dateTimeOrigination" conversion formula in Excel?

These fomulas don't work.

Here is an example-

My perl script output-

C:\perlprogs>perl tconvert.pl 1074732166

Wed Jan 21 19:42:46 2004

The excel formula result for 1074732166-

1/22/04 12:42 AM

Date and time from a CDR search matches the perl script.

Here is the perl script I use-

#! c:\perl\bin\perl

# convert single integer on command line (assumed to be standard

# unix "seconds since 1/1/70" time) to local date.

require "ctime.pl";

if ($#ARGV >= 0) {

$time = shift(@ARGV);

}

else {

die "Usage: num2date number\n";

}

print &ctime($time);

Beginner

CDR "dateTimeOrigination" conversion formula in Excel?

In ISO format:

use POSIX qw( strftime );

use File::stat;

$fecha = strftime('%Y-%m-%d %H:%M:%S', localtime( $PRE_fecha ) );

Beginner

Re: CDR "dateTimeOrigination" conversion formula in Excel?

Thanks a lot, this was very helpful for me.
Highlighted
Beginner

Example Epoch Time:

Example Epoch Time: 1406610231

Description:

Microsoft Excel =(A1 / 86400) + 25569 Format the result cell for date/time, the result will be in GMT time (A1 is the cell with the epoch number). For other time zones: =((A1 +/- time zone adjustment) / 86400) + 25569.


Formula:
 
=(A1/86400)+25569
 

It gives you GMT time. If you would like to convert MST (in our case) time please use the below formula in excel.

=A2-TIME(7,0,0)

CreatePlease to create content
Ask the Expert- Firepower configuration & troubleshooting