[dev] Best strategy to store money values

[Luis Felipe Marzagao/Andamentos]

Em 17/08/11 03:03, Ralf Lang escreveu:
> Am Mittwoch, 17. August 2011, 02:12:07 schrieb Luis Felipe
> Marzagao/Andamentos:
>
>>>> I have never developed any app involving money values and
>>>> calculations, so I could use some advice concerning the best
>>>> strategy/practice before I invest time going the wrong direction.
>>>>
>>>> Any pointers are appreciated.
>>> The form type should be "number", the column type "float".
>>>
>>> Jan.
>> Thanks. I still have to do some manually conversion, because the
>> "number" form type only validates and returns the number wih your local
>> decimal point (in my case the comma ',', not the point '.'), which is
>> not accepted by mysql float type. It seems mysql expects to receive a
>> pointed decimal number (like 500.10), otherwise it will loose the digits
>> after the decimal point. But this is something manageable.
>>
>> Let me enjoy the opportunity to ask how do I tell migrations create the
>> column type 'float' with the options (12,2), twelve digits, two being
>> after the decimal point. I can't find any reference on how to pass the
>> correct parameters to $t->column( .... array(????));
> You probably know that, I just want to remind:
No, I didn't know!
> The float type is not suitable if you want to use the price/money value field
> for "equals" matches.
>
> Simplified example:
>
> mysql>  CREATE TABLE items (money_value FLOAT);
> mysql>  INSERT INTO items VALUES (0.99);
> mysql>  select money_value from items;
> +-------------+
> | money_value |
> +-------------+
> |        0.99 |
> |        0.66 |
> |        0.33 |
> +-------------+
>
> BUT
> mysql>  select money_value from items where money_value=0.33;
> Empty set (0.00 sec)
>
> mysql>  select money_value from items where money_value=.66;
> Empty set (0.00 sec)
>
> And so on.
>
> Workaround
> mysql>  select money_value from items where money_value<  .66 + 0.00001 and
> money_value>  0.66 - 0.000001;
> +-------------+
> | money_value |
> +-------------+
> |        0.66 |
> +-------------+
> 1 row in set (0.00 sec)
>
> If you need exact database matches on money values, use a fixed-point type.
>
Hum, okay, I'm going to do some tests. Great tip.
Thanks!
Luis Felipe