[Nottingham] Smearing the Leap Second?… What of time in databases?...

[Andy Smith]

Hello,

On Tue, Dec 06, 2016 at 02:45:43PM +0000, Martin via Nottingham wrote:
> When working with a database for storing logging/transactions through a
> leap second, one barmy question is...
> 
> Will a database store leap seconds and compute with leap seconds
> accordingly?

It depends on if the column type supports it. MySQL's DATETIME,
TIMESTAMP and TIME data types do not, for example, and will store
00:00:00 (and give a warning).

> EG: Can you have transactions for 2015/07/01 08:59:60 (*)?

In UTC that would be 2015-06-30 23:59:60. My question would be where
would that timestamp be coming from? The POSIX time routines present
in Linux (e.g. clock_gettime()) repeat 59, i.e.

2015-06-31 23:59:58
2015-06-31 23:59:59
2015-06-31 23:59:59
2015-07-01 00:00:00

> And how is that extra second counted for time calculations that span
> across it?
> 
> Does the leap second get counted as an extra second of time?

Leap seconds are not normally considered in civil timekeeping, so
they don't appear anywhere "after the fact", except for in a table
of leap seconds that have been applied.

> Or are leap seconds *never considered* (ignored) for time differences?

Most timekeeping practices don't consider leap seconds. "Unix time",
for example, counts from 1970-01-01 00:00:00 without the insertion
of leap seconds so a delta between any two dates will be off by some
amount of observed seconds. e.g.:

$ date -d "2015-06-30 23:59:59" +%s
1435708799
$ date -d "2015-07-01 00:00:00" +%s
1435708800

Yet those of us who where conscious between 2015-06-30 23:59:59 and
2015-07-01 00:00:00 experienced 2 seconds, not 1.

UTC supports the insertion of leap seconds, and the display of them
as 23:59:60, but as with Unix time requires a lookup table of leap
seconds to tell the precise delta between two UTC times.

There are timekeeping standards that support accurate deltas, such
as UT1 and TAI:

    https://en.wikipedia.org/wiki/Universal_Time
    https://en.wikipedia.org/wiki/International_Atomic_Time

They do so by not having a concept of leap seconds and just counting
every elapsed second. As they are currently some 35 seconds offset
from UTC they can be confusing for people more used to civil time.

When calculating time deltas in running programs it is normally
advised to use clock_gettime with the clock CLOCK_MONOTONIC (as
opposed to CLOCK_REALTIME, the wall time). That gives you some
number which will only ever increase, at a rate of 1 second per
second of elapsed time. So you can calculate a delta even across
leap seconds or other clock adjustments.

As far as I know all database systems just store timestamps
internally as a count of seconds past some epoch and include no leap
second logic. So, to calculate deltas between times in a database
you either need to apply a lookup table to those times or else store
the times yourself as a large integer TAI or UT1 time.

> Does that mean that everyone works for free for a leap-second?!...

In UK since the leap second is at UTC, it will be at 23:59:60 or
00:59:60 local time so most people are probably not working. But if
you were then yes.

Much like if you're at working during the transition between BST and
GMT and aren't being paid hourly: you work an extra hour
uncompensated. That did actually affect me for many years because I
was on call in an arrangement that paid a daily rate. So an extra
hour of on call for no extra compensation!

Cheers,
Andy

-- 
https://bitfolk.com/ -- No-nonsense VPS hosting