Hacker News Reader

> Unix timestamp, or even integer based UTC datetimes work perfectly fine.

For serious work, it’s worth noting that leap seconds are not representable in this format. Many financial applications can get away without representing leap seconds, but this is fundamentally a kludge. If you actually need to represent any point in time (according to UTC or any particular timezone), use a representation that actually represents what you want.

This is especially true for future times. If you mean “10 AM, August 1, 2025, New York time”, then represent that! Do not kludge it as an integer, do not use a different time zone as a proxy, do not use UTC, and don’t use a representation that contains the terms “standard” or “daylight”. If you do this wrong, you will eventually regret it. Bonus points for also being able to represent future leap seconds, I suppose, but I’ve never encountered any need to do that.

Keep in mind that those properly represented future times are not guaranteed to ever happen. Time zone definitions can and do change.

(And, since this is finance, you also get to deal with bank holidays, past, present, and future. Your legal department may have opinions about what should happen when a future date, that you thought wouldn’t be a bank holiday, turns out to be a bank holiday after all.)

(Also, since this is finance, sometimes people will want to translate a time into a date. This can be a nontrivial operation. The date associated with a timestamp may or may not matter for actual business purposes, and various parties may or may not agree about what date a certain time belongs to and may or may not implement their opinion correctly.)

There’s a difference between a point in physical time, and a coordinate on a calendar. UTC works great for points in time, both past and future. So UTC is very appropriate for logging, including of financial transactions, where often what matters is the exact point in time something happens and not how that maps to someone’s calendar or the time zone on a server.

But UTC doesn’t work at all for a calendar app, where a calendar entry could for example span a calendar day, in some specific time zone, especially if it is in the future and there is not yet a mapping between that future date and UTC.

The mapping to someone’s calendar is important too, though, when you start looking at accruals…

And you need all the mapping rules for the past. Say, we got rid of daylight savings time starting next year. The year after that, what happens when you look at a time/date a decade before?

The IANA (“Olson”) database handles this.

That handles the time "now". Assume within 3 years, a country changed timezones 2 times. If you rely on TZDB, you can never be sure "What happened on May <year> 16.00?". It happened in Turkey and it was a bad time to be a sysadmin.

Is there any financial system, regime, or product that depends upon leap seconds? I have never heard of any. If so, it is safe to ignore them.

When storing date/time stamps, I prefer to persist into two separate fields: (a) date/time using UTC time zone and (b) original time zone offset, e.g., UTC+8. When debugging, it is useful to know the original time zone offset. And when querying and processing data, it is easier to use UTC time zone.

> Is there any financial system, regime, or product that depends upon leap seconds?

According to the standard for increasing leap seconds, they interact with the opening auction of the Tokyo Stock Exchange by delaying it one second.

Source for TSE? Cursory source shows nothing.

No, but the representation of new year 2029 00:00 in a UTC timestamp depends on how many leap seconds happen between epoch, 1970-01-01T00:00:00, and then. If you compute the timestamp for 2029-01-01T00:00:00 UTC now, and they add another leap seconds in 2025, then your timestamp represents instead 2028-31-12T23:59:59 UTC. Depending on your business this may matter or not. Differences based on time zone changes may be even larger.

That’s what the TIMESTAMPTZ type does in Postgres, albeit you have to read the offset and do the math. MySQL also allows you to specify an offset. Or for a fun kludge, store them as TIMESTAMP in MySQL (just not past 2038), and it silently casts it into UTC for storage, and then back to the server’s TZ – or whatever the connection specifies – for retrieval.

> If so, it is safe to ignore them.

if you say so, but don't whinge if someone pulls off a major heist during a leap second by counting on you to ignore it

https://www.imdb.com/title/tt0137494/

Timezone, not the offset, should generally be stored.

Offsets rotate throughout the year.

If you store only the offset, you can’t necessarily apply a delta to the time and localize it to the origin locale.

Only a guess, but maybe financial markets that run 23 hours a day, 6 days a week, such as futures?

That is exactly why I asked. Show me a source and I will believe it.

https://www.fia.org/fia/articles/fia-coordinates-industry-pr...

> "Leading up to the 2015 leap second event, there was concern within the financial industry that similar issues could lead to a disruption to trading operations across exchanges, clearinghouses, brokers, investors and key service providers. The Commodity Futures Trading Commission asked U.S. futures exchanges to provide details on how they planned to approach the leap second event. Some exchanges published plans to delay the opening of trading. Several exchanges in Asia have also announced details regarding how their systems will adjust their clocks before or after the leap second, and will retain normal trading hours."

> Is there any financial system, regime, or product that depends upon leap seconds? I have never heard of any.

They’re certainly unusual.

> If so, it is safe to ignore them.

Really? Leap seconds have happened, and they could plausibly happen again. If so, during those leap seconds, events will occur. Do you need to record the time of those events?

> When storing date/time stamps, I prefer to persist into two separate fields: (a) date/time using UTC time zone and (b) original time zone offset, e.g., UTC+8.

That covers most bases for past events. It is inappropriate for many future events.

Hmm, maybe a good system library would have separate types for timestamps that were actually observed (or interpolated or otherwise computed after the fact, etc) and for times of events that may not have occurred at the time of computation.

In Java, the original date/times were awful in the base library. Eventually, a very good replacement appeared called Joda-Time. Eventually, a "fix-all-the-design-flaws" 2.0 version was officially added to the base library as part of JSR-310. Excluding leap seconds (for which I have no practical need), I have never once found something that it cannot handle -- no matter how complex my conversion and calculation needs. A few other languages have basically ported Joda-Time or JSR-310 into their own. In 2024, date/time math feels like a hard, but solved problem with the correct libraries.

And last: I really like your example about Ruritania and the 48 cancellation policy. Sure, the country name is intended as a joke, but in the Real World all kinds of crazy things like this. Even Islamic calendars can be quite complex in 2024 -- knowing what days will be holidays in the future is a non-trivial task.

I like this answer as usually the world changes that cannot be easily ported to code makes software shit at the end.

I like to quip that many hard technical-problems are actually three business-problems in a trenchcoat.

In this case, the technical outgrowth might be heroic over-weekend crunch: Write some code that finds every affected timestamp in the DB, reconstructs the various code-chains that created them, divines an "original meaning", calculates the difference, and somehow apply changes in a way that doesn't make auditors angry or lock a key table for and hour or leads to data-corruption from race conditions...

If you have stored future times as timestamp in the database, then you also need to know with which version of the IANA timezone database those timestamps were computed. Fun ensues. Or one could use the correct data time format for the application use case. It depends on how much you value your sleep and weekends.

Also:

> Granularity of your financial amounts should ...

... comply with relevant regulations.

I worked on a gambling webapp a long time back - we were bound by government regulations on gambling licences to perform all calculations at a precision of one ten thousandth of a cent, and were only permitted to round to cents once at the very end just before displaying a dollar amount to a person.

(This suited me down to the ground because I pointed out to everybody that Javascript couldn't be guaranteed to treat numbers and calculations in a way that would keep the regulators auditors happy, and managed to push _every_ calculation involving dollar amounts off to the Java backend team.)

We also used integer UTC milliseconds for time, so we'd have good enough for the auditors timestamps showing who placed a valid bet at (say) 12:34:59.9997 and who else place an invalid bet at 12:35:00.0003 for a wager with a cut off time of 12:35. (We asked for and got a ruling from the regulators that the time that mattered was the time the API call was processed by the backend, since network latencies between the webapp and the backend platform were impossible to predict. I have no idea if the backend had millisecond accurate time, that wasn't my problem and I _so_ didn't want to have them make it my problem by asking the question.)

> This suited me down to the ground because I pointed out to everybody that Javascript couldn't be guaranteed to treat numbers and calculations in a way that would keep the regulators auditors happy,

Javascript can represent dollar values up to $2.25 billion at a resolution of ten-thousandths of a cent without any loss of precision.

I would want all money calculations done by the backend team as a matter of policy. But it's not a technical limitation.

While you are correct that it can _represent_ these numbers with that precision... it cannot operate on them and retain that precision, so DO NOT USE FLOATS TO REPRESENT MONEY. Thank you for your time.

    let a = 35
    let b = -34.99

    console.log(a+b)

    // output: 0.00999999999999801

Brr almost got the shivers until I read your comment

You wrote "a long time back", so maybe my point can be ignored.

Did you not consider to use decimal math? According to MDN, the largest int in Javascript is 2^53 – 1. If you steal four digits for your fraction, that still leaves a huge number. I will assume that no one was gambling more than one billion currency units, so you had plenty of space. Do all your calcs with ints, then divide by 10,000 for a final result.

Ref: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...

> Do all your calcs with ints

I suspect there's an important set of gotchas lurking in here around the difference between actually doing calculations with integers---which is not possible in Javascript--versus doing calculations with floating-points that happen to represent (safe) integers.

Luckily javascript has bigint now, so you can do this stuff safely in js now (just don't do it in the frontend ever, because you cannot trust the frontend).

> We asked for and got a ruling from the regulators that the time that mattered was the time the API call was processed by the backend

I haven't been in that situation, but I imagine I would reach for a "what if it was snail-mail" analogy. "It's dangerous to honor whatever time the bettor wrote by hand, the postmark-time may not be fair if the mailman got delayed because of a half-illegible address or random USPS delays... So how about we go for when it was actually delivered to us?"

I wonder if that's the sort of ruling that just got thrown out by the Supreme Court in favor of having courts decide.

> but you don't reeeeealy know how many seconds from now that will happen

So don't use a time-like object. Instead use a condition.

Honestly we should just abandon all that bullshit and just use TAI for internal representation. Would probably save billions.