New subject: Changing acc_cdrs time and duration columns from varchar to int/float

21 Jul 2015

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;

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