[SR-Users] Changing acc_cdrs time and duration columns from varchar to int/float

[mayamatakeshi]

On Tue, Jul 21, 2015 at 6:35 AM, mayamatakeshi <mayamatakeshi at gmail.com>
wrote:

> Hello,
> I am planning to enable acc cdr in my kamailio 4.1 server.
> I am testing in my lab and it is working fine.
> However, when I check the table definition I see this:
>
> mysql> describe acc_cdrs;
> +------------+------------------+------+-----+---------+----------------+
> | Field      | Type             | Null | Key | Default | Extra          |
> +------------+------------------+------+-----+---------+----------------+
> | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
> | start_time | varchar(32)      | NO   | MUL |         |                |
> | end_time   | varchar(32)      | NO   |     |         |                |
> | duration   | varchar(32)      | NO   |     |         |                |
> +------------+------------------+------+-----+---------+----------------+
> 4 rows in set (0.00 sec)
>
>
> The *_time and duration columns are defined as varchar.
> Because of this, i need to perform a cast when doing queries:
>   select * from acc_cdrs where cast(start_time as unsigned) >= 1437120622;
> which would cause extra processing in the db server.
>
> So I experimented changing these columns to "int unsigned" and to "float
> unsigned" and it worked for both without problems (records properly added
> to acc_cdrs and no errors in kamailio.log):
>
> alter table acc_cdrs modify start_time int unsigned;
> alter table acc_cdrs modify end_time int unsigned;
> alter table acc_cdrs modify duration int unsigned;
>
> alter table acc_cdrs modify start_time float unsigned;
> alter table acc_cdrs modify end_time float unsigned;
> alter table acc_cdrs modify duration float unsigned;
>

Actually, checking again, they should not be a "float unsigned" but "double
unsigned":

alter table acc_cdrs modify start_time double unsigned;
alter table acc_cdrs modify end_time double unsigned;
alter table acc_cdrs modify duration double unsigned;



>
> So I am thinking in doing this in my production server but I would like to
> know if there is any potential risk of doing this (as I am not sure why it
> was decided for these fields to be varchar instead of a numeric type).
>
> Regards,
> Takeshi
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20150721/2cd4adeb/attachment.html>