Re: Money type todos? - Mailing list pgsql-hackers

August Zajonc wrote:
> Agreed with Tom on this one. Full usage of money is beyond tagged types
> etc. For example, when you earn money in another currency, it is the
> time at which you earn it that describes its value. So for P&L accounts
> there is generally no change in exchange rates over time and you need to
> track what the rate was at time of earning. Solution is to date earnings
> and have a table of exchange rates by day.

Personally I think a true money type should hold the numeric value and 
optionally the currency (similar to the timestamp with timezone) and 
have support functions that handle the i/0 conversion (text - 
$US1,000.00 - to money) as happens now. As opposed to the db designer 
storing it in different columns.
But I think the data returned should be of purely numeric type unless a 
function is used to get pretty currency formatting or requesting the 
currency.

> For balance sheet accounts, their value at a given point in time in a
> home currency is of course dependent on exchange rates which creates the
> currency gain or loss on the P&L side, the account that captures
> exchange rate movements. But this is dependent on the relative
> differences between the rates when every dollar was earned and current
> rates.

Well the exchange rate at the time the payment is received is only the 
speculative (possible) value of a foreign currency and may not account 
for exchange fees either. This speculative value changes daily (hourly) 
and is not really relevant to the money amount recorded. The speculative 
value is only relevant at the time a report is run to show current value 
in a common currency.

If you have bank accounts in different countries then the exchange rate 
at the time of running, say a balance sheet, will give you the 
speculative value in a common currency of your foreign bank accounts.

The true value to you will only be realised when you transfer the 
foreign money to your local account and get $xx affecting your local 
account balance after exchange fees using the exchange rate on offer at 
the time you initiate the exchange.

> Darcy had suggested removing the currency symbol. That is a change I'd
> support. The only other nice thing would be user defined precision, but
> can live without that as most currencies work under nnn.mm. Speed is
> everything in these systems. For a complex general system you often can
> get away with integers if you define at the app layer the handling
> (including a lookup in system for format, type).

As I mentioned before I think the returned data should be pure numeric 
value unless requesting formatted data. I also agree with the precision 
setting, saving only ddd.cc is not universal enough for all 
applications. Some industries work with tenth's or hundredth's of a cent 
for their pricing and calculations and are only rounded to a whole cent 
on the final invoice when payment is due.

Not sure about America but here in Australia petrol is sold at xxx.x 
cents per litre with the total being rounded to a whole cent when 
payment is made. And our smallest coin is 5 cents so cash paying 
customers are also rounded to 5 cent increments.


-- 

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz