16

I'm dealing with lots of different currencies in my application, and I want to know what the "best" way is to store them in an SQLite3 database.

I'm leaning towards a fixed-point representation (i.e. store them as integers, where $3.59 gets stored as 359, ¥400 stored as 40000). Is this a good idea? What if my input data later changes and requires more precision?

nornagon
  • 15,393
  • 18
  • 71
  • 85

5 Answers5

32

Given that SQLite 3 will use up to 8 bytes to store INTEGER types, unless you are going to have numbers greater than 10^16, you should be just fine.

To put this in perspective, the world gross domestic product expressed in thousandths of a USD (a mill) is about 61'000'000'000'000'000 which sqlite3 has no problem expressing.

sqlite> create table gdp (planet string, mills integer);
sqlite> insert into gdp (planet, mills) values ('earth', 61000000000000000000);
sqlite> select * from gdp;
earth|61000000000000000000

Unless you are handling interplanetary accounting, I don't think you have to worry.

msw
  • 42,753
  • 9
  • 87
  • 112
  • 23
    Well, to be completely accurate, this doesn't account for all eventualities: http://en.wikipedia.org/wiki/File%3AZimbabwe_%24100_trillion_2009_Obverse.jpg – msw Jul 16 '10 at 04:07
  • 1
    Here it is being assumed that fixed point decimals will have always the same number of decimal places, which might not be the case. Prices for small items might go up to 4 or 6 decimals, or might be defined by user (such as they are in almost every serious ERP -just to give a real life example-). – M.E. Aug 09 '19 at 00:40
  • For reference, by using 8 fraction digits (like bitcoin), the max representable signed Int64 is 92 billion = 92_233_720_368.54775807. If using 4 digits, 922 trillion. – geekley Mar 13 '20 at 00:16
3

In financial software currency is always represented as fixed-point (decimal). You can emulate it in SQLite using integers (64-bit integer holds up to 18 digits).

Piotr
  • 211
  • 3
  • 1
0

I'd say string/text. You can always convert it to whatever culture if you need to do that as well.

AlvinfromDiaspar
  • 6,611
  • 13
  • 75
  • 140
  • 7
    Storing numeric data as strings is silly, especially when there is a perfectly fine numeric type that is supporting the use case. – dr. Sybren May 03 '13 at 10:06
  • 1
    Storing fixed decimals as integers implies that you need to store somewhere else the number of decimal positions. So you need two fields, one for the integer and another for the number of decimal places (unless you are 100% sure that the number of decimal places is always the same and it is not going to change). So storing them as strings might not be that silly. – M.E. Aug 09 '19 at 00:37
-1

I see this post is long ago, but anybody noticed there is a money type out now for sqlite :D

MMMM
  • 3,320
  • 8
  • 43
  • 80
-3

SQLite has no BigDecimal field So I use the SQLite Integer type and convert like so:

BigDecimal bd = new BigDecimal("1234.5678");
int packedInt = bd.scaleByPowerOfTen(4).intValue(); // packedInt now = 12345678

Now save packedInt to SQLite Integer field.

To go back to BigDecimal:

BigDecimal bd = new BigDecimal(packedInt); // bd = 12345678
bd = bd.scaleByPowerOfTen(-4);             // now bd = 1234.5678
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
J.W.L
  • 43
  • 1
  • 2
    Please don't post [identical answers](http://stackoverflow.com/a/39626427/5292302) to multiple questions. Post one good answer, then vote/flag to close the other questions as duplicates. If the question is not a duplicate, *tailor your answers to the question.* – Petter Friberg Sep 21 '16 at 21:05