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