21–30 of 112 posts
— If you’re using JSON to pass around monetary quantities (eg. from the frontend to the backend), put them in strings as opposed to the native number type. You never know what the serializers and deserializers across languages will do to your numbers (round them, truncate them etc.).
— Start recording the currency of the transactions as early as possible. It can be a separate column in your table.
— Use TIMESTAMPTZ. Always.
One more: Timezones Various platforms use: UTC, the user’s timezone as set in their dashboard, the user’s detected timezone, the timezone of the server that is generating the reports. Aggregating or reconciling data from different platforms can be a pain if the timezones aren’t clearly indicated. I’ve had bank statements that didn’t agree to CSV exports of the same data because the servers generating the two reports…
The transactions were in the late evening on New Year's Eve, central US time, but that was already the next year by UTC, so any readout of "transactions for 2022" would not include them.
[1] e.g. https://www.irs.gov/taxtopics/tc301
This is a great little article, and I can't emphasize what a big deal #2 is.
Others (like me) will cry because we know of multibillion-dollar fintechs that still struggle with this.
The one I'm thinking of didn't even have cents for a long time. After a pretty heroic migration effort they added cents. And they did it properly. But within weeks folks were using floats all over the place for money, leading to flaky tests and all kinds of other errors.
You can absolutely use doubles for money. Excel does it and so do many other financial tools. There are pros and cons but as long as you do rounding and comparisons correctly it works perfectly fine.
In EU prices to customers are required to be comprehensive of VAT, so a price is € 60,00 included VAT 10%.
But in an invoice/receipt you have to explicit how much is the net and how much is the tax, so 60 / 1.10 = 54.55 and VAT is 54.55 x 0.10 = 5.46 which makes a nice 60.01.
You may be tempted to round down the 60 / 1.10 = 54.54 and have VAT 54.54 x 0.10 = 5.45 but this makes 59.99.
I’d also add: — If you’re using JSON to pass around monetary quantities (eg. from the frontend to the backend), put them in strings as opposed to the native number type. You never know what the serializers and deserializers across languages will do to your numbers (round them, truncate them etc.). — Start recording the currency of the transactions as early as possible. It can be a separate column in your table. — Use…
By which time hopefully interest bearing CBDCs will show up, and make all these mindless intermediaries sitting between my wallet and someone elses wallet obsolete.
You can absolutely use doubles for money. Excel does it and so do many other financial tools. There are pros and cons but as long as you do rounding and comparisons correctly it works perfectly fine.
0.1 + 0.2 Earlier quoted context omitted.
If Excel uses doubles for money, it should be a warning sign. It uses simple numeric data type (I guess just ints) for freaking dates.. I can trace at least a couple of bugs in my career to just that fact.
Apart from its quirks Excel is fine, if you know its limitations. I think the real warning sign would be an analyst/programmer working with Excel and expecting high precision results.
This is a great little article, and I can't emphasize what a big deal #2 is.
Some folks will laugh at this advice. Others (like me) will cry because we know of multibillion-dollar fintechs that still struggle with this. The one I'm thinking of didn't even have cents for a long time. After a pretty heroic migration effort they added cents. And they did it properly. But within weeks folks were using floats all over the place for money, leading to flaky tests and all kinds of other errors.