<html>
<head>
<base href="https://wiki.asterisk.org/wiki">
<link rel="stylesheet" href="/wiki/s/2042/1/7/_/styles/combined.css?spaceKey=AST&forWysiwyg=true" type="text/css">
</head>
<body style="background: white;" bgcolor="white" class="email-body">
<div id="pageContent">
<div id="notificationFormat">
<div class="wiki-content">
<div class="email">
<h2><a href="https://wiki.asterisk.org/wiki/display/AST/MySQL+CDR+Backend?focusedCommentId=13076842#comment-13076842">MySQL CDR Backend</a></h2>
<h4>Comment edited by <a href="https://wiki.asterisk.org/wiki/display/~kwk">Konrad Kleine</a>
:</h4>
<br/>
<h4>Changes (3)</h4>
<div id="page-diffs">
<table class="diff" cellpadding="0" cellspacing="0">
<tr><td class="diff-snipped" >...<br></td></tr>
<tr><td class="diff-unchanged" >If you want to create a view to avoid this calculation by hand you can do so with this command: <br>{code:sql} <br></td></tr>
<tr><td class="diff-changed-lines" >CREATE VIEW <span class="diff-changed-words"><span class="diff-added-chars"style="background-color: #dfd;">YOUR_DATABASE.</span>cdr_full</span> AS <br></td></tr>
<tr><td class="diff-unchanged" >SELECT <br> *, <br></td></tr>
<tr><td class="diff-snipped" >...<br></td></tr>
<tr><td class="diff-unchanged" >There is one problem with the column types though: *answer* and *end* in my case are of type *double(23,6)* which is not what we want. To fix this we need to do some casting (Don't know if this is MySQL specific): <br>{code:sql} <br></td></tr>
<tr><td class="diff-changed-lines" >CREATE VIEW <span class="diff-changed-words"><span class="diff-added-chars"style="background-color: #dfd;">YOUR_DATABASE.</span>cdr_full</span> AS <br></td></tr>
<tr><td class="diff-unchanged" >SELECT <br> *, <br></td></tr>
<tr><td class="diff-snipped" >...<br></td></tr>
<tr><td class="diff-unchanged" >If you want to be perfectly correct, the colum attributes like *NOT NULL* and default values need to be set correctly. Well, since all the other columns carry the *NOT NULL* definition it should suffice if you just do the calculation. Anyway should by whatever circumstance the result of a calculation be *NULL* you might want to default to *'0000-00-00 00:00:00'*. Here's how you can achieve this: <br>{code:sql} <br></td></tr>
<tr><td class="diff-changed-lines" >CREATE VIEW <span class="diff-changed-words"><span class="diff-added-chars"style="background-color: #dfd;">YOUR_DATABASE.</span>cdr_full</span> AS <br></td></tr>
<tr><td class="diff-unchanged" >SELECT <br> *, <br></td></tr>
<tr><td class="diff-snipped" >...<br></td></tr>
</table>
</div> <h4>Full Content</h4>
<div class="notificationGreySide">
<p>The <b>start</b> is already there, but it's called <b>calldate</b> in MySQL for instance.</p>
<p>If you do a</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<script type="syntaxhighlighter" class="toolbar: false; theme: Confluence; brush: bash; gutter: false"><![CDATA[
grep "^[^;]" /etc/asterisk/cdr_mysql.conf
]]></script>
</div></div>
<p>on an Asterisk 1.8.4 installation amongst other things it reveals:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<script type="syntaxhighlighter" class="toolbar: false; theme: Confluence; brush: bash; gutter: false"><![CDATA[
[columns]
alias start => calldate
alias callerid => clid
]]></script>
</div></div>
<p>So what's Asterisk internally called <b>start</b> is called <b>calldate</b> in MySQL. As you can see from the <b>.conf</b> excerpt, the same goes for <b>callerid</b> and <b>clid</b>.</p>
<p>If I'm not totally mistaken, <b>end</b> and <b>answer</b> can be derived from <b>duration</b> and <b>billsec</b>. In the original description of the <a href="/wiki/display/AST/CDR+Fields" title="CDR Fields">CDR Fields</a> it says this:</p>
<blockquote>
<ul>
<li>start: Start of call (date/time)</li>
<li>answer: Answer of call (date/time)</li>
<li>end: End of call (date/time)</li>
<li>duration: Total time in system, in seconds (integer), from dial to hangup</li>
<li>billsec: Total time call is up, in seconds (integer), from answer to hangup</li>
</ul>
</blockquote>
<p>In MySQL you only have <b>duration</b>, <b>billsec</b>, and <b>start</b> which is actually called <b>calldate</b>.</p>
<p>Thus, this should be what you're looking for:</p>
<p><b>start</b> = calldate<br/>
<b>answer</b> = start + (duration - billsec)<br/>
<b>end</b> = start + duration</p>
<p>In SQL this would look like this:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<script type="syntaxhighlighter" class="toolbar: false; theme: Confluence; brush: sql; gutter: false"><![CDATA[
SELECT
*,
(`calldate`+(`duration`-`billsec`)) AS `answer`,
(`calldate`+`duration`) AS `end`
FROM
cdr;
]]></script>
</div></div>
<p>If you want to create a view to avoid this calculation by hand you can do so with this command:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<script type="syntaxhighlighter" class="toolbar: false; theme: Confluence; brush: sql; gutter: false"><![CDATA[
CREATE VIEW YOUR_DATABASE.cdr_full AS
SELECT
*,
(`calldate`+(`duration`-`billsec`)) AS `answer`,
(`calldate`+`duration`) AS `end`
FROM
cdr;
]]></script>
</div></div>
<p>There is one problem with the column types though: <b>answer</b> and <b>end</b> in my case are of type <b>double(23,6)</b> which is not what we want. To fix this we need to do some casting (Don't know if this is MySQL specific):</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<script type="syntaxhighlighter" class="toolbar: false; theme: Confluence; brush: sql; gutter: false"><![CDATA[
CREATE VIEW YOUR_DATABASE.cdr_full AS
SELECT
*,
CAST( `calldate`+(`duration`-`billsec`) AS DATETIME ) AS `answer`,
CAST( `calldate`+`duration` AS DATETIME ) AS `end`
FROM
`cdr`;
]]></script>
</div></div>
<p>If you want to be perfectly correct, the colum attributes like <b>NOT NULL</b> and default values need to be set correctly. Well, since all the other columns carry the <b>NOT NULL</b> definition it should suffice if you just do the calculation. Anyway should by whatever circumstance the result of a calculation be <b>NULL</b> you might want to default to <b>'0000-00-00 00:00:00'</b>. Here's how you can achieve this:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<script type="syntaxhighlighter" class="toolbar: false; theme: Confluence; brush: sql; gutter: false"><![CDATA[
CREATE VIEW YOUR_DATABASE.cdr_full AS
SELECT
*,
CAST( COALESCE(`calldate`+(`duration`-`billsec`), '0000-00-00 00:00:00') AS DATETIME ) AS `answer`,
CAST( COALESCE(`calldate`+`duration`,'0000-00-00 00:00:00') AS DATETIME ) AS `end`
FROM
`cdr`;
]]></script>
</div></div>
</div>
<div id="commentsSection" class="wiki-content pageSection">
<div style="float: right;">
<a href="https://wiki.asterisk.org/wiki/users/viewnotifications.action" class="grey">Change Notification Preferences</a>
</div>
<a href="https://wiki.asterisk.org/wiki/display/AST/MySQL+CDR+Backend?focusedCommentId=13076842#comment-13076842">View Online</a>
|
<a id="reply-13076842" href="https://wiki.asterisk.org/wiki/display/AST/MySQL+CDR+Backend?replyToComment=13076842#comment-13076842">Reply To This</a>
</div>
</div>
</div>
</div>
</div>
</body>
</html>