Solved: SQL Query for CDR

mlong0000 · ‎11-19-2007

Hi

Im trying to run a query through Query Analyser on CallManager 4.1(3)

The query runs fine except for the date and time (which for me is the important part). It seems that the date/time is being returned to me in some form or seconds since 1970 (I believe this is from UNIX).

All I am running is a

select * from CallDetailRecord

Does anyone know code to add in that will convert this date/time number in my result?

Thanks

Michael

Rob Huffman · ‎11-19-2007

Hi Michael,

I beleive this is referred to as "Universal Time". Here is some related info;

Date Format in the CDR Database

In order 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. Complete these steps in order 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.

From this excellent doc;

Using SQL Queries to Search the Call Detail Record with Cisco CallManager

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

Hope this helps!

Rob

PS: Ithink these tools work as well :)

http://www.iwebtool.com/unix_time_converter

View solution in original post

Rob Huffman · ‎11-19-2007

Hi Michael,

I beleive this is referred to as "Universal Time". Here is some related info;

Date Format in the CDR Database

In order 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. Complete these steps in order 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.

From this excellent doc;

Using SQL Queries to Search the Call Detail Record with Cisco CallManager

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

Hope this helps!

Rob

PS: Ithink these tools work as well :)

http://www.iwebtool.com/unix_time_converter

Erick Bergquist · ‎11-19-2007

SQL Query like this does the conversion in SQL, GMT times.

use cdr

select DATEADD(ss, datetimeorigination, 'Jan 1, 1970 00:00:00')

AS "datetime GMT", callingpartynumber, finalcalledpartynumber,

originalcalledpartynumber, duration, datetimeorigination

from calldetailrecord

You can adjust for timezone, with a select entry including adjustment below. 21600 is # of seconds for 6 hour offset for CST.

DATEADD(ss, datetimeorigination-21600, 'Jan 1,

1970 00:00:00')

AS "datetime GMT-6"

mlong0000 · ‎11-20-2007

Thanks for the replies on this