?
Solved

Problem with very small decimals

Posted on 2009-06-29
10
Medium Priority
?
785 Views
Last Modified: 2012-08-13
Hi

I am just a novice mysql user so hopefully you will be able to help me.

I am storing some very small numbers in a field of type double. Some of the numbers are being stored as values like this: 8.72852595488195e-005. This is no good to me because some of the 3rd party vb.net components that I work with cannot handle the data in this form (ie. e-005).

Can i prevent the database from storing the values with this notation? Perhaps I need to use a different data type?

many thanks
0
Comment
Question by:andieje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 3

Expert Comment

by:psycle
ID: 24735380
I have never seen mysql store values like that.  This sounds like a problem with your application.
0
 
LVL 29

Expert Comment

by:fibo
ID: 24743298
I think you need to make a distinction between:
- how the number is stored, and more specifically how many significant digits are kept, or more precisely are usable
- how the number is displayed.
(and remember than values are stored with binary digits, while they are rendered as decimal digits... which adds some problems).

When looking at the value, it is describing
0.0000872852595488195

If you know that all your values will be in the range of 0.5e-5 (ie 0.000 005) to 10.0e-5 (ie 0.000 100), and that you will need 3 significant digits eg 0.598e-5 (0.000 00598) you might consider storing not the values but the values time e+8, ie scaling them to 100 000 000 times the value.
0.598e-5 would be stored as 598,  10.0e-5 as 10 000
No problem with additions and substarction, you would just need to remember that these values are to be divided by e+8, ie by 100 000 000.
BEWARE though that you have to take a special care
- if you are multiplying or dividing by a "normal" value (eg 13%, or one-tenth, or 10 times), no problem (but remember that you will have to round the result to integer value)
- if you are multiplying two of the numbers together.... remember that you will have to divide the "integer result" by the scaling factor 100 000 000 (once)
- if you are dividing two of the numbers together... nothing to do, scale is correct.
0
 

Author Comment

by:andieje
ID: 24744557
Hi

The values can be anything from large to very small.

The data in this form -  8.72852595488195e-005- must come from an application I am using which is storing the data direclty in the database. Is there anyway I can get mysql to convert this data to its other form (0.0000872852595488195) when data like this is written to the database? Perhaps you could use an insert/update trigger?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 29

Expert Comment

by:fibo
ID: 24744870
Yes you can, but in fact you can't.

Why?
- you define (ie it cannot be changed) the width of the field
- you can define that you want 20 digits out of which 19 after the decimal point... you can define 20 digits out of which 10 after the decimal point... but you cannot have BOTH at the same time

So if you want to cover a range of 10 digits before the decimal points (and say 8 significant digits) down to 12 after the decimal point (out of which still 8 significvant digits)... you will need to go into "scientific notation" which will use powers of ten as a scaling factor.

BEWARE of the significance of computations in these situations... specially when dealing with additions and substractions.
eg if A=3.E+10 (30 000 000 000), B=2.E-5 (0.00002), and if you add them keeping 8 significant digist, you will be in a "non-mathematical situation" where A+B and A are (numerically) equal while B>0...

0
 
LVL 3

Accepted Solution

by:
psycle earned 2000 total points
ID: 24744885
The problem is not mysql.  If you login directly to mysql and enter large decimal numbers into the database and select them back out, you'll see that they are exactly as you entered them.  I believe the problem is vb.net.  Leave it M$ to muck about with your data! :-)

Search google for "vb.net displaying numbers in scientific notation" and there are a ton of results that should point you in the right direction.  I don't know vb.net well enough to give you code and I don't feel good about copying pasting an example from google that you could easily get yourself.

It seems that if you convert the number to a string it will display properly.  I said I wasn't going to do this, but here is an example showing the problem...

Dim d As Double = Math.PI / 10000000000
Dim ds As String
ds = d.ToString() 'with scientific notation
ds = d.ToString("N20") 'without

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24746797
It is how it is being presented to you most likely as you suspect.  The data should be stored without the scientific notation.  On the VB.NET side, use Decimal instead of Double.

Dim d As Decimal
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24746814

Dim d As Decimal = Math.PI / 10000000000
MsgBox(d) 'Without scientific notation
Dim ds As String = d.ToString() 
MsgBox(ds) 'Also without scientific notation

Open in new window

0
 

Author Closing Comment

by:andieje
ID: 31597848
Many thanks
0
 

Author Comment

by:andieje
ID: 24748229
mwvisa - I'm sorry i did not see your comment about using a decimal. That was actually key to fixing the issue. I had to this

dim d as decimal = getReallySmallValueasDouble.tostring("N20")

to get rid of the notation
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24748670
Just glad to help.

Best regards,
Kevin
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Creating and Managing Databases with phpMyAdmin in cPanel.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question