82

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;
Community
  • 1
  • 1
Anonymous
  • 6,181
  • 7
  • 45
  • 72

5 Answers5

126

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.

Louis-Philippe Huberdeau
  • 5,341
  • 1
  • 19
  • 22
33

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)
khagler
  • 3,996
  • 29
  • 40
giggsy
  • 401
  • 4
  • 6
26
@Column(type="decimal", precision=5, scale=2) means 123.45
slfan
  • 8,950
  • 115
  • 65
  • 78
somspeaks
  • 534
  • 2
  • 6
  • 11
  • What happens if you store 1.20 in a precision=5, scale=2 field? Or is the precision=5 a maximum? – Jos Mar 01 '23 at 10:48
1

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.

Waldemar
  • 171
  • 9
  • Sometimes people need to work with percentages and proportions of the money, like giving 25% benefit monthly based on profit or something. what then? – fafa.mnzm Jan 19 '21 at 14:03
  • 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 Jan 20 '21 at 16:02
  • 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 Jan 20 '21 at 16:05
  • The only issue could be when dealing with rounding errors, but you have the same issue with decimal. – Waldemar Jan 20 '21 at 16:10
  • Also I'm curious what do the big banks do if they have a customer with trillions like the USA country? – Waldemar Jan 20 '21 at 16:14
  • They use bigint, I guess. Or increase the precision to its highest limjt or something. – fafa.mnzm Jan 20 '21 at 21:32
  • In the bank case, they would store the values probably as strings and use special classes within their programmings that split the strings up to groups of integers for which they do the maths separately, just like in school mathematics - the major difference then is that in bank-like programming you do not deal with single digits but with blocks of digits (which means computing modulo max integer size and not modulo 10). – Heiko Vogel Feb 22 '23 at 11:55
0
 * @ORM\Column(type="decimal", precision=10, scale=2)
Fahim
  • 407
  • 4
  • 11