What is the best SQL data type for currency values? I'm using MySQL but would prefer a database independent type.
This question is related to
mysql
sql
sqldatatypes
It is also important to work out how many decimal places maybe required for your calculations.
I worked on a share price application that required the calculation of the price of one million shares. The quoted share price had to be stored to 7 digits of accuracy.
The only thing you have to watch out for is if you migrate from one database to another you may find that DECIMAL(19,4) and DECIMAL(19,4) mean different things
( http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html )
DBASE: 10,5 (10 integer, 5 decimal) MYSQL: 15,5 (15 digits, 10 integer (15-5), 5 decimal)
Though this may be late, but it will be helpful to someone else.From my experience and research I have come to know and accept decimal(19, 6).That is when working with php and mysql. when working with large amount of money and exchange rate
super late entry but GAAP is a good rule of thumb..
If your application needs to handle money values up to a trillion then this should work: 13,2 If you need to comply with GAAP (Generally Accepted Accounting Principles) then use: 13,4
Usually you should sum your money values at 13,4 before rounding of the output to 13,2.
Assaf's response of
Depends on how much money you got...
sounds flippant, but actually it's pertinant.
Only today we had an issue where a record failed to be inserted into our Rate table, because one of the columns (GrossRate) is set to Decimal (11,4), and our Product department just got a contract for rooms in some amazing resort in Bora Bora, that sell for several million Pacific Francs per night... something that was never anticpated when the database schema was designed 10 years ago.
It depends on the nature of data. You need to contemplate it beforehand.
Although MySQL lets you use decimal(65,30), 31 for scale and 30 for precision seem to be our limits if we want to leave transfer option open.
Maximum scale and precision in most common RDBMS:
Precision Scale Oracle 31 31 T-SQL 38 38 MySQL 65 30 PostgreSQL 131072 16383
September 2015 Zimbabwean government stated it would exchange Zimbabwean dollars for US dollars at a rate of 1 USD to 35 quadrillion Zimbabwean dollars 5
We tend to say "yeah, sure... I won't need that crazy figures". Well, Zimbabweans used to say that too. Not to long ago.
Let's imagine you need to record a transaction of 1 mln USD in Zimbabwean dollars (maybe unlikely today, but who knows how this will look like in 10 years from now?).
- (1 mln USD) * (35 Quadrylion ZWL) = ( 10^6 ) * (35 * 10^15) = 35 * 10^21
- we need:
- 2 digits to store "35"
- 21 digits to store the zeros
- 4 digits to the right of decimal point
- this makes decimal(27,4) which costs us 15 bytes for each entry
- we may add one more digit on the left at no expense - we have decimal(28,4) for 15 bytes
- Now we can store 10 mln USD transaction expressed in Zimbabwean dollars, or secure from another strike of hiperinflation, which hopefully won't happen
For accounting applications it's very common to store the values as integers (some even go so far as to say it's the only way). To get an idea, take the amount of the transactions (let's suppose $100.23) and multiple by 100, 1000, 10000, etc. to get the accuracy you need. So if you only need to store cents and can safely round up or down, just multiply by 100. In my example, that would make 10023 as the integer to store. You'll save space in the database and comparing two integers is much easier than comparing two floats. My $0.02.
You could use something like DECIMAL(19,2)
by default for all of your monetary values, but if you'll only ever store values lower than $1,000, that's just going to be a waste of valuable database space.
For most implementations, DECIMAL(N,2)
would be sufficient, where the value of N
is at least the number of digits before the .
of the greatest sum you ever expect to be stored in that field + 5
. So if you don't ever expect to store any values greater than 999999.99, DECIMAL(11,2)
should be more than sufficient (until expectations change).
If you want to be GAAP compliant, you could go with DECIMAL(N,4)
, where the value of N
is at least the number of digits before the .
of the greatest sum you ever expect to be stored in that field + 7
.
Source: Stackoverflow.com