About Time

Temporal madness in Healthcare

This post is not about the movie, which I loved almost as much as Koreans did. It’s about how we represent time, which has quite as many plot holes. Likely more. And when it comes to computers ...

People who think that computer timestamps are simple, and that the solution is just to encode every time recorded as “ISO 8601 UTC timestamp with timezone offset” may not have explored particularly well. Let’s start simply.

Daylight saving

From the above graphic, you can work out how it’s easy to take a simple 100 mile drive and flick “backwards and forwards in time” many times. Yep Arizona doesn’t observe daylight saving time, either. A plot hole within a plot hole.

Daylight saving (DST) is one of the worst ideas ever imagined, and surely the worst bad idea ever to be enacted. The sole justification for DST is to make winter a bit more bearable—with long summer evenings. In my home country New Zealand, I also wouldn’t change it for anything. (Plot hole!)

DST wreaks havoc wherever it’s been implemented (especially on farms), and the rationale for implementing or indeed fiddling with it is often utterly crazy. Here are just a few further “plot holes”.

• Various people like Benjamin Franklin (1784) and New Zealand entomologist George Hudson (1895) suggested shifting the clock by an hour or two in spring, but these were either satirical or just not taken seriously.

• However in 1916 multiple nations decided that they might ‘save energy’ using DST. First cab off the Rank was German Sommerzeit (30 April 1916), together with the Austro-Hungarian empire, followed by the UK, France and others. For actual results, see below.

• When DST starts, we ‘spring’ forward, usually by an hour. In autumn, we ‘fall back’. But where computer records are based on wall time (the time of the wall clock) this means they alternately acquire gaps, or there’s a “groundhog hour” that is repeated. This knocks the hell out of badly designed anaesthetic and intensive care records, in particular.

• Morocco observes DST, but not during the fasting month of Ramadan, which is based on a lunar calendar, which varies annually. And in Iran, under Jaʿfarī law, DST was based on the first day of Farvardin and last day of Shahrivar—which depend on actually viewing the crescent moon. Pity if there’s wide, full cloud cover :( This differs a bit from other Islamic states.

• Date rules for changes are complex (We’ll get back to this later.)

• There’s even an Antarctic base that keeps Northern hemisphere DST.

Some have attributed health effects from alterations to circadian rhythms. A 2019 meta-analysis claims a small rise in heart attacks in the two weeks following the spring transition. A more recent JAMA study of 168,870 patients found no such effect.

Some South American countries have also rather arbitrarily introduced DST around the times of power crises! There’s remarkably scant evidence that DST makes any difference to energy consumption, by the way. Take UK coal use data for example. No sudden drop in 1916 or 1917:

A brief history of Time :)

In the past, most societies have been a bit less fixated than we are about time, and even dates. The Romans used to divide daylight into hours, which gave you a variable-duration hora. Julius Caesar kicked off all the date problems in 46 BCE with the ‘Julian calendar’, with a built-in error of 1 day in every 128 years.

Easter was a bugger, too. Technically, it’s the “first Sunday after the Paschal full moon”, which results in quite complex computations.¹ There were almost religious wars about this, especially as two calendars were being used for many hundreds of years—the ‘old style’ Julian calendar, and the modern Gregorian one, which gradually diverged over the centuries.

Which is why Sir Isaac Newton was born on both 25 December 1642 and 4 January 1643, depending on which calendar you use. Eventually, almost everyone transitioned from the Julian to Gregorian calendar.² When England did so, the 11-day discrepancy was eagerly seized upon by employers (who paid less at the end of the month “you worked fewer days”) and landlords (“rent is due”), purportedly resulting in the “English calendar riots of 1752”.³

Leaping about

Things become more exciting with leap intervals. We likely all know the rules for leap years—a year is a leap year if it’s divisible by 4, unless it’s divisible by 100, unless it’s divisible by 400—but what about leap seconds? The problem here is that leap seconds are (a) required to keep the time of day in synch, and (b) are completely unpredictable. So they get shoved in as required, depending on the rotational slowing of the Earth (it could even speed up).

These are tricky, and as with all good arguments, two implacable factions emerged. Sensible people tend to say things like “F-ck it, let’s just let the clock tick” (as with “International Atomic Time”); die-hard fanatics say things like “In 10,000 years, we may be out by simply ages, Old Chap!” (They tend to be British). If you’re on the other side, reverse the sensible/fanatic labels, of course.

