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

[Martin]

Andy,

Thanks for the very good answers.

For (sub-second) process logging, I think I've decided that databases
are not a good way to go...


I've since found ESR's treatise on the Unix time subject:

Time, Clock, and Calendar Programming In C
http://www.catb.org/esr/time-programming/

"The C/Unix time- and date-handling API is a confusing jungle full of
the corpses of failed experiments and various other traps for the unwary..."

:-O


The critical bit is:

> 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).

Also to note that we have "civil" time whereby we assume the unit of
time to be the 'Earth day'.


This is all a question of reference. Time is relative!


Enough rambling...

There may yet be another posting or two to wrap this on up ;-)

Thanks,
Martin




On 07/12/16 20:14, Andy Smith via Nottingham wrote:
> 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



-- 
- ╔═══════════════════╦══════════════════════════════════════════╗
- ║   Martin Lomas    ║ OpenPGP (GPG/PGP) Public Key: 0xCEE1D3B7 ║
- ║ martin@ ml1 co uk ║ Import from   hkp://subkeys.pgp.net   or ║
- ║ ----------------- ║ http:// ml1 .co .uk/martin_ml1_co_uk.gpg ║
- ╚═══════════════════╩══════════════════════════════════════════╝