Hacker News Reader

Comment by calrain | Hacker News Reader

When storing dates in a database I always store them in Unix Epoch time and I don't record the timezone information on the date field (it is stored separately if there was a requirement to know the timezone).

Should we instead be storing time stamps in TAI format, and then use functions to convert time to UTC as required, ensuring that any adjustments for planetary tweaks can be performed as required?

I know that timezones are a field of landmines, but again, that is a human construct where timezone boundaries are adjusted over time.

It seems we need to anchor on absolute time, and then render that out to whatever local time format we need, when required.

View on HN

> Should we instead be storing time stamps in TAI format, and then use functions to convert time to UTC as required, ensuring that any adjustments for planetary tweaks can be performed as required?

Yes. TAI or similar is the only sensible way to track "system" time, and a higher-level system should be responsible for converting it to human-facing times; leap second adjustment should happen there, in the same place as time zone conversion.

Unfortunately Unix standardised the wrong thing and migration is hard.

I wish there were a TAI timezone: just unmodified, unleaped, untimezoned seconds, forever, in both directions. I was surprised it doesn’t exist.

TAI is not a time zone. Timezones are a concept of civil time keeping, that is tied to the UTC time scale.

TAI is a separate time scale and it is used to define UTC.

There is now CLOCK_TAI in Linux [1], tai_clock [2] in c++ and of course several high level libraries in many languages (e.g. astropy.time in Python [3])

There are three things you want in a time scale: * Monotonically Increasing * Ticking with a fixed frequency, i.e. an integer multiple of the SI second * Aligned with the solar day

Unfortunately, as always, you can only chose 2 out of the 3.

TAI is 1 + 2, atomic clocks using the caesiun standard ticking at the frequency that is the definition of the SI second forever Increasing.

Then there is UT1, which is 1 + 3 (at least as long as no major disaster happens...). It is purely the orientation of the Earth, measured with radio telescopes.

UTC is 2 + 3, defined with the help of both. It ticks the SI seconds of TAI, but leap seconds are inserted at two possible time slots per year to keep it within 1 second of UT1. The last part is under discussion to be changed to a much longer time, practically eliminating future leap seconds.

The issue then is that POSIX chose the wrong standard for numerical system clocks. And now it is pretty hard to change and it can also be argued that for performance reasons, it shouldn't be changed, as you more often need the civil time than the monotonic time.

The remaining issues are:

* On many systems, it's simple to get TAI * Many software systems do not accept the complexity of this topic and instead just return the wrong answer using simplified assumptions, e.g. of no leap seconds in UTC * There is no standardized way to handle the leap seconds in the Unix time stamp, so on days around the introduction of leap second, the relationship between the Unix timestamp and the actual UTC or TAI time is not clear, several versions exist and that results in uncertainty up to two seconds. * There might be a negative leap second one day, and nothing is ready for it

[1] https://www.man7.org/linux/man-pages/man7/vdso.7.html [2] https://en.cppreference.com/w/cpp/chrono/tai_clock [3] https://docs.astropy.org/en/stable/time/index.html

> you more often need the civil time than the monotonic time

I don't think that's true? You need to time something at the system level (e.g. measure the duration of an operation, or run something at a regular interval) a lot more often than you need a user-facing time.

Thank you ; it’s kind of you to write such a thoughtful, thorough reply.

In my original comment, when I wrote timezone, I actually didn’t really mean one of many known civil timezones (because it’s not), but I meant “timezone string configuration in Linux that will then give TAI time, ie stop adjusting it with timezones, daylight savings, or leap seconds”.

I hadn’t heard of the concept of timescale.

Personally i think item (3) is worthless for computer (as opposed to human facing) timekeeping.

Your explanation is very educational, thank you.

That said, you say it’s simple to get TAI, but that’s within a programming language. What we need is a way to explicitly specify the meaning of a time (timezone but also timescale, I’m learning), and that that interpretation is stored together with the timestamp.

I still don’t understand why a TZ=TAI would be so unreasonable or hard to implement as a shorthand for this desire..

I’m thinking particularly of it being attractive for logfiles and other long term data with time info in it.

I did this for my systems a while ago. You can grab <https://imu.li/TAI.zone>, compile it with the tzdata tools, and stick it in /etc/zoneinfo. It is unfortunately unable to keep time during a leap second.

In theory, if you keep your clock set to TAI instead of UTC, you can use the /etc/zoneinfo/right timezones for civic time and make a (simpler) TAI zone file. I learned of that after I'd created the above though, and I can imagine all sorts of problems with getting the NTP daemon to do the right thing, and my use case was more TZ=TAI date, as you mentioned.

There's a contentious discussion on the time zone mailing list about adding a TAI entry. It really didn't help that DJB was the one wanting to add it and approached the issue with his customary attitude. There's a lot of interesting stuff in there though - like allegedly there's a legal requirement in Germany for their time zone to be fixed to the rotation of the earth (and so they might abandon UTC if it gives up leap seconds).

Sorry, there is a "not" missing there.

A remaining issue is that it is not easy to get proper TAI on most systems.

Why do you think a time scale has to be aligned with solar day? Are you an astronomer or come from an astronomy adjacent background?

Of all the definitions and hidden assumptions about time we’re talking about, possibly the oldest one is that the sun is highest at noon.

