Thread: "money" binary representation
I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the value '$50.2'. I could not find description anywhere on how to convert the binary data into, for example, a double precision number.
Would you please help me find a method of converting binary "money" data into a double precision?
Thank you!
Konstantin Izmailov wrote: > I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). > The function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, > for the value '$50.2'. I could not find description anywhere on how to > convert the binary data into, for example, a double precision number. > > Would you please help me find a method of converting binary "money" > data into a double precision? > Its my understanding that MONEY is deprecated that you really should store money values as NUMERIC instead. a wild guess says thats some variant on NUMERIC, which is stored in base 10000 as a series of 16 bit integers, with a fuixed point fraction. why would you convert money to floating point? $0.10 in binary floating point is a repeating fraction which can't be represented exactly btw, are you sure your value isn't $51.20 ? 0x1400 is 5120 decimal.
Konstantin Izmailov <pgfizm@gmail.com> writes:
> I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The
> function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the
> value '$50.2'. I could not find description anywhere on how to convert the
> binary data into, for example, a double precision number.
> Would you please help me find a method of converting binary "money" data
> into a double precision?
It's a binary integer, either int4 or int8 depending on which PG version
you're working with, measured in pennies (or whatever the minimum unit
of your currency is). So that should correspond to 5020.
regards, tom lane
Right, the value is '$51.20'! Now I understand how to interpret the bytes - thank you!
I had to work with an existing database and I do not know why they still use "money" fields.
On Sun, Nov 15, 2009 at 9:38 PM, John R Pierce <pierce@hogranch.com> wrote:
Konstantin Izmailov wrote:Its my understanding that MONEY is deprecated that you really should store money values as NUMERIC instead.I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the value '$50.2'. I could not find description anywhere on how to convert the binary data into, for example, a double precision number.
Would you please help me find a method of converting binary "money" data into a double precision?
a wild guess says thats some variant on NUMERIC, which is stored in base 10000 as a series of 16 bit integers, with a fuixed point fraction.
why would you convert money to floating point? $0.10 in binary floating point is a repeating fraction which can't be represented exactly
btw, are you sure your value isn't $51.20 ? 0x1400 is 5120 decimal.
On 2009-11-16, Konstantin Izmailov <pgfizm@gmail.com> wrote: > --000e0cd5d09230ff7d04787526aa > Content-Type: text/plain; charset=ISO-8859-1 > > I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The > function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the > value '$50.2'. I could not find description anywhere on how to convert the > binary data into, for example, a double precision number. money is a 64 bit integer representing a number of cents, it appears to be big-endian byte order on ypur system it is deprecated and should not be used in new applications, use some sort of numeric instead. > Would you please help me find a method of converting binary "money" data > into a double precision? floating point is not recomended for financial calculations, but dividing the integer value by 100.0 should get you there.
On Mon, Nov 16, 2009 at 3:02 AM, Jasen Betts <jasen@xnet.co.nz> wrote: > On 2009-11-16, Konstantin Izmailov <pgfizm@gmail.com> wrote: >> --000e0cd5d09230ff7d04787526aa >> Content-Type: text/plain; charset=ISO-8859-1 >> >> I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The >> function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the >> value '$50.2'. I could not find description anywhere on how to convert the >> binary data into, for example, a double precision number. > > money is a 64 bit integer representing a number of cents, it appears > to be big-endian byte order on ypur system > > it is deprecated and should not be used in new applications, use some > sort of numeric instead. > >> Would you please help me find a method of converting binary "money" data >> into a double precision? > > floating point is not recomended for financial calculations, but > dividing the integer value by 100.0 should get you there. if you are reading/writing binary data to/from postgres, you should consider using libpqtypes: PGmoney m; res = PQexecf(conn, "INSERT INTO foo(m) values(%money) returning m", m); PQgetf(res, 0, "#money", "m", &m); /* # notation pulls field by name */ boy, this question sure seems to come up an awful lot! merlin
>>> I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The >>> function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the Huh... You mean 8 bytes, right? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/