There have been some embarrassing consequences. Computers pretty much all tick on UNIX time, which unfortunately requires introduction of leap seconds when they occur—either at the end of June, or the end of December. A day in UNIX time is always 86,400 seconds, so if there’s a leap second, well then, tough titty!

So far, all of the leap seconds have been positive (added) which results in a UNIX “groundhog second” where the same second is ticked twice. This is a little embarrassing if you have, for example, scheduled the bank payment to occur at midnight—and it then happens twice. In translating to wall time, you get times like 1998-12-31T23:59:60.⁴ In fact, the leap second on 30 June 2012 caused some major outages at Reddit, LinkedIn, Foursquare and Yelp. There were other fuckups later, too.

On 18 November 2022, the General Conference on Weights and Measures resolved to phase out leap seconds by 2035.⁵ But if you use GPS time, this just ticks like that already.⁶ So there!

Time zones

There was a young lady called Bright
Who could travel much faster than light.
She travelled one day
In a relative way
And came back the previous night.

Or she could have just flown from New Zealand to California.

The fun has just begun. The international reference point for time zones runs through Greenwich, giving us “Coordinated Universal Time” (UTC), and offsets that range from -12:00 to +14:00. This is made more amusing by fractional offsets of half or three quarters of an hour. For example, the Chatham Islands are UTC+12:45, and Lord Howe Island is +10:30 (with a DST increase of—wait for it—half an hour). India is +5:30, without DST.

Things have changed a lot over time, too. And sometimes the rules are even changed retroactively. This gets weird, for example Samoa switched sides across the international date line twice. The second time it did so, it lost the whole of 30 December 2011. Plot hole!

tz or not tz

You have two choices when it comes to looking up the rules. You can go with the international standard (the IANA time zone database,⁷ zones pictured above), as used everywhere UNIX and Linux are used, or you can trust Microsoft, I guess.⁸

Soo, tz. A sure recipe for insanity is to work through the rules encoded in tz. I know. I’ve done this.⁹ The rules are based on three different representations of time, and combine retrospectively and prospectively in ways that do your head in, even before you get to the fine print. This also reads like a history book. Here’s just a tiny fragment from the comments in the Israel section of the Asia file:

# According to the Office of the Secretary General of the Ministry of
# Interior, there is NO set rule for Daylight-Savings/Standard time changes.
# One thing is entrenched in law, however: that there must be at least 150
# days of daylight savings time annually. From 1993-1998, the change to
# daylight savings time was on a Friday morning from midnight IST to
# 1 a.m IDT; up until 1998, the change back to standard time was on a
# Saturday night from midnight daylight savings time to 11 p.m. standard
# time. 1996 is an exception to this rule where the change back to standard
# time took place on Sunday night instead of Saturday night to avoid
# conflicts with the Jewish New Year. In 1999, the change to
# daylight savings time was still on a Friday morning but from
# 2 a.m. IST to 3 a.m. IDT; furthermore, the change back to standard time
# was also on a Friday morning from 2 a.m. IDT to 1 a.m. IST for
# 1999 only. In the year 2000, the change to daylight savings time was
# similar to 1999, but although the change back will be on a Friday, it
# will take place from 1 a.m. IDT to midnight IST. Starting in 2001, all
# changes to/from will take place at 1 a.m. old time, but now there is no
# rule as to what day of the week it will take place in as the start date
# (except in 2003) is the night after the Passover Seder (i.e. the eve
# of the 16th of Nisan in the lunar Hebrew calendar) and the end date
# (except in 2002) is three nights before Yom Kippur [Day of Atonement]
# (the eve of the 7th of Tishrei in the lunar Hebrew calendar).

The tz files are a fascinating chronicle of the history of time, if you have the time and inclination.

Weirdly however, all of the complexity in tz can be expressed in a simple table that associates the time zone, a transition time, the corresponding time offset, and a further offset for DST. But because of the inscrutable tz rules, only a madman would attempt this. Sane people simply put a wrapper around the tz app and interrogate it.

Some years ago I wrote a Perl library to translate tz into simple, tabular form. With checks. And the matching SQL database called ‘smalltime’. You can now download it from the GitHub repository below.

My ‘solution’: https://github.com/jvanschalkwyk/smalltime

