News for hackers. Stuff that matters.
Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @09:19AM (#16358678)
Sadly the lack of a decimal type complicates monetary calculations sufficiently for me to stay with postgresql even for small projects that could easily be done with SQLite.
Re:Appropriate Uses for SQLite by asaph on Monday February 12, 2018 @09:33AM (#16358766)
Store your monetary values as cents instead of dollars and use integers. Convert to dollars in the presentation layer. My assumption here is that you're dealing in US currency but the same idea applies to any currency; use integers, not decimals.
Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @09:40AM (#16358807)
Say you have a savings account with an interest rate of 0.125% and any interest has to be converted into another currency for taxation purposes at a rate of 0.88619.

Working around these sorts of problems with integers is just not worth the trouble.

Re:Appropriate Uses for SQLite by Pamar on Monday February 12, 2018 @10:32AM (#16359220)
And yet the proper way to handle monetary values has always been to operate on integer values with a predetermined precision.

https://stackoverflow.com/questions/3730019/why-not-use-doub...

I can understand the convenience if you are setting up a simple online store, but more complicated cases (like the one in your example) absolutely need a proper handling of decimal values.

Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @10:59AM (#16359461)
Can you explain to me what particular cases postgresql's decimal type cannot handle properly _and_ conveniently?
Re:Appropriate Uses for SQLite by anarazel on Monday February 12, 2018 @11:27AM (#16359677)
> Can you explain to me what particular cases postgresql's decimal type cannot handle properly _and_ conveniently?

I entirely agree that numeric is a good solution for this case.

But to try to answer your question nevertheless: I'd say there is potential for storage density and math performance issues. You really need to have a number of numerics for each row, and a lot of rows to make storage density matter. If you do a lot of aggregates over numerics you definitely can notice the CPU overhead of them over say a plain float. But most of the time that doesn't matter.

Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @11:37AM (#16359754)
I agree completely.
Re:Appropriate Uses for SQLite by haimez on Monday February 12, 2018 @10:23AM (#16359145)
Yes it is. You convert to a big decimal representation, compute the tax/interest and then intentionally round back to an integer format using the appropriate rounding mode.
Re:Appropriate Uses for SQLite by ifdefdebug on Monday February 12, 2018 @10:34AM (#16359234)
GP didn't say it's impossible, he said it's not worth the trouble which is an opinion statement that can't be countered with "yes it is".
Re:Appropriate Uses for SQLite by haimez on Thursday February 15, 2018 @10:40PM (#16389683)
Yes, it can. The case and point is that you need to control where those moments of imprecision happen, and using fixed point a fixed point (often integer/long) representation is absolutely worth the effort because it's not much effort at all and you DEFINITELY care about when your money calculations involved rounding.
Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @11:02AM (#16359487)
And why would I not prefer to get help from a database system to do that?
Re:Appropriate Uses for SQLite by jenscow on Monday February 12, 2018 @11:58AM (#16359928)
If that's the only reason to use a "real" database, then what you have is a 100MB+ multi-process (and potentially costly) library to perform a few calculations.

(I don't disagree with you, just answering your question)

Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @01:03PM (#16360458)
Of course there are many other considerations, but there is a class of small finance related multi-tenancy systems for which monetary calculations are key and either a database server or a database library can potentially make sense.

Resource consumption isn't an issue at all. 100MB+ is less than loading an average news website in a browser and it costs almost nothing. The reason why I would have wanted to use SQLite sometimes is that it makes it easy to distribute and run the entire app on premises if needed. People are rightly concerned about losing access to web based software.

Re:Appropriate Uses for SQLite by haimez on Thursday February 15, 2018 @10:44PM (#16389696)
There are also also many use cases where you don't want to "store" something at all, just compute the value (eg: a shopping cart that is not yet a purchase), and you don't want to to either 1. duplicate implementations or 2. wait for a network request to your database to complete.
Re:Appropriate Uses for SQLite by dragonwriter on Monday February 12, 2018 @11:53AM (#16359885)
You could just store decimal strings and use application-language decimal functions to operate on them, even in queries, since it's fairly trivial to use application-language functions as SQLite (scalar or aggregate) database functions, at least in most decent SQLite drivers I've seen. It's one of the advantages of an in-process database.
Re:Appropriate Uses for SQLite by BareNakedCoder on Tuesday February 13, 2018 @01:20PM (#16369376)
Strings don't work well with numeric aggregate SQL functions like SUM or MAX. Also, won't work well with comparisons or sorting: "111.00" is less than "9.99" string-wise (unless you store all numerics with a bunch of leading "0"; but then that takes a lot more space).
Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @01:05PM (#16360475)
Interesting. I'll look into it next time it comes up. I wonder how it interacts with indexes and how it affects performance generally.
Re:Appropriate Uses for SQLite by BenjiWiebe on Monday February 12, 2018 @09:32AM (#16358763)
Why wouldn't you just store the value in cents? And divide by 100 to display it.
Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @09:43AM (#16358835)
As I said in my reply to asaph, it doesn't work well with interest rates and exchange rates. Also, it simply adds to the mental load in many places (e.g ad hoc queries)
Re:Appropriate Uses for SQLite by TeMPOraL on Monday February 12, 2018 @09:54AM (#16358919)
And floats do? With all the (implicit) rounding and precision issues, and some base-10 numbers not being representable in an exact form?

Serious question, what's the proper way to handle this case? I'm having hard time to believing it's IEEE-754 floats.

Re:Appropriate Uses for SQLite by aldanor on Monday February 12, 2018 @10:09AM (#16359036)
The proper way to handle this is fixed-point representation (whether the DB natively supports this or not). E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges. A typical scaling factor is 10^8, so if the exchange wants to tell you that the price is now 101.27, they send you an int64 value of 10127000000. You may then choose to take it further and store e.g. in 128-bit decimal.

Of course, there's an issue of how to handle mul/div (i.e., rounding/truncation), but that's a separate question -- at least you'd be making decisions yourself rather than relying on how IEEE-754 floats work.

Some DBs like PG, do, as a matter of fact, support decimals natively which makes things quite a bit simpler.

Re:Appropriate Uses for SQLite by KMag on Monday February 12, 2018 @01:13PM (#16360545)
Certainly for decimalized exchanges, binary floating point is the wrong choice. (On a side note, it's also the wrong choice for spread sheets, at least as the default. The average freshly graduated accountant can have a reasonable grasp on decimal floating point rounding after a 5 minute explanation, whereas most professional software engineers with a decade of experience have a very weak understanding of IEEE 754 binary floating point.)

> E.g., you won't find floating point fields in binary protocols for any of the major stock exchanges.

That's true of NASDAQ's OUCH, but some (many?) other protocols use decimal floating point.

NYSE's UTP Direct[0] (See section 4.15) Is not fixed point, but actually their own decimal floating point system with a 32-bit significand and single ASCII character exponent ("0" to "6", indicating negated base 10 exponent, not allowing "5").

FIX SBE uses an 8 byte significand and a 1 byte (power-of-10) exponent, if I remember correctly.

It's a bit unfortunate that (as far as I know) there aren't any major messaging formats supporting IEEE 754-2008 decimal64 values.

[0]https://www.nyse.com/publicdocs/nyse/markets/nyse/NYSEUTPDir...

Re:Appropriate Uses for SQLite by elteto on Monday February 12, 2018 @10:08AM (#16359028)
OP mentioned a decimal data type, which in postgres can have a very large precision: https://www.postgresql.org/docs/10/static/datatype-numeric.h.... Not the same as a floating point.
Re:Appropriate Uses for SQLite by anarazel on Monday February 12, 2018 @11:00AM (#16359463)
16383 digits ought to be enough for everyone:

"up to 131072 digits before the decimal point; up to 16383 digits after the decimal point"

Non jokingly: I think precision wise that's really plenty enough for pretty much any halfway realistic usecase (no, don't do public key crypto using SQL datatypes). There's some issues with the numeric / decimal type, but lack of potential precision isn't one of them.

Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @10:03AM (#16358985)
No, binary floating point doesn't work at all. Decimal floating point can work if the representation is large enough.
Re:Appropriate Uses for SQLite by icebraining on Monday February 12, 2018 @10:07AM (#16359020)
Arbitrary-precision numbers. DBs like Postgres support them, as do most languages (e.g. Ruby has BigDecimal, Python has Decimal).
Re:Appropriate Uses for SQLite by aeorgnoieang on Monday February 12, 2018 @10:34AM (#16359231)
Neither `BigDecimal` in Ruby or `Decimal` in Python are quite what I'd expect for "arbitrary-precision".

From the docs for [`BigDecimal`](http://ruby-doc.org/stdlib-1.9.3/libdoc/bigdecimal/rdoc/BigD...):

> Ruby provides built-in support for arbitrary precision integer arithmetic. For example: > > 4213 -> 1265437718438866624512 > > BigDecimal provides similar support for very large or very accurate floating point numbers.

My takeaway from the above is that, if arbitrary precision is needed, you should still just use integers. Tho, in practice, it may not be significant.

Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @11:34AM (#16359735)
Integers are not magic. If you want arbitrary precision on top of integers you're going to need arbitrarily long integers as well.
Re:Appropriate Uses for SQLite by aeorgnoieang on Monday February 12, 2018 @02:32PM (#16361300)
I was referring to Ruby and based on the first sentence I quoted:

> Ruby provides built-in support for arbitrary precision integer arithmetic.

[I'm also assuming that it's really arbitrary precision and not just 'really big but limited' precision.]

Re:Appropriate Uses for SQLite by dragonwriter on Monday February 12, 2018 @11:56AM (#16359908)
Serious monetary work often needs values smaller than cents. Exact decimals or, better, exact fractions are often what you want.
Re:Appropriate Uses for SQLite by lsaferite on Monday February 12, 2018 @03:21PM (#16361694)
What is the typical use-case of a unit smaller than the smallest tradable currency unit? Mind you, I understand there are smaller units than the circulated currency (For the USD you have a mill which is 1/10 of a cent).
Re:Appropriate Uses for SQLite by tracker1 on Tuesday February 13, 2018 @04:23AM (#16365810)
Bitcoin currency trades? Large fractions. Convert to/from yuan/yen, large whole numbers, back to US, somewhere in the middle.

Tracking transactional values for large stock and currency trades would be an example. That said, could use integers to represent arbitrary precision.

Re:Appropriate Uses for SQLite by hajile on Monday February 12, 2018 @10:59AM (#16359452)
The computer has only integers and floats for purposes of calculation (except for some IBM units implementing IEEE fixed point). In storage though, there are only whole numbers to which we assign meaning.

Store your as a single integer then write a small layer that converts that integer into decimal (that's all a language or DB does anyway). Alternatively, use two entries in the database for each side of the decimal if you want even more precision or store it as a string if you want arbitrary precision.

Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @11:16AM (#16359582)
>(that's all a language or DB does anyway)

Well, that's my point. Postgres does it for me. SQLite doesn't. I'm lazy.

Also, SQLite's largest integer type is 64 bits. That's awfully tight for some use cases.

Re:Appropriate Uses for SQLite by prewett on Monday February 12, 2018 @01:34PM (#16360746)
I'm curious how 64-bits would be tight for monetary data. A 64-bit integer is +/- 9e18, which is so big I don't even know how to actually say that number. Given that economies are only in the trillions, you have at least three orders of magnitude left when representing the largest amounts of money on the planet. Anything smaller would be a piece of cake.

Also, seems like the obvious thing to do to store money in an integer would be to store it in pennies. Or if the numbers get too big, do what corporate financial reports do and use thousands.

Re:Appropriate Uses for SQLite by qznc on Monday February 12, 2018 @04:39PM (#16362510)
Pennies (2 decimals) is not fine enough. Exchanges and other financial services use 4, 6, or maybe even 8 decimals. With 6 decimals, a few trillion is the limit.

If you implement multiplication and division with integer arithmetic, you need the additional decimals to keep the error small. See https://en.wikipedia.org/wiki/Salami_slicing

Re:Appropriate Uses for SQLite by eesmith on Monday February 12, 2018 @04:31PM (#16362426)
The largest Zimbabwean banknote during hyperinflation was Z$100,000,000,000,000 or 1E14. I assume they printed more than 10,000 of those.

I recall something about Oracle sending people down there to help with the bank or perhaps government databases deal with those numbers. I can't find confirmation of that, but https://en.wikipedia.org/wiki/Hyperinflation_in_Zimbabwe says "Computers could not handle the amount of zeros such that other forms of money had to be used to act as normal money (bearer’s cheques)."

Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @04:43PM (#16362545)
64 bits can be tight if you have to set aside, say, 7 digits for decimal places in order to perform a lot of divisions/multiplications by small numbers, like exchange rates, without rounding errors adding up.

But if you sacrifice 7 places you can no longer represent the market cap of Toyota Motor at the Tokyo stock exchange (24 trillion Yen) or the US GDP (~19 trillion USD).

So if you don't have a data type that can represent all of these quantities as well as the results of any calculations up to the required precision then your code will become a lot more complex and error prone.

Re:Appropriate Uses for SQLite by cdelsolar on Monday February 12, 2018 @11:38AM (#16359765)
import decimal; decimal.Decimal('3.14159')
Re:Appropriate Uses for SQLite by fauigerzigerk on Monday February 12, 2018 @11:58AM (#16359927)
A few words of explanation wouldn't hurt. Are you saying that I shouldn't ever do monetary calculations in the database at all? If so then I have to disagree.
Re:Appropriate Uses for SQLite by sigzero on Monday February 12, 2018 @04:23PM (#16362330)
No, they are giving ways to do it outside of the db. You really answered your own bias on this. You want the db to handle it, so you wouldn't use sqlite in the first place.
Re:Appropriate Uses for SQLite by tracker1 on Tuesday February 13, 2018 @04:19AM (#16365789)
Could look at Firebird if you want something embedded still. Can even scale to use a server mode. I wrote a distributed system about a decade and a half ago using Firebird, worked pretty well.