Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Float rounding in mySQL

    Please forgive my admittedly basic question, but...

    I'm trying to work out how mySQL data types handle rounding numbers...

    Let's say I put the number 104.853632 into a row that uses FLOAT(12,6)... I need it to keep 6 significant figures to the right of the decimal. Now for some reason, after I put this in it gives me 104.853630 ... in other words it (apparently) rounded to one higher digit than it's actually displaying! I didn't like that so I tried changing it to FLOAT(12,7)... this time it gives me 104.8536301! I can understand the 7 decimal places (as that's what I specified), but why wouldn't it be 104.8536320 ?? Can someone explain what's going on here??

    I don't want it to round off at all! Just store my numbers to 6 significant figures (past the decimal... the number before the decimal can vary from 0 to the hundreds).

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    would using a fixed-precision type (decimal/numeric/whatever mysql calls it?) not solve your problems?
    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com

  • #3
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, it might, but I was reading about data types and they were saying that fixed-precision (digital I think) is actually stored as a string... and that this can decrease performance. Also I need numeric functionality with the data...

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,023
    Thanks
    2
    Thanked 313 Times in 305 Posts
    A single-precision floating-point number is accurate to approximately 7 decimal places
    This also means that it suffers from rounding errors at approximately 7 decimal places, which is what your results show.

    You should either use a DOUBLE "A double-precision floating-point number is accurate to approximately 15 decimal places." or use a DECIMAL.

    There are two problems with FLOATS/DOUBLES.

    1) Math performed on them is done in binary, the number of bits is limited by the hardware and you have precision and rounding problems if you need significant digits close to the limit of what is available.

    2) When they are converted from their internal binary format for display, they also suffer from precision and rounding problems.

    The DECIMAL type uses BCD math and you can specify a greater number of decimal digits to keep rounding out of the significant digits you want. There are also no conversion errors when it is output and displayed because it is already stored in a directly displayable format.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, I see. But I guess I'm interested in the fastest queries and being able to use the stored info as a number...

    How does DECIMAL stack up (compared to DOUBLE) in this case?

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    they are all fast and you probably won't notice any speed difference at all. don't store numbers in varchar/char/text fields.

    if you are doing arithmatic or rounding do not use float/double you will run into errors as described above.

  • #7
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Great! I think decimal is what I want... Thanks a bunch!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •