Storing money in MySQL (the right way)
ฝัง
- เผยแพร่เมื่อ 20 มิ.ย. 2023
- 📚 Learn more about PlanetScale at planetscale.com/youtube.
------------------
01:15 Creating the money table
02:27 The problem with floating points
03:12 Remaking the table with decimals
04:14 Storing money as integers
05:12 Remaking the table with integers
------------------
💬 Follow PlanetScale on social media
• Twitter: / planetscaledata
• Discord: / discord
• TikTok: / planetscale
• Twitch: / planetscale
• LinkedIn: / planetscale - วิทยาศาสตร์และเทคโนโลยี
You are an excellent teacher. Knowing or being expertise on something is something but ability of teaching it very simple and alligent is beyond that. Great content, I learnt a new thing today!
That's incredibly kind of you. Thank you!
@@PlanetScale it's the reality. Thanks to your effort, PlanetScale is lucky to have you. Greeetings from Istanbul.
I also hope to see more videos explained by him. :)
There are comments saying "YOU SHOULD ALWAYS STORE MONETARY VALUES IN CENTS" - and it's also mentioned here in the video, but it depends on the situation. Just "Normal billing", yea, sure, that's fine. But I've worked on core banking applications, and in the financial industry cent precision is not really good enough, and we usually use a decimal precision of 4 or 6.
Like imagine you're doing Index Investing - you might have 0.01% of a specific stock that rises 1% that day. You only gain $0.009 on that specific stock, but you have 10 million of those kinda stocks in your portfolio. That's 90k you don't see. Then if you check your generated reporting every day, you'll get very weird spikes and dips when things wobble between $0.009 and $0.01
in that case store it as micro cents
Always still store it as an integer, otherwise you'll get unprecise, which is even worse in banking :)
You're a very good teacher. I really like your way of teaching stuff. Keep the good work up.
Thank you! 😃
I recommend the INT values if using multiple currencies. Some currencies like the Vietnamese Dong (haha), don't have "sub unit" values, as 1 VND equals 0.000042 USD. It then allows the application side to properly apply currency functions and formatting.
Totally! You can see that explanation at 04:52
Yeah I've used this when dealing with multiple currencies.. then you can use a scaling factor. Though dealing with conversions such as what you present here could be challenging since 0.000042 USD is not representable in cents.
Thank you sooo much, i'm behind my deadline and discovered this issues, the solutions you've provided will do perfectly
This happened to a colleague of mine. He was developing some kind of report, and the boss was giving him a hard time because his totals were wrong.
The colleague was a decent developer, but not very detail-oriented. And the boss was and ex-accountant.
They were unable to find the cause until I pointed it out.
This channel is a gem.
Excellent video. I'm experiencing a situation in my new job just like you described in the video. The old system, made in PHP, is used to store monetary values and has a lot of problems because the previous developer was using doubles. The system never calculates the values correctly, it always misses a few cents.
lol
php and floats? that's a big no-no. they're weirdly implemented and has different precision depending on cpu architecture. this comes from a background where i had to sum up how many hours someone worked, some division and stuff like that. it's horrible.
when dealing with multiple currencies, I've used the integer technique and you can a scaling factor like 100 for USD, or 1 for currencies that don't have cents.
I once worked on a project that uses DOUBLE for currency and it was very painful for the same reason you mentioned here.
Thank Aaron!
YourSQL videos are really useful 🙌
Working with cents as an integer is fine until you suddenly need exact fractions of cents for any reason. Exact decimal types are slower but you can always change the precision at any time in the database and even set it to infinite.
Just times all the values by 10 or 100 or whatever then, same thing for decimal.
@@zeteya what do you mean?
@@zeteya If you already have a running program that uses cents and suddenly need a few extra decimals you would have to rewrite a bunch of code and migrate all the data in database by multiplication and you have to do that every time you need to represent a smaller number which is just a waste of time and an unnecessary potential source of bugs. There are very few good reasons for not using a data type that is actually made for precise decimal representation.
@@pleggli Just don't change your database then, change your app logic, the decimal precision is there already.
@@zeteya if you use cents as an integer in your database you can never get higher precision than a cent. You need to use it both in the logic and in the database to be able to store and do calculations with and display it correctly to your customers.
Thanks for the video, I really appreciate it 💯
Very Good Explanation. Keep it up.
I worked at a company that had hundreds of thousands of dollars running through it every day and it was storying everything as floats. We had no idea how inaccurate our data was or how much money we lost or left on the table because of this error. What was crazier was that the devs there refused to acknowledge this was a problem and fix it.
Bro, you save me from the trouble I would make in the future. Super thanks from Indonesia :>
I wish I heard this advice like 8 years ago when I was first starting to work on a system that heavily does money-related calculations 😂 Some data already existed when I joined, but I might have been able to migrate the data at that point to use integers only. I can't count how many times we have had to format to 2 decimals on both the front-end and the back-end when doing calculations because our calculations would be off by one or more cents due to rounding errors 😂 And just now I had to do a report in MySQL only... It took me a little while to fix the rounding errors when multiplying numbers together to get the same results as we do on the front-end.
I think it is definitely much better to only work with integers, this is great advice!
Oh this pains me 😭 Sorry you had to deal with all that
@@PlanetScale thanks! With the healing power of time, looking for the missing few cents was kind of fun 😂
@@TheMoonWatcher Been there: It wasn't fun but it was a very valuable lesson in 'rounding errors' and how 'picky' Accountants are. ;-/ It was a valuable lesson.
I like this guy.
I agree in terms of Tables, but not always for Views where business logic may be dividing/distributing values. You don't want $100 / 3 to be $33.33 and only $99.99 when the three values are added back up in a report - unless you have an offshore account to put the extra cent in.
well... what should $100 / 3 be equal to instead...?
@@neehgurg2111generally, 33.33, 33.33, 33.34. The extra cent has to go somewhere.
Your business logic should handle such cases and become $33.33, $33.33 and $33.34. You shouldn't just blindly divide monetary values without accounting for any remainder.
😂
I subscribed. Thank you! easy to understand and no bullshido. arigato gozaimasu
my usual way of doing things, If want to calculate the value, use int
decimal is relatively slower
Great information, thank you!
What is the interface you're using for writing those mySql queries?
TablePlus!
A great Part 2 of this video would be explaining a strategy on how to handle multiple concurrencies! Thanks for the video!
I have dealing with this kind of data.
We usually stored the value as cents or just simply store the value times whatever decimal you want it as be (and of course you should save/store this decimal value either on your database or just make some config for this).
So for example there is $15 and I use 16 decimals, basically will store the data as 150000000000000000.
Wow! Thanks a lot
Great video. What do you personally prefer to use? I do love to use integers, as I have seen many big companies use this approach, like in your example Stripe
Personally (this is Aaron) I like to use decimals when working with USD only. If I was going to be storing multiple currencies, I'd probably go the Stripe route and store it as integers!
@@PlanetScale Why that differentiation?
@@Ropbastos i can do an educated guess on this one: it's simpler to calculate the currency exchange. there are currencies (like HUF) which are calculated as 100 HUF = 0.26 EUR, due to the fact that 1 HUF = 0.0026 EUR, so you'll have to constantly scale the decimal part constantly, i.e. from decimal(10,2) you'll have to alter to decimal(10,4), and so on, which may cause issues in some cases. this is also applies for Romania before the denomination of it's currency, when you had to multiply by a thousand every time.
Lots of financial math might deals with fraction cents, so using 3 decimals (*1000) is needed, or for some things like item prices even 4 or 5 decimals
Absolutely! Very dependent on the domain requirements
Great explanation
Completely agree, I ALWAYS use decimals for any financial value or anything needing accuracy
I would even say that you should also use integers for other units like for example kilograms. And instead store it in grams or even smaller unit if need more precision. It will be easier to convert it to imperial system if you ever need that. And also all the benefits of summing values etc.
I agree. I usually use cents ( € and $ ), then you just have to show in the unit that you want to the user on the frontend.
I've been building apps with financial values for years. I just had a case where I wanted to store "salary" and knew Aaron recorded something so had to get his opinion!
What is this code editor you are using? It looks very cool. Is it just something native to planetscale?
Nice video. What is the tool you are using to run queries?
TablePlus!
Facts! ❤
Good stuff
I want to take this one step further and say, YOU SHOULD ALWAYS STORE MONETARY VALUES IN CENTS, every company I know of, Stripe, Square and others, all store there values in cents in the backend, it is the most accurate and precise way to do calculations, especially when your doing currency conversions. EDIT: I wrote this comment before watching the end of the video.
I'm a newbie in database design. Does that mean I should use the integer method instead of the decimal one when dealing with the MONETARY values?
@@IamI16i think you should store it as integer. This however has to be decided when you design your application. Think about the smallest possible denomination too. If your smallest possible value is 1 cent, then simply converting monetary values to integer by multiplying it with 100 works fine. So $1 will be stored as 100 in the database. But if your application smallest denomination value can be half of a cent or 1/10 of a cent, then you might want to consider multiplying the monetary value by 1000, that is 1 = 0.1 cent. So a dollar will be stored as 1000 in the database.
@niz11 example show why you should NEVER store monetary values in cents. If your business considers that the precision should be limited to cents, yes it would work and would be even better. But if one day the business change, and you want more precision, what is your solution ?
Change that implict rules of what this integer represent ? Then you will need to go through a data migration, and code update.
And if your business goes international with currency using more precision, you must adapt you whole codebase / data structure for them (a customer would not like to see its balance on your app fluctuate with dollar FX rate, so convertion to dollar is not an option)
What if you are a store which sold a product priced at $ 100 in 3 equal instalments? Each would be registered as $ 33.33 which would sum up as $ 99.99 and not original $ 100. You would have to work this around too...
@@CarlosPaleodaRocha Any software you build should be able to either mark installments as $34, $33, $33 or choose to only charge up to the $99.99, most businesses would chose to forgo the 1 pence because it really isn't a big deal
I stay away from decimal because they usually come as strings from the database and they overflow very fast
It only worked with decimal because you have set digits [decimal(10,2)] as if you didn't [using decimal without parenthesis info] it would only return 20 for each field. Also, if you set them as double(10,2) you will get correct result too. We cannot say it works with decimal and didnt with double when not using the same approach on both. Nothing against storing in cents, but it also may get you in trouble. Lets say you sold your product in 3 equal instalments and your product is sold at $ 100. It would store 3 instalments of $ 33.33, which would sum up as $ 99.99 and not your original $ 100
Do you have a solution for the $99.9 problem? Other than checking if they have actually paid the full amount and asking for the remainder
Hello @@klicer3068 , no I don't. I believe it doesn't matter which data type you choose, you will always end up by checking for that. My point was exactly this, you will ever have to check it.
i store diff in first installment
What is the use case for using float or double? Is it to store numbers that would never be used in computations?
Great question, and truly... floating point numbers kind of blow my mind. You're right though, if you're doing computation and you need strict accuracy floating point won't work well. If you need huge ranges of values with varying precisions, then floating point is great. You could think of perhaps measurements, lat/lon (if you dont use point columns), or timings. Stuff like that might be a good case!
They're useful for physics problems. For probabilities. For statistics.
what are the use cases for floats and doubles, why not use decimals for everything?
What app are you using to make the queries?
Any maybe give a try to libraries like MoneyPHP and dinero.js...
Sad to admit that I know of one company whose software targets accountants and they store monetary values as DOUBLE and every now and then clients complain that the results of their queries are inexplicably rounding or approximating values.
Interesting! I assume this is not unique to MySQL and this is also true for Postgres, SQL Server, etc?
It applies to every database and every programming language
One exception to this rule is Oracle. Float and Double is stored in decimal representation
How do you handle different types of currencies?
You could use integers and store the lowest denomination as a whole number, and then store the currency in a second column.
Wouldn't using an integer for cents ignore possible fractional cents?
You'd have to decide what the lowest unit you want to store is and adjust the conversion math appropriately.
What if you are dealing with bitcoins (BTC)?? which datatype would you use??
What's the tool you are using here? thanks!
TablePlus!
So what is the use of float or double? When to use them
Float is for physics or math not currency. Where values are always approximate.
So if you need to store the probability it is going to rain, you can use floats or doubles. Or the estimated demand, or things like that which are approximations to begin with.
Though it is interesting to consider whether it's appropriate to use for an interest rate.
Can you add a schema or interface to a JSONB field?
You can actually
Soooo.. Decimal and Big Decimal, right? Right? :D
With integers you have to remember how places for "cents" to keep. Some currencies have 2, some have 3, there are currencies that have 0 decimal places. So you need additional value to know how many places you're using. Or to be safe you store 3 places, so you multiply by a 1000., so $20.55 becomes "20550". So you have floating point problem handled, what about rounding numbers? It gets tricky. Decimal type was designed for storing money in databases and I wouldn't use anything else.
Woah i though of coming here and grab some of that money you stored inside those db. Going empty handed
Ah shoot, sorry for the bamboozle. Check back next time!
What application are you using?
TablePlus!
so float(10,2) does the same thing as decimal(10,2) i have always use float(10,2)
check. but decimal seems more correct.
For money use integer or decimal, because that is optimal, float or double will get you in trouble.
I almost always use the decimal for everything with a decimal point
Are they glasses without lenses?
What if you want to divide the money with some value?
It all depends on what you want to do with fractions of a penny. You usually want to round in a way your accounting department tells you to.
Not only the accounting department, the laws of the country also dictate the rounding rules.
Now you can imagine how developing an international accounting software can become a nightmare. 😂
I'm in the IT field for decades now, and I still think floating point arithmetics are weird. They make sense for some special cases, but in general they are just WTF...
Honestly I still barely understand them.
I like to handle with cents, because i will not have problems with dynamic language
This problem is because of the conversion from decimal to binary and from binary to decimal. You can do this to any programing language 0.1 + 0.1 + 0.1 it's not equal to 0.3
What editor is this?
Table Plus
@@PlanetScaleThank you! Looks like a clean alternative to datagrip. Much appreciated.
In a multi-currency system, the number of decimal places might need to be stored too.
Good point!
maybe other relates me that i often use string datatype for money.
inefficient for massive computation.
What if using it in varchar
I wouldn't store money in a varchar! Tough to do math on at that point
Just a quick note to audience -- if you are building multi0currency app, be aware that min denomination in different currencies may not be expected 0.01.
Simple use decimal(10,3)
how else am i supposed to shave off the rounded bits then?
"I'm just talking about fractions of a penny here"
Always use decimal data type ingeneral
I always store my money as strings because they'll be exactly as I enter them :D
Hard to do math on them then!
Great, please send me 10bc0 dollars.
@@PlanetScale I usually store as XXX LOWEST_UNIT_INT e.g " USD100" represents $1.
Now in the Laravel side, there is cast so I create a cast class that will use the string to create a Money PHP object and when storing back, it will store it accordingly as STRING.
In the database side, I use CAST function on the column to convert to signed integer for that particular while using REPLACE to remove the currency code.
$columnCast = sprintf(
'CAST(REPLACE(%s, "%s", "") AS SIGNED)',
$column,
$currency->getCurrency(),
);
Instead of repeating that logic, I also created a "whereMoney" Eloquent Builder macro that can compare exact value, greater than, less than and range.
What about the money type? That’s a thing lol so it’s probably the better way to do things
Can you kindly point me to the money type in the MySQL documentation? If it exists I'm truly not aware of it.
At my last job, they stored currency as string. I don't want to talk about it.
decimals! 😀
Spoiler alert!
@@PlanetScale oops 😬
This dude's Hot Take is trying not to have Hot Takes! How dare he, let's get him! C'mon Internet!
oh no
Storing in int in the smallest denominator (cents, wei, etc) is something very common in blockchain apps. I thought it was only a crypto world thing and not a thing in other languages/environments tbh, this is very interesting to find out and see.
Imagine not having MONEY type
I don't have to imagine it, I live it every day!
He kept being passed the ball. The other 4 should make sure not to pass to the idiot. Passing to him is the same as throwing away the possession.
Use decimal.
Spoiler alert
@@PlanetScale:D
'Promo SM'
everything you say makes sense until you start working with cryptocurrency. integer cent values don't make sense for any cryptocurrency - 8 18 or sometimes even 256 decimal places are common. in this case you will run into limitations even with decimal. in this case i'd recommend using strings as a fallback or using a DB that supports this precision natively (postgres)
Oh yeah, I can't speak to crypto at all. Also isn't all that stored on the Blockchain? What's it doing in a MySQL database?
@@PlanetScale A blockchain is just a DB with weird write semantics :) Serious reply: most app specific backends need to mirror operations happening on blockchain, either to accumulate operations, enable efficient querying, store TX to broadcast.. the list goes on, there are many reasons to mirror a very small subset of a blockchain on a classic DB.
Let's not exaggerate things. Many real world systems use doubles for financial data and it's really just fine. Unless you're a bank where extreme precision is important, it won't matter. If at the end of the year some random sum is off by 5 cents, then nobody cares and it's not an issue (ask accounting!). Using integers is much more error prone and using decimals is unfortunately poorly supported by many programming languages, so sometimes doubles is an okay choice. Of course, YMMV.
Integers will fail in the case of cryptocurrencies like Bitcoin, which needs at least 6 decimal places to convert to pennies and may need more in the future.
Saved 💾