What do scale and precision mean when specifying a decimal field type in Doctrine 2?

Question

I'm creating a decimal field to hold a financial figure in Doctrine2 for my Symfony2 application.

Currently, it looks like this:

/**
 * @ORM\Column(type="decimal")
 */
protected $rate;

When I entered a value and said value was persisted to the database, it was rounded to an integer. I'm guessing that I need to set the precision and scale types for the field, but I need someone to explain exactly what they do?

The Doctrine2 documentation says:

precision: The precision for a decimal (exact numeric) column (Applies only for decimal column)

scale: The scale for a decimal (exact numeric) column (Applies only for decimal column)

But that doesn't tell me an awful lot.

I'm guessing precision is the number of decimal places to round to, so I assume that should be 2, but what is scale? Is scale the significant figures?

Should my field declaration be this? :-

/**
 * @ORM\Column(type="decimal", precision=2, scale=4)
 */
protected $rate;

Solution 1

Doctrine uses types similar to the SQL types. Decimal happens to be a fixed precision type (unlike floats).

Taken from the MySQL documentation:

In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99.

Solution 2

Just a quick note: I had to remove the quotes from the attributes precision and scale, like so:

@ORM\Column(type="decimal", precision=8, scale=2)

Solution 3

@Column(type="decimal", precision=5, scale=2) means 123.45

Solution 4

I know this one is old, but why oh why are programmers still using decimal... just use 64bit integer and define the value as cents (or thousands) and not wholes. Everything simplifies from that.

i.e. instead of storing 123.45 just store 12345, do all calculations on integers and when needed present the value to user as 123.45

P.S. And at least for now no-one has 2305843009213693952 cents or 23.058.430.092.136.939,52 wholes on their account.

View more solutions

Comments

[fafa.mnzm]

Sometimes people need to work with percentages and proportions of the money, like giving 25% benefit monthly based on profit or something. what then?

[Waldemar]

You can always multiply the value with 100 ( ( 100*a )/b = % ) and get the % directly. Now I do admit that if you would want to have 25.1234% you would soon run out of the numbers for trilloners. :) Then again if you use the decimal values as others have defined (e.g. precision=10, scale=2) you would only be able to represent 9.999.999.999,99 which is 9+billion which is faaar below what would a simple 64bit integer of cents give you.

[Waldemar]

also in case you need to give somebody x% of something you would just calculate it as you would manually -> x% = x/100 -> ( x*value )/100

[Waldemar]

The only issue could be when dealing with rounding errors, but you have the same issue with decimal.

[Waldemar]

Also I'm curious what do the big banks do if they have a customer with trillions like the USA country?

[fafa.mnzm]

They use bigint, I guess. Or increase the precision to its highest limjt or something.