Storing money in MySQL (the right way) 

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!

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

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.

You're a very good teacher. I really like your way of teaching stuff. Keep the good work up.

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.

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.

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.

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.

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 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.

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.

This channel is a gem.

Bro, you save me from the trouble I would make in the future. Super thanks from Indonesia :>

I subscribed. Thank you! easy to understand and no bullshido. arigato gozaimasu

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!

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!

Completely agree, I ALWAYS use decimals for any financial value or anything needing accuracy

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.

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

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!

A great Part 2 of this video would be explaining a strategy on how to handle multiple concurrencies! Thanks for the video!

Thank you sooo much, i'm behind my deadline and discovered this issues, the solutions you've provided will do perfectly

my usual way of doing things, If want to calculate the value, use int decimal is relatively slower

I like this guy.

YourSQL videos are really useful 🙌

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.

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.

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

One exception to this rule is Oracle. Float and Double is stored in decimal representation

Woah i though of coming here and grab some of that money you stored inside those db. Going empty handed

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.

Great explanation

What is this code editor you are using? It looks very cool. Is it just something native to planetscale?

Thanks for the video, I really appreciate it 💯

Very Good Explanation. Keep it up.

What if you are dealing with bitcoins (BTC)?? which datatype would you use??

what are the use cases for floats and doubles, why not use decimals for everything?

I like to handle with cents, because i will not have problems with dynamic language

Wow! Thanks a lot

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

For money use integer or decimal, because that is optimal, float or double will get you in trouble.

Simple use decimal(10,3)

Wouldn't using an integer for cents ignore possible fractional cents?

I stay away from decimal because they usually come as strings from the database and they overflow very fast

What is the use case for using float or double? Is it to store numbers that would never be used in computations?

Always use decimal data type ingeneral

Interesting! I assume this is not unique to MySQL and this is also true for Postgres, SQL Server, etc?

Facts! ❤

Any maybe give a try to libraries like MoneyPHP and dinero.js...

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

I almost always use the decimal for everything with a decimal point

Great information, thank you! What is the interface you're using for writing those mySql queries?

Nice video. What is the tool you are using to run queries?

What app are you using to make the queries?

Good stuff

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...

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.

Are they glasses without lenses?

What if using it in varchar

So what is the use of float or double? When to use them

maybe other relates me that i often use string datatype for money.

How do you handle different types of currencies?

Can you add a schema or interface to a JSONB field?

What application are you using?

so float(10,2) does the same thing as decimal(10,2) i have always use float(10,2)

What's the tool you are using here? thanks!

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.

In a multi-currency system, the number of decimal places might need to be stored too.

What if you want to divide the money with some value?

At my last job, they stored currency as string. I don't want to talk about it.

This dude's Hot Take is trying not to have Hot Takes! How dare he, let's get him! C'mon Internet!

What about the money type? That’s a thing lol so it’s probably the better way to do things

What editor is this?

I always store my money as strings because they'll be exactly as I enter them :D

how else am i supposed to shave off the rounded bits then?

decimals! 😀

Use decimal.

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.

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)

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.

Imagine not having MONEY type

'Promo SM'

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 💾