CDR "dateTimeDisconnect" field conversion

walshda · ‎04-22-2003

Has anybody found a way to convert the "dateTimeDisconnect" field into a readable date? I have that field as the number of seconds since midnight Jan 1, 1970. I am using Access 2002 with an ODBC connection to the calldetailrecord table in sql on CCM.

Help?

aaronw.ca · ‎04-25-2003

You can use this formula in Excel to change a date of that format into a more readable date:

=C3/86400+DATE(70,1,1)

C3 is the cell containing the raw dateTimeOrigination, dateTimeConnect, or dateTimeDisconnect field values.

Alternatively, you can use a SQL statement to convert the date/time value to a more readable format. A query like this would work against the CallManager database:

select *,

CAST(CAST(dateTimeOrigination AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeOrigination_dt,

CAST(CAST(dateTimeConnect AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeConnect_dt,

CAST(CAST(dateTimeDisconnect AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeDisconnect_dt,

from calldetailrecord

order by datetimeorigination

Note that the dates/times in CallManager are stored in UTC timezone, so to change them to your timezone you would need to modify the query to reflect that (apply a modifier to the date/time fields in the calculation based on your timezone).

walshda · ‎04-26-2003

It is giving me this error: Syntax error (missing operator) in query expression 'dbo_CallDetailRecord.dateTimeOrigination CAST(CAT(dateTimeOrigination AS FLOAT / CAST(86400 AS FLOAT + DATEDIFF(day, 'Jan 1,1990', Jan 1,1970') AS datetime)'.

I am putting in this as my sql query:

SELECT dbo_CallDetailRecord.callingPartyNumber, dbo_CallDetailRecord.finalCalledPartyNumber, dbo_CallDetailRecord.duration, dbo_CallDetailRecord.dateTimeOrigination

CAST(CAST(dateTimeOrigination AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeOrigination_dt,

CAST(CAST(dateTimeConnect AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeConnect_dt,

CAST(CAST(dateTimeDisconnect AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeDisconnect_dt,

FROM dbo_CallDetailRecord

WHERE (((dbo_CallDetailRecord.callingPartyNumber)="5118" Or (dbo_CallDetailRecord.callingPartyNumber) Like "*6480206"));

Here is what it was:

SELECT dbo_CallDetailRecord.callingPartyNumber, dbo_CallDetailRecord.finalCalledPartyNumber, dbo_CallDetailRecord.duration, dbo_CallDetailRecord.dateTimeOrigination

FROM dbo_CallDetailRecord

WHERE (((dbo_CallDetailRecord.callingPartyNumber)="5118" Or (dbo_CallDetailRecord.callingPartyNumber) Like "*6480206"));

Any ideas?

christopher.l.lawson · ‎04-28-2003

Here's the functions I wrote for Access to convert UTC time to/from normal time. I also included the function to convert the IPs into dotted notation. They works pretty well for me. I know the timezone offset function is brute force but it works.

Public Function Normal_Time(UTC As Long) As Date

UTC = UTC + TimeZoneOffset(UTC) * 3600 ' Time Zone Correction

DaySec = UTC Mod 86400

HR = Int(DaySec / 3600)

MN = Int((DaySec - (HR * 3600)) / 60)

SC = Int(DaySec - (HR * 3600) - (MN * 60))

Normal_Time = FormatDateTime(DateSerial(1970, 1, 1 + ((UTC - DaySec) / 86400)) + TimeSerial(HR, MN, SC), vbGeneral)

End Function

Public Function TimeZoneOffset(UTC As Long) As Integer

'Assuming PST/PDT

'Daylight Saving Time begins 2 a.m. on the first Sunday of April.

'Time reverts to standard time at 2 a.m. on the last Sunday of October.

'2001 April 1 October 28 986119200 1004263200

'2002 April 7 October 27 1018173600 1035712800

'2003 April 6 October 26 1049623200 1067162400

'2004 April 4 October 31 1081072800 1099216800

'2005 April 3 October 30 1112522400 1130666400

'2006 April 2 October 29 1143972000 1162116000

'2007 April 1 October 28 1175421600 1193565600

'2008 April 6 October 26 1207476000 1225015200

'2009 April 5 October 25 1238925600 1256464800

'2010 April 4 October 31 1270375200 1288519200

If (986119200 <= UTC And UTC < 1004263200) Then

TimeZoneOffset = -7 '2001 PDT

ElseIf (UTC >= 1004263200 And UTC < 1018173600) Then

TimeZoneOffset = -8 '2001 PST

ElseIf (UTC >= 1018173600 And UTC < 1035712800) Then

TimeZoneOffset = -7 '2002 PDT

ElseIf (UTC >= 1035712800 And UTC < 1049623200) Then

TimeZoneOffset = -8 '2002 PST

ElseIf (UTC >= 1049623200 And UTC < 1067162400) Then

TimeZoneOffset = -7 '2003 PDT

ElseIf (UTC >= 1067162400 And UTC < 1081072800) Then

TimeZoneOffset = -8 '2003 PST

ElseIf (UTC >= 1081072800 And UTC < 1099216800) Then

TimeZoneOffset = -7 '2004 PDT

ElseIf (UTC >= 1099216800 And UTC < 1112522400) Then

TimeZoneOffset = -8 '2004 PST

ElseIf (UTC >= 1112522400 And UTC < 1130666400) Then

TimeZoneOffset = -7 '2005 PDT

ElseIf (UTC >= 1130666400 And UTC < 1143972000) Then

TimeZoneOffset = -8 '2005 PST

ElseIf (UTC >= 1143972000 And UTC < 1162116000) Then

TimeZoneOffset = -7 '2006 PDT

ElseIf (UTC >= 1162116000 And UTC < 1175421600) Then

TimeZoneOffset = -8 '2006 PST

ElseIf (UTC >= 1175421600 And UTC < 1193565600) Then

TimeZoneOffset = -7 '2007 PDT

ElseIf (UTC >= 1193565600 And UTC < 1207476000) Then

TimeZoneOffset = -8 '2007 PST

ElseIf (UTC >= 1207476000 And UTC < 1225015200) Then

TimeZoneOffset = -7 '2008 PDT

ElseIf (UTC >= 1225015200 And UTC < 1238925600) Then

TimeZoneOffset = -8 '2008 PST

ElseIf (UTC >= 1238925600 And UTC < 1256464800) Then

TimeZoneOffset = -7 '2009 PDT

ElseIf (UTC >= 1256464800 And UTC < 1270375200) Then

TimeZoneOffset = -8 '2009 PST

ElseIf (UTC >= 1270375200 And UTC < 1288519200) Then

TimeZoneOffset = -7 '2010 PDT

'Else

' Debug.Assert "Can't Determine TimeZone"

End If

End Function

Public Function UTC_Time(DT As Date) As Long

YR = Year(DT)

UTCStart = DateSerial(1970, 1, 1) + TimeSerial(0, 0, 0)

DYS = DT - UTCStart

UTC_Time = ((DT - UTCStart) * 86400)

UTC_Time = UTC_Time - (TimeZoneOffset(UTC_Time) * 3600) ' Time Zone Correction

End Function

Public Function IPConvert(IPDec As Long) As Variant

IPHex = Hex(IPDec)

If IPDec = 0 Then IPHex = "00000000"

If Len(IPHex) < 8 Then IPHex = "0" & IPHex

Octet1 = CDec("&H" & Right(IPHex, 2))

Octet2 = CDec("&H" & Mid(IPHex, 5, 2))

Octet3 = CDec("&H" & Mid(IPHex, 3, 2))

Octet4 = CDec("&H" & Left(IPHex, 2))

IPConvert = Octet1 & "." & Octet2 & "." & Octet3 & "." & Octet4

End

aaronw.ca · ‎04-28-2003

Sorry, my example included an additional comma that should not be there (before the FROM).

This should work much better:

select *,

CAST(CAST(dateTimeOrigination AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeOrigination_dt,

CAST(CAST(dateTimeConnect AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeConnect_dt,

CAST(CAST(dateTimeDisconnect AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeDisconnect_dt

from calldetailrecord

order by datetimeorigination

Incorporating your example:

SELECT dbo.CallDetailRecord.callingPartyNumber,

dbo.CallDetailRecord.finalCalledPartyNumber,

dbo.CallDetailRecord.duration,

CAST(CAST(dateTimeOrigination AS FLOAT) / CAST(86400 AS FLOAT) +

DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime) AS dateTimeOrigination_dt

FROM dbo.CallDetailRecord

WHERE ((dbo.CallDetailRecord.callingPartyNumber='5118')

OR (dbo.CallDetailRecord.callingPartyNumber Like '*6480206'));

I changed the double-quotes to single quotes and changed the parenthesis.

Hope that helps!

walshda · ‎04-29-2003

It (your example that you so nicely gave to me with my info) runs fine in query analyzer, but bombs out in access, and this is on the same computer. I can actually get the results in the analyzer.

Access tells me:

Syntax error (missing operator) in query expression 'CAST(CAST(dateTimeOrigination AS FLOAT) / CAST(86400 AS FLOAT) + DATEDIFF(day, 'Jan 1,1900', 'Jan 1,1970') AS datetime)'.

I have the ODBC connection changing the master to CDR and then using the Cisco login and password. Other than that I am using the default settings for an ODBC connection in winXP.

I really appreciate all of you for helping a novice with sql queries such as me.

aaronw.ca · ‎04-30-2003

Yes, MS Access thinks a bit differently than SQL Server!

Try this query in MS Access, I'm no Access guru, but it should work!

SELECT dbo.CallDetailRecord.callingPartyNumber,

dbo.CallDetailRecord.finalCalledPartyNumber,

dbo.CallDetailRecord.duration,

CVDate(Format( ((dbo.dateTimeOrigination/86400) + DateDiff("d", #Jan 1,1900#,#Jan 1,1970#)) ,"dd mm yyyy hh:mm:ss")) AS dateTimeOrigination_dt

FROM dbo.CallDetailRecord

WHERE ((dbo.CallDetailRecord.callingPartyNumber='5118')

OR (dbo.CallDetailRecord.callingPartyNumber Like '*6480206'));

Check the date/time values returned to ensure that they are accurate (and that my conversion to Access is correct), though remember that this query does not take timezone into consideration so the values will be reported in UTC 'timezone'.