Question about CDR Database

[sbray]

Under the CallDetailRecord table there are three Date Time fields; dateTimeOrigination, dateTimeConnect and dateTimeDisconnect. What format are the values in? How can you translate them into a date and time you can understand?

[aaronw.ca]

The values in those fields are stored as "number of seconds since Jan 1, 1970".

An excel formula like this:

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

(where DATE_TIME_VALUE is the date/time value from CallManager (such as 982407753)) works fine. Just format the cell as a date/time value.

Also, a query like this will show the values in a more readable format. Note that the date/times are stored as UTC, so you'd have to convert the values to your timezone. There might be a better way to do this in SQL, but this works for me :)

SELECT

dateTimeOrigination,

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

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

datetimeconnect,

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

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

datetimedisconnect,

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

[jorchard]

I use Perl to sort out my CDRs... I suggest you perform all your date calculations using EPOCH time (fancy name for number of seconds since Jan 1, 1970); however, to display this date/time value, the following function works in a pinch:

$readable = localtime($epoch);

I hope this helps...

[sbray]

Any idea how to do this in Access. I have it converted to the date and time right now but it's 5 hours too fast, same thing with that Excel formula you gave me. I'm thinking it has something to do with the time zone? Thanks for the help.

[jscheffler]

You need to adjust the time for your time zone. The epoch time you get is UTC.

[jorchard]

That is Correct

Using localtime() gives you the time using your system's time/timezone settings. For example, if you have your timezone configured for EST (UTC Offset), then localtime() will return your time in EST. Make sure your local machine has the timezone properly configured. I don't know of any way to do this in Access; however, if you find a way please post it here for all of us to see.

Thanks.

[aaronw.ca]

One option that you can optionally use, though not perfect, is to apply a modifier to the numeric value used in the formula. For Eastern Daylight Time, you're 4 hours behind UTC, which is 14400 seconds. For Eastern Standard Time, it's 5 hours, or 18000 seconds.

So applying this to the excel formula, we get:

=(DATE_TIME_VALUE-14400)/86400+DATE(70,1,1)

For the SQL query:

SELECT

dateTimeOrigination,

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

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

datetimeconnect,

CAST(CAST((datetimeconnect -14400) AS FLOAT) / CAST(86400 AS FLOAT) +

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

datetimedisconnect,

CAST(CAST((datetimedisconnect -14400) 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 in the sql query, we're now subtracting the value 14400 from the date/time value to arrive at our local time.

This has a drawback, of course. You must change the query twice per year to account for daylight savings time coming and going (ie for half the year you subtract 14400, for the other half of the year you subtract 18000). In addition, if you are applying the query to a set of data that spans the daylight savings time change, part of the data will have the wrong time displayed (ie in your case be off by 1 hour).

This is a limitation of the tool (in this case MS Access) rather than CallManager. I am not sure if MS Access has a function to convert a date/time value to and from UTC. SQL Server 2000 has a UTC function, but this function is not available in SQL Server 7. As others have suggested, there are various ways to handle the time difference, depending on the tools being employed.

I'm not sure if this is of any help.

[sbray]

This is what I did to solve my 5 hour difference in Access;

=(([dateTimeDisconnect])/(86400))+25569-(0.20833333333333)

the number that is subtracted is 5 (hours that I was off) divided by 24.

[dgoodwin]

There are many ways to do this. If you are going to write a Visual Basic app where you want to do this, one easy way is to do:

variable = DateAdd("s", valFromCDR, #1/1/1970#)

Where "s" means seconds. If you want to compensate for time zone, you can reference the Windows API function "GetTimeZoneInformation"