[solved] How do you insert microtime into a database

[eluser]gtech[/eluser]
I am using MYSQL, and I am trying to insert microtime(true) into a database.. microtime(true) returns a float, so I set the database field type to FLOAT(13,2) and then tried to insert it into the database

Code:

...
        $updatedata = array('LastResult' => (float)microtime(true));
        $checkdata  = array('ResultSetID' => $resultsetid);
...
        $this->db->where($checkdata);
        $res = $this->db->update('resultsets', $updatedata);

when micro time is inserted into the database the values get rounded up

1197307524.75

becomes

1197307520.00 when I select it from the database.

has anyone tried this before?

thanks in advance.

[eluser]Michael Wales[/eluser]
When you echo $updatedata['LastResult'] it returns 1197307524.75?

It definitely seems to be an issue with MySQL (almost as if you defined a FLOAT(13,-2) - which I don't think is possible).

I'm curious, although I'm confident it won't change anything - why typecast the microtime() function to a float when passing true as a parameter guarantees to return a float?

[eluser]gtech[/eluser]
Hi Michael, thanks for the reply.. just come back from a joyous family trip to S**sburys.

When you echo $updatedata[’LastResult’] it returns 1197307524.75?

yes thats correct.

why typecast the microtime() function to a float when passing true as a parameter guarantees to return a float?

I was trying everything.. it was a belt and braces approach, originally there was no typcasting.

I know I can get around it by preplaceing the . with a blank and store it as an int, but it seems a cheesy hack. I am going to try several approaches I just wondered if any one has come across this problem before.

[eluser]Michael Wales[/eluser]
Could it possibly be your Float definition? The floats you are displaying are float(12,2) not float(13,2) - MySQL doesn't count the decimal. A quote from the MySQL documentation:

MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

Does microtime() always return a (12,2) float? I have little experience with this function unfortunately.

Sorry I couldn't be of more help - but hopefully I get those gears turning and you can figure it out. When you hit the solution be sure to let us know!

[eluser]gtech[/eluser]
hello again, thanks for the reply... I read your post and gave it a go before I replied, I discovered it was sometimes returning a 13,3 or a 12,2 mostly 13,3... so I changed the database field to float 13,3 but some form of weird rounding still happens.

I now know its not a code igniter problem as I have tried to update the data manually in the MYSQL CLI assuming it would work fine and it does not. so I shall have a play untill the thing works.

[eluser]gtech[/eluser]
got it! I had to use a DOUBLE 13,3 instead of a FLOAT 13,3 it now works... thanks for the input it helped me solve the problem, I am guessing the number was too large to store in a MYSQL float.