Thread: dividing money by money
When I divide a money value by another money value, I get an error message,=
as follows:
***************************
psql (8.4.1)
Type "help" for help.
postgres=3D# select version();
version=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
-----------------------------------------------------------
PostgreSQL 8.4.1 on i386-apple-darwin9.8.0, compiled by GCC i686-apple-dar=
win9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5490), 32-bit
(1 row)
postgres=3D# select '$2'::money / '$1'::money;
ERROR: operator does not exist: money / money
LINE 1: select '$2'::money / '$1'::money;
^
HINT: No operator matches the given name and argument type(s). You might n=
eed to add explicit type casts.
***************************
I expected to get a result of 2 or 2.0 in some numeric type (maybe double p=
recision). The result should be a pure number because the units (dollars, i=
n this case) cancel out.
The ability to divide money by money would be useful for finding what perce=
nt one money value is of another. That is what I was wanting to use it for=
=97finding out what percentage of a customer's original balance has been pa=
id off.
It would also provide a better way to convert money into numeric types than=
the regular expression in the documentation. You could just divide the mon=
ey amount by '1'::money.
Andy Balholm
(509) 276-2065
andy@balholm.com
Andy Balholm wrote: > The ability to divide money by money would be useful for finding > what percent one money value is of another. That certainly sounds useful and natural to me. I don't think it rises to the level of a *bug*, but it's a reasonable request for enhancement. If there are no objections I'll add it to the TODO list. -Kevin
On 2010-03-30, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Andy Balholm wrote: > >> The ability to divide money by money would be useful for finding >> what percent one money value is of another. > > That certainly sounds useful and natural to me. I don't think it > rises to the level of a *bug*, but it's a reasonable request for > enhancement. If there are no objections I'll add it to the TODO > list. That'd also make it easy to convert money to numeric or double by dividing by '1'::money.
Kevin Grittner írta:
> Andy Balholm wrote:
>
>
>> The ability to divide money by money would be useful for finding
>> what percent one money value is of another.
>>
>
> That certainly sounds useful and natural to me. I don't think it
> rises to the level of a *bug*, but it's a reasonable request for
> enhancement. If there are no objections I'll add it to the TODO
> list.
>
How about improving the money type so it can store
values in different currencies?
=# create table money1 (x money);
CREATE TABLE
=# insert into money1 values ('1');
INSERT 0 1
=# select * from money1;
x
--------
Ft1,00
(1 sor)
=# insert into money1 values ('$1');
ERROR: invalid input syntax for type money: "$1"
pg_dump -t money1:
==================
Ft1,00
\.
==================
Loading this dump into another database that happens
to have a different locale than hu_HU fails.
But then any operator between two money values would
only work if both values have the same currency.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
Boszormenyi Zoltan wrote: > Loading this dump into another database that happens > to have a different locale than hu_HU fails. > > But then any operator between two money values would > only work if both values have the same currency. > indeed, its all a big tarpit. next, you'll want currency conversion tables. and, are there still any currenccies like old style UK where the subunits aren't 100ths? schillings or whatever
John R Pierce írta: > Boszormenyi Zoltan wrote: >> Loading this dump into another database that happens >> to have a different locale than hu_HU fails. >> >> But then any operator between two money values would >> only work if both values have the same currency. >> > > indeed, its all a big tarpit. next, you'll want currency conversion > tables. and, are there still any currenccies like old style UK where > the subunits aren't 100ths? schillings or whatever Yeah, and we could also switch PG numeric type to base 12 to count in dozens or base 60 to deal with the mayan calendar... ;-) -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics ---------------------------------- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/
Boszormenyi Zoltan wrote: > How about improving the money type so it can store > values in different currencies? Have you seen "taggedtypes"? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
John R Pierce <pierce@hogranch.com> wrote: > Boszormenyi Zoltan wrote: >> But then any operator between two money values would >> only work if both values have the same currency. That sounds like a sane limitation. > and, are there still any currenccies like old style UK where > the subunits aren't 100ths? schillings or whatever I'm not sure if you're arguing for or against the database type knowing how to divide those to get a percentage, versus putting the onus on the application programmer. Where does it make the most sense to you to put such logic? -Kevin
Forgot to send to the list.... On Tue, Mar 30, 2010 at 8:25 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > John R Pierce <pierce@hogranch.com> wrote: >> Boszormenyi Zoltan wrote: > >>> But then any operator between two money values would >>> only work if both values have the same currency. > > That sounds like a sane limitation. > >> and, are there still any currenccies like old style UK where >> the subunits aren't 100ths? =A0 schillings or whatever > > I'm not sure if you're arguing for or against the database type > knowing how to divide those to get a percentage, versus putting the > onus on the application programmer. =A0Where does it make the most > sense to you to put such logic? With due respect, this sort of thing is rather difficult to get right all at once. =A0I would suggest at some point having a modified MONEY or maybe to avoid conflicts let's call it a CURRENCY datatype on Pg-foundry where we can experiment and get these details right. =A0I am thinking of doing a rough draft in SQL and PLPGSQL so that someone can convert to C once everything works properly :-). If folks are interested, I might make a simple approximation of this that would require 8.4 or higher. =A0It might come in handy for LedgerSMB too..... Best Wishes, Chris Travers
Chris Travers <chris@metatrontech.com> wrote: > With due respect, this sort of thing is rather difficult to get > right all at once. The existing type is fixed point and we know how to add and subtract two of them. I don't think it's all that difficult to add division, yielding some non-money numeric type (like perhaps float8). Neither do I see it as a particularly slippery slope. If someone has a list of other things they want to do for monetary types -- I don't see that it has anything to do with this particular request, unless some convincing argument can be made that adding this would make the other features harder to implement. Do we have to take a simple request for something useful and blow it up into something grand and complicated? Well, I mean, *every* time? ;-) The grander aspects of this thread would make more sense as a separate thread for a longer-term effort, probably for a new type (or set of types). -Kevin
I wrote: > yielding some non-money numeric type (like perhaps float8). Hmmm... Given that we've already had a couple posts on the idea that dividing by '1'::money could convert money to something more general, I guess it would be safer to stick to numeric. -Kevin
On Tue, Mar 30, 2010 at 12:22 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > [Did you mean to take this off-list, or was that accidental?] Accidental. > > Chris Travers <chris@metatrontech.com> wrote: > >> With due respect, this sort of thing is rather difficult to get >> right all at once. > > Division of two fixed-point numbers we already know how to add, or > the whole suite of all features one might possibly want in a > monetary data type? =A0(I get the feeling that some of the posts on > this thread involve a straw man going down a slippery slope.... ;-) Ok. Here is my application: I write a multi-currency accounting program backed by PostgreSQL. After 1.3 is released (2Q this year), we expect to be doing a full redesign. What I am thinking about is having a custom data type, something like: CREATE DOMAIN curr VARCHAR(3); CREATE TYPE monetary AS (amount NUMERIC, currency CURR, multiplier NUMERIC); This reduces into two basic components: a value (amount * multiplier) and a currency identifier (USD, etc). One could also then store monetary[] arrays for addressing specific denomination storage. I.e. "When closing the till we had 26 pennies, 53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5 $20 bills." Then we can allow NUMERIC arithmetic on monetary amounts provided that the CURR field is the same. We could also store things like the cash counted from a till at the end of the day by denomination. One could have easy monetary::numeric casts as well. Anyway, that's my basic thinking. One could further add currency conversion tables to an application if necessary. Just thinking about the more general problem and how things could be handled more gracefully... Best Wishes, Chris Travers
Chris Travers <chris@metatrontech.com> wrote:
> Just thinking about the more general problem and how things could
> be handled more gracefully...
Sure, but in the meantime, consider:
test=# select '12'::money * '2'::numeric;
?column?
----------
$24.00
(1 row)
test=# select '24'::money / '2'::numeric;
?column?
----------
$12.00
(1 row)
test=# select '24'::money / '12'::money;
ERROR: operator does not exist: money / money
LINE 1: select '24'::money / '12'::money;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
So we support:
a * b = c
c / b = a
but don't even *think* about c / a = b ???
The OP just wanted to add some symmetry to this, so that the
existing class could handle a not-uncommon use case more easily. As
far as I can see, the implementation of this operator could convert
two int64 values to numeric values and perform numeric division to
get the result. (I was going to mark the TODO as an easy one.) I
don't see how this change would affect what you want to do, one way
or the other.
-Kevin
On Mar 31, 2010, at 7:07 AM, Kevin Grittner wrote:
> (I was going to mark the TODO as an easy one.)=20=20
I thought it would be pretty simple, too, so I decided to go ahead and writ=
e and test it as an external module.=20
I think the function definition could be pasted directly into an appropriat=
e place in src/backend/utils/adt/cash.c, if someone wants to add it to the =
main code base. The SQL to load it would need to be modified somewhat to fi=
t into postgres.bki.=20
Here is the C source:
#include <postgres.h>
#include <fmgr.h>
#include <utils/cash.h>
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(cash_div_cash);
/* cash_div_cash()
* Divide cash by cash, returning float8.
*/
Datum
cash_div_cash(PG_FUNCTION_ARGS)
{
Cash dividend =3D PG_GETARG_CASH(0);
Cash divisor =3D PG_GETARG_CASH(1);
float8 quotient;
=20=20=20=20
if (divisor =3D=3D 0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
=20=20=20=20
quotient =3D (float8)dividend / (float8)divisor;
PG_RETURN_FLOAT8(quotient);
}
---------------------------------------------------------------------------=
----------
And here is the SQL to load it (assuming it has been compiled as a dynamica=
lly loadable module named divide_money and placed in the library directory =
on the server):
CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE
AS '$libdir/divide_money', 'cash_div_cash';
=20=20=20=20
CREATE OPERATOR / (
PROCEDURE =3D cash_div_cash,
LEFTARG =3D money,
RIGHTARG =3D money
);
Andy Balholm <andy@balholm.com> wrote: > quotient = (float8)dividend / (float8)divisor; > PG_RETURN_FLOAT8(quotient); That was my first inclination, but the fact that two different people talked about using division by '1'::money as a way to convert money to another type has me nervous about using an approximate type. Any chance you could rework it using numeric? I know it's less trivial that way, but unless we provide a cast to numeric, I'm afraid people will use the above trick, assign it to a numeric variable or column, and then wonder why they've lost precision. Or I guess we could leave this as you've written it and add support for a cast from money to numeric. -Kevin
On Mar 31, 2010, at 11:01 AM, Kevin Grittner wrote: > That was my first inclination, but the fact that two different > people talked about using division by '1'::money as a way to convert > money to another type has me nervous about using an approximate > type. Any chance you could rework it using numeric? I know it's > less trivial that way, but unless we provide a cast to numeric, I'm > afraid people will use the above trick, assign it to a numeric > variable or column, and then wonder why they've lost precision. >=20 > Or I guess we could leave this as you've written it and add support > for a cast from money to numeric. It probably is wiser to rewrite it with the numeric type. A cast from money= to numeric is theoretically ambiguous (the result could be either dollars = or cents), although most people would expect dollars. I'll see if I can rewrite it with a return type of numeric.
> Or I guess we could leave this as you've written it and add support
> for a cast from money to numeric.
I tried rewriting my function to use numeric, but I discovered that numeric=
division is not exact. (Otherwise SELECT 1::numeric / 3::numeric would res=
ult in an infinite loop.) So I went back to my float8 version and wrote a c=
ast from money to numeric.
Here is my C source code now:
#include <postgres.h>
#include <fmgr.h>
#include <utils/cash.h>
#include <utils/numeric.h>
#include <utils/pg_locale.h>
PG_MODULE_MAGIC;
extern Datum int8_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_div(PG_FUNCTION_ARGS);
extern Datum numeric_mul(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(cash_div_cash);
/* cash_div_cash()
* Divide cash by cash, returning float8.
*/
Datum
cash_div_cash(PG_FUNCTION_ARGS)
{
Cash dividend =3D PG_GETARG_CASH(0);
Cash divisor =3D PG_GETARG_CASH(1);
float8 quotient;
=20=20=20=20
if (divisor =3D=3D 0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
=20=20=20=20
quotient =3D (float8)dividend / (float8)divisor;
PG_RETURN_FLOAT8(quotient);
}
PG_FUNCTION_INFO_V1(cash_numeric);
/* cash_numeric()
* Convert cash to numeric.
*/
Datum
cash_numeric(PG_FUNCTION_ARGS)
{
Cash money =3D PG_GETARG_CASH(0);
int fpoint;
int64 scale;
int i;
Numeric result;
Datum amount;
Datum numeric_scale;
Datum one;
=20=20=20=20
struct lconv *lconvert =3D PGLC_localeconv();
=20=20=20=20
/*=20
* Find the number of digits after the decimal point.
* (These lines were copied from cash_in().)
*/
fpoint =3D lconvert->frac_digits;
if (fpoint < 0 || fpoint > 10)
fpoint =3D 2;
scale =3D 1;
for (i =3D 0; i < fpoint; i++)=20
scale *=3D 10;
=20=20=20=20
amount =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(mone=
y));
one =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(1));
numeric_scale =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(scal=
e));
numeric_scale =3D DirectFunctionCall2(&numeric_div, one, numeric_scale);
result =3D DatumGetNumeric(DirectFunctionCall2(&numeric_mul, amount, nu=
meric_scale));
=20=20=20=20
result->n_sign_dscale =3D NUMERIC_SIGN(result) | fpoint; /* Display the=
right number of decimal digits. */
=20=20=20=20
PG_RETURN_NUMERIC(result);
}
---------------------------------------------------------------------------=
---------
And here is the SQL it takes to load it:
CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_div_cash';
CREATE FUNCTION cash_numeric(money) RETURNS numeric
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_numeric';
CREATE OPERATOR / (
PROCEDURE =3D cash_div_cash,
LEFTARG =3D money,
RIGHTARG =3D money
);
CREATE CAST (money AS numeric) WITH FUNCTION cash_numeric(money) AS ASSIGNM=
ENT;
Andy Balholm <andy@balholm.com> writes:
> I tried rewriting my function to use numeric, but I discovered that numeric division is not exact. (Otherwise SELECT
1::numeric/ 3::numeric would result in an infinite loop.) So I went back to my float8 version and wrote a cast from
moneyto numeric.
That's hardly an improvement if you're concerned about lack of
exactness.
regards, tom lane
> That's hardly an improvement if you're concerned about lack of > exactness. I know; I lose a couple of digits by using float8 instead of numeric, but i= t's much simpler and faster, and if it returned numeric people would _think= _ it was exact. And if we have a cast to numeric, people who want those extra digits can ca= st to numeric before dividing. But I do still have the numeric code that I tried, so if that's how people = want to do it, I can provide it.
Andy Balholm <andy@balholm.com> wrote: >> That's hardly an improvement if you're concerned about lack of >> exactness. > > I know; I lose a couple of digits by using float8 instead of > numeric, but it's much simpler and faster It also has the advantage of being symmetrical with the other operators. > and if it returned numeric people would _think_ it was exact. Well, I don't know how many people would expect an *exact* decimal representation of dividing a number by three. The case which had me concerned was specifically division by one as a "back door" cast. With numeric we could guarantee *that* was exact. > And if we have a cast to numeric, people who want those extra > digits can cast to numeric before dividing. And nobody has much reason to do the divide-by-one trick. > But I do still have the numeric code that I tried, so if that's > how people want to do it, I can provide it. I'm inclined to think it's better to have an explicit cast from money to numeric, as long as it is exact, and leave the division of money by money as float8. It does sort of beg the question of whether we should support a cast back in the other direction, though. I think that would wrap this all up in a tidy package. -Kevin
On Apr 1, 2010, at 7:57 AM, Kevin Grittner wrote:
> I'm inclined to think it's better to have an explicit cast from
> money to numeric, as long as it is exact, and leave the division of
> money by money as float8. It does sort of beg the question of
> whether we should support a cast back in the other direction,
> though. I think that would wrap this all up in a tidy package.
OK. Here is the whole thing in C:
#include <postgres.h>
#include <fmgr.h>
#include <utils/cash.h>
#include <utils/numeric.h>
#include <utils/pg_locale.h>
PG_MODULE_MAGIC;
extern Datum int8_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_div(PG_FUNCTION_ARGS);
extern Datum numeric_mul(PG_FUNCTION_ARGS);
extern Datum numeric_int8(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(cash_div_cash);
/* cash_div_cash()
* Divide cash by cash, returning float8.
*/
Datum
cash_div_cash(PG_FUNCTION_ARGS)
{
Cash dividend =3D PG_GETARG_CASH(0);
Cash divisor =3D PG_GETARG_CASH(1);
float8 quotient;
=20=20=20=20
if (divisor =3D=3D 0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
=20=20=20=20
quotient =3D (float8)dividend / (float8)divisor;
PG_RETURN_FLOAT8(quotient);
}
PG_FUNCTION_INFO_V1(cash_numeric);
/* cash_numeric()
* Convert cash to numeric.
*/
Datum
cash_numeric(PG_FUNCTION_ARGS)
{
Cash money =3D PG_GETARG_CASH(0);
int fpoint;
int64 scale;
int i;
Numeric result;
Datum amount;
Datum numeric_scale;
Datum one;
=20=20=20=20
struct lconv *lconvert =3D PGLC_localeconv();
=20=20=20=20
/*=20
* Find the number of digits after the decimal point.
* (These lines were copied from cash_in().)
*/
fpoint =3D lconvert->frac_digits;
if (fpoint < 0 || fpoint > 10)
fpoint =3D 2;
scale =3D 1;
for (i =3D 0; i < fpoint; i++)=20
scale *=3D 10;
=20=20=20=20
amount =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(mone=
y));
one =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(1));
numeric_scale =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(scal=
e));
numeric_scale =3D DirectFunctionCall2(&numeric_div, one, numeric_scale);
result =3D DatumGetNumeric(DirectFunctionCall2(&numeric_mul, amount, nu=
meric_scale));
=20=20=20=20
result->n_sign_dscale =3D NUMERIC_SIGN(result) | fpoint; /* Display the=
right number of decimal digits. */
=20=20=20=20
PG_RETURN_NUMERIC(result);
}
PG_FUNCTION_INFO_V1(numeric_cash);
/* numeric_cash()
* Convert numeric to cash.
*/
Datum
numeric_cash(PG_FUNCTION_ARGS)
{
Datum amount =3D PG_GETARG_DATUM(0);
Cash result;
int fpoint;
int64 scale;
int i;
Datum numeric_scale;
=20=20=20=20
struct lconv *lconvert =3D PGLC_localeconv();
=20=20=20=20
/*=20
* Find the number of digits after the decimal point.
*/
fpoint =3D lconvert->frac_digits;
if (fpoint < 0 || fpoint > 10)
fpoint =3D 2;
scale =3D 1;
for (i =3D 0; i < fpoint; i++)=20
scale *=3D 10;
=20=20=20=20
numeric_scale =3D DirectFunctionCall1(&int8_numeric, Int64GetDatum(scal=
e));
amount =3D DirectFunctionCall2(&numeric_mul, amount, numeric_scale);
amount =3D DirectFunctionCall1(&numeric_int8, amount);
=20=20=20=20
result =3D DatumGetInt64(amount);
PG_RETURN_CASH(result);
}
---------------------------------------------------------------------------=
-----------
And the SQL to load it:
CREATE FUNCTION cash_div_cash(money, money) RETURNS double precision
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_div_cash';
CREATE FUNCTION cash_numeric(money) RETURNS numeric
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'cash_numeric';
CREATE FUNCTION numeric_cash(numeric) RETURNS money
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/divide_money', 'numeric_cash';
CREATE CAST (money AS numeric) WITH FUNCTION public.cash_numeric(money) AS =
ASSIGNMENT;
CREATE CAST (numeric AS money) WITH FUNCTION public.numeric_cash(numeric) A=
S ASSIGNMENT;
CREATE OPERATOR / (
PROCEDURE =3D cash_div_cash,
LEFTARG =3D money,
RIGHTARG =3D money
);
Andy Balholm <andy@balholm.com> wrote: > OK. Here is the whole thing in C Cool! I'll take it for a spin when I get a little time. I guess there's not much point adding that TODO item now. ;-) -Kevin
Chris Travers <chris@metatrontech.com> writes: > Ok. Here is my application: I write a multi-currency accounting > program backed by PostgreSQL. After 1.3 is released (2Q this year), > we expect to be doing a full redesign. > > What I am thinking about is having a custom data type, something like: I'd be interested into participating into such a project, in particular a multi-currencies support type coded in C would be useful for me. > CREATE DOMAIN curr VARCHAR(3); > CREATE TYPE monetary AS (amount NUMERIC, currency CURR, multiplier > NUMERIC); This reduces into two basic components: a value (amount * > multiplier) and a currency identifier (USD, etc). > > One could also then store monetary[] arrays for addressing specific > denomination storage. I.e. "When closing the till we had 26 pennies, > 53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5 > $20 bills." > > Then we can allow NUMERIC arithmetic on monetary amounts provided that > the CURR field is the same. We could also store things like the cash > counted from a till at the end of the day by denomination. One could > have easy monetary::numeric casts as well. Sounds a good starting point, but it sounds like we'll have to think about it to see how it survive a more detailed approach. > Anyway, that's my basic thinking. One could further add currency > conversion tables to an application if necessary. That's where it become interesting. Finding a nice way to solve the problem of more than one currency in the same table, with dated (timestamped?) conversion rates that are possibly unknown at INSERT time=E2=80=A6 --=20 dim
On Fri, Apr 2, 2010 at 9:51 AM, Dimitri Fontaine <dfontaine@hi-media.com> w= rote: >> One could also then store monetary[] arrays for addressing specific >> denomination storage. =A0I.e. "When closing the till we had 26 pennies, >> 53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5 >> $20 bills." >> >> Then we can allow NUMERIC arithmetic on monetary amounts provided that >> the CURR field is the same. =A0We could also store things like the cash >> counted from a till at the end of the day by denomination. =A0One could >> have easy monetary::numeric casts as well. > > Sounds a good starting point, but it sounds like we'll have to think > about it to see how it survive a more detailed approach. Sure. See below. > >> Anyway, that's my basic thinking. =A0One could further add currency >> conversion tables to an application if necessary. > > That's where it become interesting. Finding a nice way to solve the > problem of more than one currency in the same table, with dated > (timestamped?) conversion rates that are possibly unknown at INSERT > time=85 Well, you have another problem (this may be wandering far afield from the original question but here it goes): Suppose I live in Canada and I have two checking accounts for my business, one in CAD and one in USD. In essence I have to account for a floating balance of a foreign currency. Consequently, I don't think you can just suggest "convert at insert time" as a way to handle that. In cases where you do (payments converted on deposit), that's a subset of the more general problem, which is converting at an arbitrary point in time. However, even where you do (suppose instead I live in the US and someone pays me in CAD), there is no guarantee that the conversion rate when you enter the payment into your system as received and when you convert it is the same. The check could be deposited the next day, for example and converted at that point. OTOH, if it is an incoming wire transfer in AUD, I would expect it to be converted on receipt. So conversion between currencies is something which has to be done at a specified point in time. While some of this could be automated to an extent, it would really be business-specific. In essence, I think you would need a function like convert_currency(source monetary, target curr, date) to do the conversion. Furthermore this would require currency tables, and would be probably outside the core data type definition. In essence, to handle exchange rates, I think you would need additional tables and the like, and UDF's to do the actual conversions. For simplicity's sake, I think this would be broken off into a separate module although I would be happy to collaborate on that as well.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, Apr 02, 2010 at 10:18:24AM -0700, Chris Travers wrote: > Suppose I live in Canada and I have two checking accounts for my > business, one in CAD and one in USD. In essence I have to account for > a floating balance of a foreign currency [...] > there is no guarantee that the conversion > rate when you enter the payment into your system as received and when > you convert it is the same. The check could be deposited the next > day, for example and converted at that point. It isn't even clear that the exchange rate for a given point in time is well-defined. Typically you get differing exchange rates depending on where (and how much!) you try to realize the conversion. > So conversion between currencies is something which has to be done at > a specified point in time. While some of this could be automated to > an extent, it would really be business-specific. > > In essence, I think you would need a function like > convert_currency(source monetary, target curr, date) to do the > conversion. Furthermore this would require currency tables, and would > be probably outside the core data type definition. > > In essence, to handle exchange rates, I think you would need > additional tables and the like, and UDF's to do the actual > conversions. For simplicity's sake, I think this would be broken off > into a separate module although I would be happy to collaborate on > that as well. Hm. An expert would have to decide whether such a simplification is useful (it could, e.g. help in estimating the money amount held in different currencies at some point in time) -- but some exchange rate seems to be well-defined only when you actually *do* the conversion. Regards - -- tomás -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFLtkjNBcgs9XrR2kYRAr++AJwKkDt6/RBbfK/KOz8ZNIK4RjYy6QCcCPPK S+gOzayBMJh+2n9sFgbTI64= =oeqv -----END PGP SIGNATURE-----
tomas@tuxteam.de writes:
> On Fri, Apr 02, 2010 at 10:18:24AM -0700, Chris Travers wrote:
>
>> Suppose I live in Canada and I have two checking accounts for my
>> business, one in CAD and one in USD. In essence I have to account for
>> a floating balance of a foreign currency [...]
>> there is no guarantee that the conversion
>> rate when you enter the payment into your system as received and when
>> you convert it is the same. The check could be deposited the next
>> day, for example and converted at that point.
>
> It isn't even clear that the exchange rate for a given point in time
> is well-defined. Typically you get differing exchange rates depending
> on where (and how much!) you try to realize the conversion.
Indeed.
You can only be certain of there actually being a conversion if the
transaction directly involved a conversion between currencies.
Thus...
1. If I buy materials using $USD on the $USD checking account, it's not
evident what conversion *ever* takes place for this transaction.
Expressing that transaction in $CDN will *always* reflect an
estimate, never a "reality."
2. In contrast, a funds transfer from the $CDN account to the $USD
account will indicate some kind of "spot rate" because there will be
two specific amounts:
- The amount of $CDN currency taken out of the one account, and
- The amount of $USD currency put into the other account.
You may or may not know both values immediately; as Chris Travers
observes, there may be some time separation.
It seems like an awfully bad idea to try to model this as if you
immediately know the exchange rate at the time the transaction is
recorded.
To the contrary, it seems to me that rate conversion shouldn't be
treated as being at all tightly integrated into this.
I actually have a similar situation to this, albeit not for business; I
have a $USD denominated account that earns interest.
I am expected to report on interest earnings on an annual basis by the
tax authorities. There tend to be three approaches considered readily
acceptable:
1. Use an annual average exchange rate (I presume it's a geometric
mean, but am not sure) on a total amount.
This is the easiest, and is what I do.
2. Use monthly average exchange rates, applying the appropriate
one to each month's earnings.
3. Use spot rates as reported by an authority, applying them to each
transaction. (For a bank account, this is actually pretty nearly
equivalent to #2, just with a different way of picking the
exchange rate!)
The fact that there are multiple policies like this points to the
conclusion that it's inappropriate to try to capture exchange rates as
something tightly coupled inside each transactions. It's something
you'd want to have the option to change later, because the reporting
policy could well change.
>> So conversion between currencies is something which has to be done at
>> a specified point in time. While some of this could be automated to
>> an extent, it would really be business-specific.
>>
>> In essence, I think you would need a function like
>> convert_currency(source monetary, target curr, date) to do the
>> conversion. Furthermore this would require currency tables, and
>> would be probably outside the core data type definition.
>>
>> In essence, to handle exchange rates, I think you would need
>> additional tables and the like, and UDF's to do the actual
>> conversions. For simplicity's sake, I think this would be broken off
>> into a separate module although I would be happy to collaborate on
>> that as well.
>
> Hm. An expert would have to decide whether such a simplification is
> useful (it could, e.g. help in estimating the money amount held in
> different currencies at some point in time) -- but some exchange rate
> seems to be well-defined only when you actually *do* the conversion.
My very little bit of exchange rate conversion takes place the day when
I'm working on my tax return :-).
There's some use in having a convenient way to capture conversion rates,
to help with the analysis, but it isn't necessarily tied to the
transactions themselves. And there's more information that likely needs
to get captured, such as whether the rate reflects:
- Actual conversion that took place (e.g. - as happens when you
transfer between your own accounts denominated in different
currencies)
- Spot rates published by financial institutions
- Average rates (for some form of "average", for some period of time),
as published.
There may be more kinds of conversion rates than those three, but I know
those three are of interest.
--
"The real romance is out ahead and yet to come. The computer
revolution hasn't started yet. Don't be misled by the enormous flow of
money into bad defacto standards for unsophisticated buyers using poor
adaptations of incomplete ideas." -- Alan Kay
Hi Chris, Many thanks for your comments. On Mon, Apr 5, 2010 at 10:16 AM, Chris Browne <cbbrowne@acm.org> wrote: > Indeed. > > You can only be certain of there actually being a conversion if the > transaction directly involved a conversion between currencies. > > Thus... > > =A01. If I buy materials using $USD on the $USD checking account, it's not > =A0 =A0evident what conversion *ever* takes place for this transaction. Right. That's a major problem with the way LSMB (and SQL-Ledger) currently handle this. There are plenty of other issues that come up here as well.... > > =A0 =A0Expressing that transaction in $CDN will *always* reflect an > =A0 =A0estimate, never a "reality." Furthermore, since there is no conversion, there isn't any realized fx gain or loss. In other words, any fx gain or loss is a mere estimate necessary for accounting reports. IMO, any estimated unrealized gains or losses should be dynamically calculated anyway. > > =A02. In contrast, a funds transfer from the $CDN account to the $USD > =A0 =A0account will indicate some kind of "spot rate" because there will = be > =A0 =A0two specific amounts: > > =A0 =A0- The amount of $CDN currency taken out of the one account, and > =A0 =A0- The amount of $USD currency put into the other account. > > =A0 =A0You may or may not know both values immediately; as Chris Travers > =A0 =A0observes, there may be some time separation. > > It seems like an awfully bad idea to try to model this as if you > immediately know the exchange rate at the time the transaction is > recorded. Thinking through this further, I have concluded that at least two ways are necessary: 1) Conversion of currencies as an intrinsic process at a known rate. I.e. if I transfer money from a USD to a CDN account, and I have the numbers and the rates, I tell it the appropriate rate. 2) Conversion of currencies as an extrinsic process at a discovered rate. I.e. if I am running an income statement for 2009, I look up rates for converting my totals from USD to CDN at the end points and calculate estimated, unrealized fx gains and losses on that basis. My initial reasoning was different, namely that data types shouldn't depend on database tables to be usable. However, this then squarely addresses the other concern. So I suppose that's a good thing :-) Obviously any extrinsic elements shouldn't be tightly coupled with the intrinsic elements (meaning you have monetary types with intrinsic operators and functions, and then you have a separate, optional business logic module which can provide those extrinsic elements along with tables to store the values). > > To the contrary, it seems to me that rate conversion shouldn't be > treated as being at all tightly integrated into this. Agreed. If you'd be interested in seeing the first draft of the spec I came up with, I would be happy to forward it along. A few things in it will need to be changed due to what you have noted regarding looked up exchange rates, but the basic type definitions and base functions seem solid. > > I actually have a similar situation to this, albeit not for business; I > have a $USD denominated account that earns interest. > > I am expected to report on interest earnings on an annual basis by the > tax authorities. =A0There tend to be three approaches considered readily > acceptable: > > =A01. =A0Use an annual average exchange rate (I presume it's a geometric > =A0 =A0 =A0mean, but am not sure) on a total amount. > > =A0 =A0 =A0This is the easiest, and is what I do. This is a published rate by the tax authorities? > > =A02. =A0Use monthly average exchange rates, applying the appropriate > =A0 =A0 =A0one to each month's earnings. Are these published as well by tax authorities? > > =A03. =A0Use spot rates as reported by an authority, applying them to each > =A0 =A0 =A0transaction. =A0(For a bank account, this is actually pretty n= early > =A0 =A0 =A0equivalent to #2, just with a different way of picking the > =A0 =A0 =A0exchange rate!) > > The fact that there are multiple policies like this points to the > conclusion that it's inappropriate to try to capture exchange rates as > something tightly coupled inside each transactions. =A0It's something > you'd want to have the option to change later, because the reporting > policy could well change. Right. There's also the following issue: 1) I send an invoice in CDN 2) 1 month later that invoice is paid in CDN. 3) I have to report relavant fx gains and losses. The only way I can see of doing this is to look up an accepted rate as of date of invoice, convert that, and then use the spot rate on the conversion of the payment. So one has (in this case) a realized gain or loss which is in part estimated (and extrinsic to the "conversion" which isn't "real) and in part known (and intrinsic to a real conversion). If conversions at specified rates are seen as a part of the core module, then any lookup logic can be neatly uncoupled :-). >> Hm. An expert would have to decide whether such a simplification is >> useful (it could, e.g. help in estimating the money amount held in >> different currencies at some point in time) -- but some exchange rate >> seems to be well-defined only when you actually *do* the conversion. > > My very little bit of exchange rate conversion takes place the day when > I'm working on my tax return :-). > > There's some use in having a convenient way to capture conversion rates, > to help with the analysis, but it isn't necessarily tied to the > transactions themselves. =A0And there's more information that likely needs > to get captured, such as whether the rate reflects: > > =A0- Actual conversion that took place (e.g. - as happens when you > =A0 transfer between your own accounts denominated in different > =A0 currencies) > > =A0- Spot rates published by financial institutions > > =A0- Average rates (for some form of "average", for some period of time), > =A0 as published. > > There may be more kinds of conversion rates than those three, but I know > those three are of interest. Hmm... Back to the drawing board on that helper module :-). Best Wishes, Chris Travers
Chris Travers wrote: > Hmm... Back to the drawing board on that helper module :-). > like I said, its a big tarpit.
John R Pierce <pierce@hogranch.com> wrote: > Chris Travers wrote: >> Hmm... Back to the drawing board on that helper module :-). > > like I said, its a big tarpit. If you start up on this again, you might want to start a new thread with a more descriptive subject. Those who weren't interested in Andy's issue might not be following your discussion. The proposed new types and conversion capabilities really have nothing to do with the original topic, which makes it kinda confusing. -Kevin
On Mon, Apr 5, 2010 at 4:37 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > John R Pierce <pierce@hogranch.com> wrote: >> Chris Travers wrote: >>> Hmm... Back to the drawing board on that helper module :-). >> >> like I said, its a big tarpit. > > If you start up on this again, you might want to start a new thread > with a more descriptive subject. =A0Those who weren't interested in > Andy's issue might not be following your discussion. =A0The proposed > new types and conversion capabilities =A0really have nothing to do > with the original topic, which makes it kinda confusing. Yeah - it should probably be on -hackers, too, not here. ...Robert