> If you want a point in time which will not "physically" change, store a datetime _with_ a timezone, always, preferably UTC (e.g. logging, timers, measuring the occurrence of events).

This works for immutably recording the current time into a log, yes.

For much else (e.g. a timer still-to-come that should go off in “1000 days”), leap seconds break this.

You could store such time using TAI as the timezone (TAI is UTC without the leap seconds), if RDBMSes actually persisted the timezone. But they don’t. They’ll just convert back to UTC at point of write.

I have a feeling that most people who really need to solve this problem end up using a (pos, len) column pair where `pos` is the current UTC time when the future-event was registered, and `len` is an interval representing how far away it is in monotonic time — either as a difference of POSIX timestamps at time of evaluation, or as a SQL INTERVAL, etc.

The trouble is that you can't really make safe assumptions about whether to use the "sticky" paradigm or the "point-in-time" paradigm.

Sticky really only makes sense in two scenarios:

1. when all participants are assumed to be in the same geographic/political time zone for the foreseeable future (in which case the only advantage over point-in-time timestamps is future political changes to that region's time zone, like DST changes), or

2. when there's some privileged participant such that everyone else can assume events follow that participant's time zone (e.g. a company headquarters that moves very rarely, or an individual's personal wakeup alarms which can probably be assumed to follow their current location's time zone as they travel).

If you have a group of friends who like to stay in touch with regular group calls, and all/most of them are digital nomads who change their time zone of residence multiple times per year, you probably don't want the sticky paradigm.

What’s the difference between these two other than how the client would convert to display to a user?