[yafetn]

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

[SideburnsOfDoom]

> — Use TIMESTAMPTZ. Always.

When you’re using JSON to pass around datetime data, Use ISO8601 date and time with offset info, always.

e.g. "transactionDate": "2023‐06‐28T15:55:22.511Z"

[fatnoah]

> Use ISO8601 date and time with offset info, always.

I think that's just sufficient for all use cases everywhere. I've been a software engineer for almost 25 years and, of all the universal truths I've encountered, implicit and unintended changes to datetime offset are the ones I've seen at every single job.

[Terr_]

> I think [ISO8601 date and time with offset info is] sufficient for all use cases everywhere.

TLDR: Also, which timezone is used (not quite the same as offset) really does matter--UTC is great but you can't use it everywhere.

________

One of my favorite simple examples of this "here be dragons" for the new developer: Any system that schedules a future calendar-event.

Such events are typically pegged, implicitly if not explicitly, to a particular timezone or geographic context. For example: "The company's Virtual Summit will occur on November 2nd at 1PM Elbonian Xtremesunshine Time, hosted out of our central Elbonian headquarters."

In that scenario, it is impossible to know for sure how many seconds-from-now it will happen until the moment actually happens! "2023-11-02 13:00:00 EXT" is actually a contract or spec for recognizing a future condition, one that will shift if/when the relevant nation/province/city simply declares their clocks shall be set differently.

So if the Elbonian government alters their daylight-savings switchover to occur earlier on 11-01 instead of 11-06, then the summit just moved. Even if you scheduled everything UTC all along... Well, now the summit is overlapping lunchtime for everyone in Elbonia, so it moved from their perspective.

[SideburnsOfDoom]

Absolutely, e.. an offset of +2:00 in e.g. 2023‐06‐28T18:00+02:00 could mean Berlin in the summer (Central European Summer Time, clocks will change) or in Johannesburg (South African Standard Time, not summer and clocks don't change). Same offset, different time zone, different clock change rules.

As you note, for some uses this _does_ make a difference and tracking which one you have can in these cases be important.

[fatnoah]

> TLDR: Also, which timezone is used

Good catch!

[prottog]

> Elbonian Xtremesunshine Time

Ha, good one! One can only hope the Elbonian Parliament will have the sense to abolish this stupid Xtremesunshine time and observe one time zone year-round.

[Terr_]

I took an embarrassing number of minutes trying to pick a plausible /E.T/ code not already in use elsewhere, then gave up.

[SideburnsOfDoom]

As I put in another comment, your class library might have a type that is equivalent to ISO 8601 data, indeed is convertible to and from it, but is a binary representation at runtime compatible with other types in the language.

So this technically isn't ISO8601, and certainly isn't "ISO8601 in a string", which is an _interchange format_ between application with potentially very different runtimes. I don't really recommend treating ISO8601 dates as mere strings, unless you intend to pass them through without even looking at the contents.

I refer to https://learn.microsoft.com/en-us/dotnet/api/system.datetime...

Use types that support ISO8601 with an offset, always.

The docs even say that, even if many are not aware:

> consider DateTimeOffset as the default date and time type for application development.

https://learn.microsoft.com/en-us/dotnet/standard/datetime/c...

[lightbendover]

Only if that offset info is 'Z' (IMHO).

[pg_1234]

"— 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.)."

I'd go a step further and prefix the strings with an ISO currency code ... to stop someone from just feeding it into their languages int to float converter and assuming that's ok. Only custom built (hopefully safe) converters will work.

[scrollaway]

Ergh, I get what you're trying to prevent, but this actively breaks those custom safe parsers we build, and now you have to do some additional active parsing. Please don't do this, just set contractual expectations in your API.

[hannofcart]

Politely, I think this comment may be incorrect advice.

I think correctness and precision requirements of financial transactions outweighs any devex concerns. Lots of banking and trading APIs do exactly this: pass your currency fields as strings.

[hirundo]

I'm generally not a fan of overloading fields, but we took the opportunity to add the currency code when we decided to encode monetary quantities in JSON as strings, e.g. "0.02USD". This has worked well, particularly because we use a money handling library that parses it unchanged.

[zeisss]

Do you always put the currency behind the number? I know some countries prefer the currency infront (e.g. USD), others behind (EUR).

But since this is not a human visible field, this is probably irrelevant, right?

[yafetn]

It’s probably fine, but this sort of thing is why transactionAmount and transactionCurrency are better when separated. Consider the case when you’re doing some reporting from your DB based on currency; do you really want to have to deal with one string that contains both or just do a simple WHERE clause?

[hirundo]

In our case those are indeed separate in the database, so that we can type the value as a numeric, which is very necessary for many operations. Those fields only get combined in the serialization.

[hirundo]

I think I looked at this when I laid it out:

https://ux.stackexchange.com/questions/9105/international-cu...

So we put the ISO code at the end and ignore whitespace. But including a prefix sign as with `€1.234.567,89 EUR` would break it.

[hermitdev]

You also included localized formatting (the '.' and ',' ) in your example that'll likely break on parsing without special handling. For interchange, you'll want to avoid using localized formats, e.g. stick to the "C" locale or something specific or agreed to between the involved parties.