Calendar dates, timezones, and the 31st of March

There are two kinds of time in a compliance register and they are not interchangeable. Confusing them produces bugs that are invisible to whoever wrote the code and constant for somebody three timezones away.

Instants and calendar dates

An instant is a moment that happened: this row was updated, this notification was delivered, this reading was taken. Everyone in the world agrees on when it was, and it is stored with a timezone.

A calendar date is a label on a day: this permit expires on 31 March. There is no instant attached. It is not midnight, it is not 23:59, and converting it to one is where the trouble starts.

-- An instant: when something happened.
delivered_at  timestamptz not null,

-- A calendar date: a label on a day, no timezone.
expires_on    date not null,

The off-by-one that only some people see

The usual bug is storing the expiry as a timestamp at midnight in the server's timezone. Render that for a user further west and it becomes the evening of the 30th; the certificate appears to expire a day early, and any count of what expires this month is wrong at both ends of the range.

It survives testing because the developer, the test fixtures and the CI runner are all in the same zone, often UTC. It reaches production and is reported as the alert came a day early, which sounds like a scheduling problem and is not.

• Store calendar dates as a date. Never as a timestamp you intend to truncate.
• Do not convert a date to an instant to compare it — compare dates to dates.
• Format dates without applying a timezone; a date has none to apply.
• In the browser, never build a date from a bare string and read the local day back out of it.

Which timezone is the organisation in?

Instants still need a zone for display and for deciding when a daily scan runs. The wrong answer is the server's. The next wrong answer is the viewing user's, which makes the same report differ between two colleagues.

The right answer is the site's. A permit belongs to a physical place, that place has a timezone, and a group with sites in three countries genuinely has three calendars. The scan therefore runs per site rather than once globally at a time that is convenient for head office.

A permit expiring on the 31st expires on the 31st where the equipment is. Any other answer is a timezone choice you made on the regulator's behalf.

The transitions nobody tests

Two edge cases are worth a test each, because they are the ones that fire at two in the morning twice a year. A daily job scheduled at a local wall-clock time can run twice or not at all on a daylight-saving transition — which is a second reason the notification ledger enforces uniqueness at the database rather than trusting the scheduler.

The other is month arithmetic. Adding one month to 31 January has no correct universal answer, and two libraries will disagree. Decide explicitly what a six-month interval from the 31st means in your domain, write it down, and test it — rather than inheriting whatever your date library happens to do.