That's already false except along one line within every timezone (and that's assuming the timezone is properly set and not a convenient political or historical fiction). Let's say your timezone is perfectly positioned, and "true" in the middle. Along its east and west boundaries, local noon is 30 minutes off. Near daylight savings transitions, it's off by about an hour everywhere.

Local noon just doesn't matter that much. It especially doesn't matter to the second.

True, exactly “noon” hasn’t had a solar definition for a while. But whatever time the sun is highest for you, I imagine you still expect that to happen at the same time every day.

The first clock precise enough to even measure the irregularity of Earth rotation was only build in 1934.

Before, it was simply the best clock available.

No, almost often no. Most software is written to paper over leap seconds: it really only happens at the clock synchronization level (chrony for example implements leap second smearing).

All your cocks are therefore synchronized to UTC anyway: it would mean you’d have to translate from UTC to TAI when you store things, then undo when you retrieve. It would be a mess.

Smearing is alluring as a concept right up until you try and implement it in the real world.

If you control all the computers that all your other computers talk to (and also their time sync sources), then smearing works great. You're effectively investing your own standard to make Unix time monatomic.

If, however, your computers need to talk to someone else's computers and have some sort of consensus about what time it is, then the chances are your smearing policy won't match theirs, and you'll disagree on _what time it is_.

Sometimes these effects are harmless. Sometimes they're unforseen. If mysterious, infrequent buggy behaviour is your kink, then go for it!

Using time to sync between computers is one of the classic distributed systems problems. It is explicitly recommended against. The amount of errors in the regular time stack mean that you can’t really rely on time being accurate, regardless of leap seconds.

Computer clock speeds are not really that consistent, so “dead reckoning” style approaches don’t work.

NTP can only really sync to ~millisecond precision at best. I’m not aware of the state-of-the-art, but NTP errors and smearing errors in the worst case are probably quite similar. If you need more precise synchronisation, you need to implement it differently.

If you want 2 different computers to have the same time, you either have to solve it at a higher layer up by introducing an ordering to events (or equivalent) or use something like atomic clocks.

Fair, it's often one of those hidden, implicit design assumptions.

Google explicitly built spanner (?) around the idea that you can get distributed consistency and availability iff you control All The Clocks.

Smearing is fine, as long as it's interaction with other systems is thought about (and tested!). Nobody wants a surprise (yet actually inevitable) outage at midnight on New year's day.

In practice with GPS clocks and OTP you can get very good precision in the microseconds

Throw in chrony and you can get nanoseconds.

That's quite the typo

Close to the poles, I'd say the assumption that the cocks be synchronised with UTC is flawed. Had we had cocks, I am afraid they'd be oversleeping at this time of year.

  > and I don't record the timezone information on the date field

Very few databases actually make it possible to preserve timezone in a timestamp column. Typically the db either has no concept of time zone for stored timestamps (e.g. SQL server) or has “time zone aware” timestamp column types where the input is converted to UTC and the original zone discarded (MySQL, Postgres)

Oracle is the only DB I’m aware of that can actually round-trip nonlocal zones in its “with time zone” type.

As always the Postgres docs give an excellent explanation of why this is the case: https://www.postgresql.org/docs/current/datatype-datetime.ht...

I read it but I only see an explanation about what it does, not the why. It could have stored the original timezone.

What's "original timezone"? Most libraries implement timezone aware dates as an offset from UTC internally. What tzinfo uses oracle? Is it updated? Is it similar to tzinfo used in your service?

It's highly complicated topic and it's amazing PostgreSQL decided to use instant time for 'datetime with timezone' type instead of Oracle mess.

> Most libraries implement timezone aware dates as an offset from UTC internally.

For what it's worth, the libraries that are generally considered "good" (e.g. java.time, Nodatime, Temporal) all offer a "zoned datetime" type which stores an IANA identifier (and maybe an offset, but it's only meant for disambiguation w.r.t. transitions). Postgres already ships tzinfo and works with those identifiers, it just expects you to manage them more manually (e.g. in a separate column or composite type). Also let's not pretend that "timestamp with time zone" isn't a huge misnomer that causes confusion when it refers to a simple instant.

I suspect you might be part of the contingent that considers such a combined type a fundamentally bad idea, however: https://errorprone.info/docs/time#zoned_datetime

I agree naming is kinda awful. But you need geo timezone only for rare cases and handling it in a separate column is not that hard. Instant time is the right thing for almost all cases beginners want to use `datetime with timezone` for.

The discussion was about storing a timestamp as UTC, plus the timezone the time was in originally as a second field.

Postgres has timezone aware datetime fields, that translate incoming times to UTC, and outgoing to a configured timezone. So it doesnt store what timezone the time was in originally.

The claim was that the docs explain why not, but they don't.

Maybe, it really depends on what your systems are storing. Most systems really won't care if you are one second off every few years. For some calculations being a second off is a big deal. I think you should tread carefully when adopting any format that isn't the most popular and have valid reasons for deviating from the norm. The simple act of being different can be expensive.

Use your database native date-time field.

Seconded. Don't mess around with raw timestamps. If you're using a database, use its date-time data type and functions. They will be much more likely to handle numerous edge cases you've never even thought about.

Reply on news.ycombinator.com