Re: [sqlite] Output in currency format
On 13 Nov 2009, at 4:40pm, Peter Haworth wrote: > That often give rise to some rounding issues. I do all the math using > however many decimal places are given to me and then round the total > to two decimal places, then calculate how much is owed to each band > member based on that total. But whoever writes the checks for some of > the above outfits doesn't seem to use the same logic since the checks > I get are often different than what I calculate by a few pennies > (usually more than the total I calculate). Not really a big deal but > it does require some otherwise-unnecessay accounting entries to deal > with the rounding errors. Whether or not you keep rounding fractions for yourself, be careful that the bands have agreed on how you do rounding. Also, have a tax accountant check out that your method of rounding is acceptable by whatever tax authorities you deal with. Oh yeah, and sort out what tax calculations you're going to have to do before you write the system. I once wrote a system that dealt with stock events for many different types of stock and it had to use one type of rounding to work out how much money to give the stockholder and another type of rounding to work out how much tax to pay. My customer was allowed to keep any difference between the two (which was always positive). Horribly complicated system to write but it generated a few pence profit every few seconds just by doing what the taxman said. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On Fri, Nov 13, 2009 at 03:07:27AM +, Simon Slavin wrote: > > On 13 Nov 2009, at 12:34am, Nicolas Williams wrote: > > > On Thu, Nov 12, 2009 at 11:40:23PM +, Simon Slavin wrote: > >> There's still some possibility for confusion, however: how many places > >> of decimals do you use for each currency ? As far as I know, no > >> currently traded currency uses more than two digits of precision. > > ^^^ > > They use integer math to avoid floating point rounding issues, but > > logically those integers are still real (or at least rational) numbers, > > and so we can speak of base, mantissa and exponent. The precision > > required is pretty large, much more than two digits. > > Integers in all languages I'm aware of are not stored as > mantissa/exponent, they're stored as bits with complete precision. That's why I wrote "logically". Clearly, very, very clearly, int64_t is not a float, has no mantissa, no exponent. But you can use it as though an int64_t were a real (well, rational) number. > You can say you need a particular number of bits, but you'll never > lose the last bit (the 1s) just because your numbers have got too big. > You'll get an overflow error instead. Yes. > By 'two digits of precision' I was referring to cents for US dollars, > pence for pounds sterling, etc.. Some currencies have no fractional > part (e.g. Yen). And I was sure there were ... ah, here we are: Tenths of pennies are also used though. See just about any gas station in the U.S. Oh, I forgot, with 64-bit _signed_ ints you get one fewer digit of precision than I wrote earlier. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
Thanks for all the comments on this. Didn't realise there were so many things to worry about when dealing with currency! The system I'm developing is only dealing with US dollars right now but I would hope it might make it's way into other countries at some point. Even with dollars, I will have to deal with more than two decimal places though. The application is aimed at independant music bands to manage their businesses and one of the things it does is import data from places like iTunes, Rhapsody, etc whose royalty payments commonly extend to three decimal places or more. That often give rise to some rounding issues. I do all the math using however many decimal places are given to me and then round the total to two decimal places, then calculate how much is owed to each band member based on that total. But whoever writes the checks for some of the above outfits doesn't seem to use the same logic since the checks I get are often different than what I calculate by a few pennies (usually more than the total I calculate). Not really a big deal but it does require some otherwise-unnecessay accounting entries to deal with the rounding errors. Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On Thu, 12 Nov 2009, Dan Bishop wrote: > Microsoft Excel has a similar problem. I ran into it back when I was > working in a credit union and tried to import a CSV file containing credit > card numbers. Wouldn't have noticed except that credit card numbers are > 16 digits long and double only has 15 digits of precision. Excel also has an incorrect formula for Net Present Value. Lotus 1-2-3 had an incorrect formula for standard deviation (they used the population formula rather than the sample formula). I don't use any M$ software, but when I need to import large numbers into a spreadsheet (I use XessSE), it's always as text. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On 11/13/09 6:31 , "Dan Bishop" wrote: > Microsoft Excel has a similar problem. I ran into it back when I was > working in a credit union and tried to import a CSV file containing > credit card numbers. Wouldn't have noticed except that credit card > numbers are 16 digits long and double only has 15 digits of precision. Well in that case and for once, I must say the problem is not Excel's, it's yours: credit card "numbers" are not numbers at all, but strings, that just happen to use digits only. You should have imported them as text, not as numbers. Excel had no way to guess. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
Simon Slavin wrote: > On 13 Nov 2009, at 3:30am, Roger Binns wrote: > > >> Simon Slavin wrote: >> >>> Integers in all languages I'm aware of are not stored as mantissa/exponent, >>> they're stored as bits with complete precision. >>> >> There is one huge exception I found out the hard way recently: Javascript >> stores all 'integers' as floating point and so Javascript integers lose >> precision. For example if you try to use 9223372036854775807 in Javascript >> it will keep coming back as 9223372036854776000. >> > > JavaScript doesn't have an integer type, just a number type: > > var myVariable = 42 > document.writeln ( "myVariable is a " + typeof myVariable ) Microsoft Excel has a similar problem. I ran into it back when I was working in a credit union and tried to import a CSV file containing credit card numbers. Wouldn't have noticed except that credit card numbers are 16 digits long and double only has 15 digits of precision. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > JavaScript doesn't have an integer type, just a number type: You are agreeing with me :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkr86D8ACgkQmOOfHg372QQFMQCgogWhSWfZzSpfILfUDhWJqjK1 w8EAoJosqghW1lBnhE3iZ4soWhpY5xFG =rJHI -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On 13 Nov 2009, at 3:30am, Roger Binns wrote: > Simon Slavin wrote: >> Integers in all languages I'm aware of are not stored as mantissa/exponent, >> they're stored as bits with complete precision. > > There is one huge exception I found out the hard way recently: Javascript > stores all 'integers' as floating point and so Javascript integers lose > precision. For example if you try to use 9223372036854775807 in Javascript > it will keep coming back as 9223372036854776000. JavaScript doesn't have an integer type, just a number type: var myVariable = 42 document.writeln ( "myVariable is a " + typeof myVariable ) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > Integers in all languages I'm aware of are not stored as mantissa/exponent, > they're stored as bits with complete precision. There is one huge exception I found out the hard way recently: Javascript stores all 'integers' as floating point and so Javascript integers lose precision. For example if you try to use 9223372036854775807 in Javascript it will keep coming back as 9223372036854776000. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkr80sIACgkQmOOfHg372QTLaACfZW8LyMa9vg3RUXuw+95L5PlS 390AmwU27YpaoP6Qf2hOd+hnPpYoJLl/ =hEhD -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On 13 Nov 2009, at 12:34am, Nicolas Williams wrote: > On Thu, Nov 12, 2009 at 11:40:23PM +, Simon Slavin wrote: >> There's still some possibility for confusion, however: how many places >> of decimals do you use for each currency ? As far as I know, no >> currently traded currency uses more than two digits of precision. > ^^^ > They use integer math to avoid floating point rounding issues, but > logically those integers are still real (or at least rational) numbers, > and so we can speak of base, mantissa and exponent. The precision > required is pretty large, much more than two digits. Integers in all languages I'm aware of are not stored as mantissa/exponent, they're stored as bits with complete precision. You can say you need a particular number of bits, but you'll never lose the last bit (the 1s) just because your numbers have got too big. You'll get an overflow error instead. By 'two digits of precision' I was referring to cents for US dollars, pence for pounds sterling, etc.. Some currencies have no fractional part (e.g. Yen). And I was sure there were ... ah, here we are: http://www.worldatlas.com/aatlas/infopage/currency.htm Okay, so do not hardwire your code for two decimal places because you won't handle Kuwaiti dinar correctly. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On Thu, Nov 12, 2009 at 11:40:23PM +, Simon Slavin wrote: > There's still some possibility for confusion, however: how many places > of decimals do you use for each currency ? As far as I know, no > currently traded currency uses more than two digits of precision. ^^^ They use integer math to avoid floating point rounding issues, but logically those integers are still real (or at least rational) numbers, and so we can speak of base, mantissa and exponent. The precision required is pretty large, much more than two digits. Consider U.S. dollars, where we need to express from some fraction of pennies to tens of trillions of dollars. That's at least 16 digits of precision. You need to use larger than 32-bit integers for this, meaning, in practice, 64-bit integers. Besides, 100 years ago 16 digits of precision for counting money would probably have seemed farfeteched, so use 64-bit integers, which gets you a bit less than 20 digits of precision. (Hmmm, 19 digits looks a bit small now!) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On 12 Nov 2009, at 7:17pm, Peter Haworth wrote: > Just to be sure I get my calculations correct, the suggestion is that > all currency amounts should be stored in the database as whole numbers > in fields of type INTEGER. Calculations would be done using the whole > numbers and I'll need some routines to convert between the database > format and the display format. Do I have that right? Exactly. I'm familiar with lots of big pieces of bank software and that's how they do it. There's still some possibility for confusion, however: how many places of decimals do you use for each currency ? As far as I know, no currently traded currency uses more than two digits of precision. Some systems use two digits for dollars (because that's the actual smallest unit you can trade in it). But some use four digits for every currency because it makes the library routines simpler, betrays rounding problems in your code, and allows for figures to be rounded later. It's one of the unsettled problems of financial software. Since you probably won't have to deal with multi-currency rounding problems I'd recommend you stick to two digits unless advised not to by someone who knows exactly what you're doing. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
That has always been my most effective last resort when attempting to do business math with many databases and development environments. > Thanks for all the advice on this. Just to be clear, I wasn't > referring to the accuracy of calculations when I compared the sqlite > date/time formatting capabilites to the lack of similar functionality > for currency, just the fact that there is a precedent for sqlite > providing output formatting capability for some types of data. > > Just to be sure I get my calculations correct, the suggestion is that > all currency amounts should be stored in the database as whole numbers > in fields of type INTEGER. Calculations would be done using the whole > numbers and I'll need some routines to convert between the database > format and the display format. Do I have that right? > > Pete Haworth > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
Thanks for all the advice on this. Just to be clear, I wasn't referring to the accuracy of calculations when I compared the sqlite date/time formatting capabilites to the lack of similar functionality for currency, just the fact that there is a precedent for sqlite providing output formatting capability for some types of data. Just to be sure I get my calculations correct, the suggestion is that all currency amounts should be stored in the database as whole numbers in fields of type INTEGER. Calculations would be done using the whole numbers and I'll need some routines to convert between the database format and the display format. Do I have that right? Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On 11 Nov 2009, at 4:51pm, Fred Williams wrote: > The best (safest?) way I have found to handle non scientific math is to > work strictly with integers and multiply and divide using ROUND/TRUNC as > required to gain the precision required. This includes way more than > SQLite situations as well. Another aspect of the problem is where to convert from integers to currency: inside SQL, in library routines, or in each application. At the moment my preference is that all numbers inside SQL are integers. No part of the SQL system knows anything but integer English currency, integer Euro currency, integer US currency, etc.. Conversion between integers and currency format is always done in surrounding software. But there are arguments for doing it in other ways. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
The best (safest?) way I have found to handle non scientific math is to work strictly with integers and multiply and divide using ROUND/TRUNC as required to gain the precision required. This includes way more than SQLite situations as well. Borland (Code Gear) seem to be the only developer tools producer to recognize the need for "business" math with their native BCD data type. Fred > Seems like I should handle the formatting in my application. Not sure > I agree that sqlite is not the place to do output formatting - it > provides lots of date and time formatting features so at least in that > area, output formatting is available. > > Thanks also for the info re accuracy/REAL formatting. I will change > my db design accordingly. > > Thanks, > > Pete Haworth > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On Wed, Nov 11, 2009 at 10:07 AM, Peter Haworth wrote: > Seems like I should handle the formatting in my application. Not sure > I agree that sqlite is not the place to do output formatting - it > provides lots of date and time formatting features so at least in that > area, output formatting is available. > A wee bit of error in time formatting and display usually will not amount to a hill of beans, but make an error in the number of pennies owed to someone and all hell will break loose. > Thanks also for the info re accuracy/REAL formatting. I will change > my db design accordingly. > > Thanks, > > Pete Haworth > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, Wisconsin, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
Seems like I should handle the formatting in my application. Not sure I agree that sqlite is not the place to do output formatting - it provides lots of date and time formatting features so at least in that area, output formatting is available. Thanks also for the info re accuracy/REAL formatting. I will change my db design accordingly. Thanks, Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter Haworth wrote: > I have a column defined with a type of FLOAT, which I believe is > treated as REAL by SQLite. When selecting that column, I would like > it to be returned with a leading "$" sign and always have a decimal > point and two numbers after the decimal point. It is a spectacularly bad idea to use floating point for currency. Numbers that look simple to us humans such as .01 and .10 are truncated recurring fractions in the binary representation used by the underlying hardware and will introduce errors in your calculations. This page helps show the details: http://docs.python.org/tutorial/floatingpoint.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkr59wgACgkQmOOfHg372QRxWACfVpaAtdRTQL6DS/vuphnZi+wu ANYAnjnHZoOLPPfJWohRpPBYTUK8xM04 =Xbdt -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
On Tue, 10 Nov 2009, Peter Haworth wrote: > Is there a way to do this or should I plan on handling it within the > application? Pete, The latter. Display formatting is not part of SQL. You might also consider using integer values for money because the math is more accurate. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output in currency format
There's no way to force SQLite to return exactly 2 decimal places for you. You have to do it in your application or if you really-really need to do it in sql you can do it like this (assuming you need column col from table tab): select '$'||case when length(col) = 1 then '0.0'||col when length(col) = 2 then '0.'||col else substr(col, 1, length(col) - 2)||'.'||substr(col, length(col) - 1) end as col from (select cast(round(col * 100) as text) as col from tab) But I wouldn't do that if I were you. ;-) Pavel On Tue, Nov 10, 2009 at 1:17 PM, Peter Haworth wrote: > I have a column defined with a type of FLOAT, which I believe is > treated as REAL by SQLite. When selecting that column, I would like > it to be returned with a leading "$" sign and always have a decimal > point and two numbers after the decimal point. I can use > concatenation to get the "$" sign in there but have not been able to > enforce the inclusion of a decimal point and two decimal places. > Whole numbers are returned with no decimal point and no decimal > places. Numbers where the second decimal place would be a zero are > returned with the decimal point but only one decimal place. > > I tried using the round function specifying 2 decimal places but this > does not affect the output. I have also tried using CAST to force the > column into various other types but that also does not affect the > output. > > Web searches suggest that the CONVERT function is available in other > SQL implementations but I don't see that anywhere in the SQLite > documentation. Is there a way to do this or should I plan on handling > it within the application? > > Thanks > > Pete Haworth > > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Output in currency format
I have a column defined with a type of FLOAT, which I believe is treated as REAL by SQLite. When selecting that column, I would like it to be returned with a leading "$" sign and always have a decimal point and two numbers after the decimal point. I can use concatenation to get the "$" sign in there but have not been able to enforce the inclusion of a decimal point and two decimal places. Whole numbers are returned with no decimal point and no decimal places. Numbers where the second decimal place would be a zero are returned with the decimal point but only one decimal place. I tried using the round function specifying 2 decimal places but this does not affect the output. I have also tried using CAST to force the column into various other types but that also does not affect the output. Web searches suggest that the CONVERT function is available in other SQL implementations but I don't see that anywhere in the SQLite documentation. Is there a way to do this or should I plan on handling it within the application? Thanks Pete Haworth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