This is not for the faint-hearted, nor likely for the very sane among you.

Database insanity

There is also an SQL data standard for timestamps. Pretty much no relational database management system sticks to it (apart from PostgreSQL, mostly). Plot hole. So across RDBMSes, your code will break. Then, we get to programmers.

Recently, the elderly Patient Administration System (PAS) at my hospital has been ‘upgraded’ at great cost from a system that was written in the 1990s to one that was written in the 1980s. No, that’s not a typo. The new system uses wall time. And that’s not the worst of it. “How”, might you ask “are timestamps stored?”

Right. There’s a date field, and a time field. The date field is represented by a full Microsoft SQL timestamp—with time 00:00:00.000000. The Time field? A full Microsoft SQL timestamp, with a date of 1900-01-01. Plot hole! Don’t fucking ask. Just don’t.

There’s also the not-so-small matter that many databases and programs that use UNIX time, store it in a signed 32-bit integer. This will run out in 2038.¹⁰ Plot hole!

Not on my time

After all that, let’s be sensible. What do we need in a database? We need three things:

1. A reliable timestamp.

2. A representation of where it applies.

3. Some sort of timestamp precision.

These should in theory all be easy to do. That last may not be obvious, but consider the timestamp 2000-01-01 00:00:00. If this was recorded as e.g. the time a reading was taken from an anaesthetic monitor, it’s likely accurate to about a second. If it’s a “date of birth”, then it’s hugely suspect. Does it actually represent (a) A date with an unknown time of birth? (b) A guess that the person was born “around the start of the millennium?”, (c) “Around about midnight” or (d) Some weirdly obsessive, manic individual sitting there grinning and recording the precise time the infant was delivered, even to the minute?¹¹ Who really knows?

So here’s my reasonable proposal:

1. For the timestamp, use GPS time (or some encoding of this).

2. For the location, use GPS location (or some encoding of this).¹²

3. Also routinely record how precise the timestamp is.

4. And record provenance, of course :)

And that’s what I do. Can you see the plot holes?¹³

Time for me to stop.

My 2c, Dr Jo.

This is part 3 in a series of posts on fixing Health IT. The previous one concerned SNOMED CT, the next is simply about people.

1. In 1800, the brilliant mathematician Gauss wrote an algorithm for the conversion that is still used—and even he initially got it wrong!

2. Catholic countries went Gregorian quickly, under Pope Gregory XIII in 1582. Others were a bit slower. Saudi Arabia only moved officially in 2016. Ethiopia is a hold-out, but also Nepal, and Iran has the solar Hijri calendar (It seems Afghanistan has gone lunar again). North Korea only pretends not to use it.

3. These likely never even happened. But the UK tax year was extended.

4. This is entirely legal within ISO 8601. It even used to be theoretically possible for there to be a two-second adjustment as in 23:59:61.

5. Technically, they will “increase the maximum value for the difference between UT1 and UTC”.

6. Russian GLONASS satellite time however ...

7. This is a volunteer effort, designed by Paul Eggert, and still maintained by him; Arthur David Olson was a founding contributor. It has an interesting history. In 2011, astrologers (Yes, them. Not astronomers) at Astrolabe Inc. sued Olson, resulting in the database mailing list and FTP site being shut down. The, er, astrologers (Yeah, them) complained about use of data from The American Atlas, owned by Thomas G Shanks. After the Electronic Frontier Foundation got involved, they agreed never, ever to sue again. ICANN took over responsibility for database maintenance on 14 October, 2011.

8. The MS zone names are different, and may map to several IANA time zones. They update at different times. And MS may be laggy.

9. Cue manic laughter.

10. Also known as the ‘Epochalypse’. The seconds after 19 January 2038 at 03:14:07 UTC may be fraught.

11. And then, which bit are you watching emerge? Some seem to use “the last toe”, recorded to the minute. Others, the anterior shoulder—except for a breech.

12. Why mess with an imprecise ISO 8601 timestamp with a Zone offset that may well not even be honoured?

13. All choices have consequences. Out of interest, I use proleptic Julian GPS timestamps in microseconds, stored in a 64 bit integer. Julian day 0 is the day starting at noon, Universal Time, Monday January 1, 4713 BCE. The Julian day number for the day starting at noon on 1 January 2000 is 2,451,545. This encoding will run out about 2850 centuries from now.