Re: currency precision with decimal row
* Jean-Pierre Schwickerath > I'm trying to write some kind of small accounting software and I got > stuck with the problem that doing a SUM() on values of a DECIMAL(7,2) > row did show problems with (relatively) small values (like 173.58). I > found a message explaining that mysql uses doubles internly to compute > the values and this would explain the behaviour. > > Now my question is how should one go around the problem for now? I > decided to use UNSIGNED INT and do a division by 100 after retrieving > a value (or a multiplication by 100 before storing a value) in the > language of the application (php). > Is there another better way to do it? I think your solution is the best. That way you avoid the spooky floating point values, and you have full control of the precision of your calculations. Note that even if you use DECIMAL, a so-called exact numeric type, all calculations are done using approximate types (float/double, i.e. floating point operations). http://www.mysql.com/doc/en/Numeric_types.html > http://www.mysql.com/doc/en/Problems_with_float.html > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Currency
I am guessing that you are trying to find a MySQL data type of "Currency", which does not exist. I generally use a column that is DECIMAL(150,2) for US currency. --Scott At 01:32 PM 10/2/2003, Fabio Bernardo wrote: I´m having some problems with currency´s fields. Actually I dont know what mysql´s field typeI have to choose..^ I wanna input this value: U$32.00 but, when I write the query: Select 'field' from table it returns.. '32' and not 32.00. Which select statement do i have to write? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Currency
At 17:32 -0300 10/2/03, Fabio Bernardo wrote: I´m having some problems with currency´s fields. Actually I dont know what mysql´s field typeI have to choose..^ I wanna input this value: U$32.00 but, when I write the query: Select 'field' from table it returns.. '32' and not 32.00. Which select statement do i have to write? Your requirements are unclear. Are you saying you want to input the value "U$32.00" and have it come back out as "32.00"? If that's what you mean, it seems a little odd to me, frankly. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Currency Problem
this is a php question, try: http://www.php.net/manual/en/function.number-format.php > Hi, > > I just want to know if mysql can set the output of a certain currency to > this pattern : > > $1,500.00 > > If I use a comma on a certain amount then I add it up to another amount with > a comma I get the wrong > answer. I'm using php & mysql by the way. > > Any hints on this one? > > Regards, > > Delz > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: currency type
At 10:57 -0700 7/30/02, Desmond Lee wrote: >Hi Mike > >Thanks for your reply > >Just to check the decimal(6, 2) means show 6 digits to the 2 >decimals of percision. Thus you get a numbers like: >1.34 >30.00 >3344.92 > >Is that correct? That is not correct. DECIMAL(6,2) means 6 digits *total*, of which two are to the right of the decimal point. This means that 1.34 and 3344.92 are okay, but 30.00 is not. > Why do you not use a float and a decmial type? DECIMAL gives you perfect accuracy to the number of decimals specified, with no roundoff error, for one thing. That's why it's good for monetary calculations. On the other hand, it's stored internally as a character string, so DECIMAL operations are not as fast as operations with native floating-point types. > >Thanks > >Desmond > >>From: "Mike(mickako)Blezien" <[EMAIL PROTECTED]> >>Reply-To: [EMAIL PROTECTED] >>To: Desmond Lee <[EMAIL PROTECTED]> >>CC: [EMAIL PROTECTED] >>Subject: Re: currency type >>Date: Tue, 30 Jul 2002 11:36:30 -0500 >> >>>>Desmond Lee wrote: >>> >>> Hi there >>> >>> Does mysql have a currency type, or is it best to just use a varchar or a >>> float? >>> >>> Thanks >>> >>> Desmond >> >>I normally use the DECIMAL(6,2) data type, works very for currency values. >>-- >>Mike(mickalo)Blezien >>=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= >>Thunder Rain Internet Publishing >>Providing Internet Solutions that work! >>http://www.thunder-rain.com >>Tel: 1(985)902-8484 >>=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: currency type
Hi Mike Thanks for your reply Just to check the decimal(6, 2) means show 6 digits to the 2 decimals of percision. Thus you get a numbers like: 1.34 30.00 3344.92 Is that correct? Why do you not use a float and a decmial type? Thanks Desmond >From: "Mike(mickako)Blezien" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Desmond Lee <[EMAIL PROTECTED]> >CC: [EMAIL PROTECTED] >Subject: Re: currency type >Date: Tue, 30 Jul 2002 11:36:30 -0500 > > >>Desmond Lee wrote: > > > > Hi there > > > > Does mysql have a currency type, or is it best to just use a varchar or >a > > float? > > > > Thanks > > > > Desmond > >I normally use the DECIMAL(6,2) data type, works very for currency values. >-- >Mike(mickalo)Blezien >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= >Thunder Rain Internet Publishing >Providing Internet Solutions that work! >http://www.thunder-rain.com >Tel: 1(985)902-8484 >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > > > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: currency type
>>Desmond Lee wrote: > > Hi there > > Does mysql have a currency type, or is it best to just use a varchar or a > float? > > Thanks > > Desmond I normally use the DECIMAL(6,2) data type, works very for currency values. -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(985)902-8484 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: currency: which data type?
Saqib Shaikh writes: > I'm interested to know what data type people consider suitable for storing > currency. As far as I know, the way to do it is to store the number of the smallest possible unit as an integer of some sort. You'll never run into any odd floating point-to-decimal conversion problems, but you might need to use BIGINT if you decide to to have many decimal places or if you're dealing with big numbers. If you use INT and store the price in cents, you'll run into problems around $20M. (I just use USD as an example.) To reduce rounding errors when converting back and forth, and to accomodate for a wide range of currencies, you might want to store more decimals for some currencies than for others. On the other hand, sums should always add up correctly, so this might be a stupid suggestion. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
