What do people recommend for storing money amounts? I've seen people use
NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate
and why? This is considering various existing currencies, some having
low rates (like IDR, in which you can have large amount up to hundreds
of trillions) and some high rates (like USD, in which you can have small
amount like 0.1 cent). Are there places/industries which involve values
lower than 0.1 cent?
And what about 'factor' field in currency conversion table? Should I use
REAL, or DOUBLE PRECISION (do we need 15-16 digit precision?) or NUMERIC
(exact numbers). The factor should range between 1E-3 (e.g. converting
IDR to USD) to 1E4 (e.g. converting IDR to pounds/euros).
--
dave
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org 12 17903
On Wed, 2003-12-03 at 07:02, David Garamond wrote: What do people recommend for storing money amounts? I've seen people use NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate and why? This is considering various existing currencies, some having low rates (like IDR, in which you can have large amount up to hundreds of trillions) and some high rates (like USD, in which you can have small amount like 0.1 cent). Are there places/industries which involve values lower than 0.1 cent?
I think you should match the customer's data and use whatever precision
is necessary for it. The needs of a small shop will not be the same as a
currency trader's.
You should not regard amounts in different currencies as equivalent.
You cannot add Euros to dollars and get a meaningful figure; so they
should not be in the same column. If you are handling multiple
currencies, your database design needs to be a lot more sophisticated
than having a single money column.
And what about 'factor' field in currency conversion table? Should I use REAL, or DOUBLE PRECISION (do we need 15-16 digit precision?) or NUMERIC (exact numbers). The factor should range between 1E-3 (e.g. converting IDR to USD) to 1E4 (e.g. converting IDR to pounds/euros).
You should only use NUMERIC for money; any kind of floating point
representation will lose detail somewhere along the line. (I suppose
you could use BIGINT for Japanese Yen.)
--
Oliver Elphick Ol************@ lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
=============== =============== ==========
"What shall we then say to these things? If God be for
us, who can be against us?" Romans 8:31
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Oliver Elphick wrote: You should not regard amounts in different currencies as equivalent. You cannot add Euros to dollars and get a meaningful figure; so they should not be in the same column.
I plan to store amount in a column (NUMERIC) and currency id in another
(CHAR(3)). Plus another column for amount in 'standard' currency (e.g.
USD; all addition/sum will be done to this column).
You should only use NUMERIC for money; any kind of floating point representation will lose detail somewhere along the line. (I suppose you could use BIGINT for Japanese Yen.)
--
dave
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
Funny you mention IDR-- I happen to be in Jakarta at the moment.
Obviously different customers have different requirements, but I always
suggest trading performance for usefulness where necessary.
Obviously, the scale will need to be in accordance with the needs of your
customer (unlikey to need any for IDR; 2, 3, or more depending on industry
for USD). I would also always suggest overshooting the precision by a few
places to ensure that:
1) if there is another banking crisis, your app still performs
2) take into account future inflation
3) take into account future growth.
Best WIshes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
David Garamond wrote: I plan to store amount in a column (NUMERIC) and currency id in another (CHAR(3)). Plus another column for amount in 'standard' currency (e.g. USD; all addition/sum will be done to this column).
If your system really is going to handle multiple, simultaneous currencies,
beware of constant changes in the exchange rate between them. Probably
you'll be better by never storing anything in a 'standard currency' column
and doing instead all math on the fly, referring to a separate
'exchange_rates ' table when needed (i.e. always :-). Of course, all of this
has nothing to do with the technical solution but instead with the bussiness
rules the application must follow, so they must be incorporated early at the
spec level. In the end, probably an accountant will be the most qualified
one to define these things.
With regard to precision, it is common in certain applications the need to
handle very small amounts, especially when used as factors of a larger
calculation. I've even seen once some rates defined in hundredths of cents!
hth
cl.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Martha Stewart called it a Good Thing when cl******@hotmai l.com ("Claudio Lapidus") wrote: With regard to precision, it is common in certain applications the need to handle very small amounts, especially when used as factors of a larger calculation. I've even seen once some rates defined in hundredths of cents!
Well, you don't need terribly much precision in the currency exchange
rate if the amount being converted is small. You only need a couple
significant digits to convert $0.24 USD to the appropriate amount in
$CDN.
But to get the pennies right on a $10,000 USD transaction converted
into GBP (UK Pounds), you need all the official precision that there
is. And if your calculation is off by 4 cents, some of those
accounting folk are liable to thrash you mercilessly over it. If you
get calculations WRONG, they get really uncomfortable, and want to
know why.
--
(reverse (concatenate 'string "ac.notelrac.te neerf" "@" "454aa")) http://www3.sympatico.ca/cbbrowne/li...ributions.html
"Women who seek to be equal to men lack ambition. "
-- Timothy Leary
"Claudio Lapidus" <cl******@hotma il.com> writes: With regard to precision, it is common in certain applications the need to handle very small amounts, especially when used as factors of a larger calculation. I've even seen once some rates defined in hundredths of cents!
Normally there's nothing smaller than a tenth of a cent in US currency. It's
called a "mill". (or "mil"? I forget.). Of course you have to multiply
currency amounts by floating point numbers like interest rates or such, and
that will produce strange numbers but they'll always be rounded off to at
least mills and usually cents. You never actually debit or credit partial
mills.
At least that's how I was taught it was supposed to work.
I'm sure someone somewhere isn't following it.
--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
Christopher Browne wrote: But to get the pennies right on a $10,000 USD transaction converted into GBP (UK Pounds), you need all the official precision that there is. And if your calculation is off by 4 cents, some of those accounting folk are liable to thrash you mercilessly over it. If you get calculations WRONG, they get really uncomfortable, and want to know why.
What I have done is store the currency amounts as bigints, at the same
precision defined for the currency (ie cents for dollars, pence for
pounds, etc). This guarantees that you don't get any rounding errors
when storing the figures as a floating point type. When manipulating the
numbers, I use Java BigDecimals, which don't lose any precision either,
and convert back to bigints to store in the database.
YMMV.
Regards,
Graham
--
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Graham Leggett wrote: Christopher Browne wrote:
But to get the pennies right on a $10,000 USD transaction converted into GBP (UK Pounds), you need all the official precision that there is. And if your calculation is off by 4 cents, some of those accounting folk are liable to thrash you mercilessly over it. If you get calculations WRONG, they get really uncomfortable, and want to know why.
What I have done is store the currency amounts as bigints, at the same precision defined for the currency (ie cents for dollars, pence for pounds, etc). This guarantees that you don't get any rounding errors when storing the figures as a floating point type. When manipulating the numbers, I use Java BigDecimals, which don't lose any precision either, and convert back to bigints to store in the database.
You won't get any rounding errors in NUMERIC either. What people should
be concerned of is to find an arbitrary precision package for the
frontend programming language they're using.
Jan
--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
On Thu, 4 Dec 2003, Jan Wieck wrote: Graham Leggett wrote:
Christopher Browne wrote:
What I have done is store the currency amounts as bigints, at the same precision defined for the currency (ie cents for dollars, pence for pounds, etc). This guarantees that you don't get any rounding errors when storing the figures as a floating point type. When manipulating the numbers, I use Java BigDecimals, which don't lose any precision either, and convert back to bigints to store in the database.
You won't get any rounding errors in NUMERIC either. What people should be concerned of is to find an arbitrary precision package for the frontend programming language they're using.
I agree, I use BigDecimal's in Java, and NUMERIC's in PostgreSQL, they
seem like a perfect match. Floating point numbers are not suitable for
money in my opinion.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Batista, Facundo |
last post by:
Can't find it.
I mean something like:
>> m1 = Money(decimal=2)
>> m2 = Money(decimal=2)
>> m1.value = 3.20
>> m2.value = 2.15
>> print m1 + m2
5.35
|
by: Batista, Facundo |
last post by:
Here I send it.
Suggestions and all kinds of recomendations are more than welcomed.
If it all goes ok, it'll be a PEP when I finish writing the code.
Thank you.
.. Facundo
|
by: Jason Szotak |
last post by:
Recently we upgraded the .Net Framework to v1.1. All of a sudden all of our queries run through .Net pages began showing all 4 of the decimal places for the money data type. Queries run through asp still display only 2 decimal places. This is causing us some validation errors. Is there a way to configure the framework to display 2 decimal places for the money data type?
Thanks,
Jason
|
by: Sam |
last post by:
I am trying to use a Simple form with 3 fields from SQL NorthWind
Database (Order Details Table with 3 Fields. - OrderId, ProductId and
Unit Price). The Field Unit Price has a data type of 'Money Type'.
All I want is to able to edit/view/insert 'UnitPrice' field with 2
decimal points instead of 4 decimal points.
If I use: Bind("UnitPrice", "{0:c}") in Edit Template, I got: "Input
string was not in a correct format...". Although this...
|
by: Sam |
last post by:
Folks..
I am trying to use a Simple form with 3 fields from SQL NorthWind
Database (Order Details Table with 3 Fields. - OrderId, ProductId and
Unit Price). The Field Unit Price has a data type of 'Money Type'.
All I want is to able to edit/view/insert 'UnitPrice' field with 2
decimal points instead of 4 decimal points.
| |
by: Stephan Diehl |
last post by:
Hi lazyweb,
I'm wondering, if there is a usable money data type for python available.
A quick search in pypi and google didn't convey anything, even though the
decimal data type seemed to be planned as a money data type originaly.
Thanks for any pointers
Stephan
|
by: thesti |
last post by:
hi,
i want to ask about money datatype, what makes it special or different that we better use it for a currency related field (like the price of a product) instead of using just, say a bigint or a decimal.
thanks
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |