2017元旦,你被閏秒嚇到了嗎?-閏秒背景與資料庫處理

德哥發表於2017-01-02

背景

什麼是閏秒，為什麼需要閏秒，閏秒會不會對資料庫產生影響？

要搞清楚這個問題，可以查一下維基百科，對閏秒的解釋，牽扯出了一堆的概念，比如

POSIX , UT1 , UTC , leap second , 閏秒 , epoch , solar time , 太陽時 , unix time , Universal Time , Coordinated Universal Time , International Atomic Time

下面我小結一下。

1. solar time , 太陽時

https://en.wikipedia.org/wiki/Solar_time

Solar time is a calculation of the passage of time based on the Sun`s position in the sky.

The fundamental unit of solar time is the day.

Two types of solar time are apparent solar time (sundial time) and mean solar time (clock time).

solar time是基於星體運轉的時間，比如地球自轉一週為一天。它的特點是很形象，但是不好在計算機中實施（或者說天體的運轉受制於各種因素的影響並不穩定，目前的原子鐘與之不能很好的對齊，這也是需要閏秒的開端）。

The difference between this calculated mean solar time and Coordinated Universal Time (UTC) determines whether a leap second is needed.

(The UTC time scale now runs on SI seconds, and the SI second, when adopted, was already a little shorter than the current value of the second of mean solar time.

2. mean solar time , 平太陽時

https://en.wikipedia.org/wiki/Solar_time

http://baike.baidu.com/view/37435.htm

平太陽時

MT（mean solar time），簡稱“平時”，也就是我們日常生活中所使用的時間。

太陽連續兩次經過上中天的時間間隔，稱為真太陽日。我們知道，地球沿著橢圓形軌道運動的，太陽位於該橢圓的一個焦點上，因此，在一年中，日地距離不斷改變。根據開普勒第二定律，行星在軌道上運動的方式是它和太陽所聯結的直線在相同時間內所劃過的面積相等，可見，地球在軌道上做的是不等速運動，這樣一來，一年之內真太陽日的長度便不斷改變，不易選做計時單位，於是引進平太陽的概念。天文學上假定由一個太陽（平太陽）在天赤道上（而不是在黃赤道上）作等速執行，其速度等於執行在黃赤道上真太陽的平均速度，這個假想的太陽連續兩次上中天的時間間隔，叫做一個平太陽日，這也相當於把一年中真太陽日的平均稱為平太陽日[1] ，並且把1/24平太陽日取為1平太陽時。通常所謂的“日”和“時”，就是平太陽日和平太陽時的簡稱。

計時方法

地球自轉週期為基準的一種時間計量系統

簡稱平時。由於真太陽的執行速度和時角變化率不均勻，不適於作為計量均勻時間的基準，在天文學中引入平太陽。它在天赤道上作勻速運動，其速度與真太陽的平均速度相一致。一個地方的平太陽時以平太陽對於該地子午圈的時角來度量。平太陽在該地下中天的瞬間作為平太陽時零時。平太陽時與平恆星時之間有相互換算關係。真太陽時與平太陽時的時刻之差即為時差。

一年四季的真太陽日長短不等

由於太陽在黃道上作變速運動，而黃道又向赤道傾斜，所以一年四季的真太陽日長短不等，在日常生活中使用不便。天文學上假設一個假想點，它每年和真太陽同時從春分點出發，也同時回到春分點來；不過它是從西向東在天球赤道上以均勻速度執行。這樣的一個假想點叫平太陽。平太陽連續兩次經過上中天的時間間隔，叫做平太陽日。1平太陽日有分為24平太陽時……等等。這個施加系統稱為平太陽時，簡稱平時。平時是以平太陽下中天起算的，平太陽時定義為：平太陽的時角加12小時。

雖然這樣變化，但是生物鐘可以很好的適應這種變化，但是電腦就不行了，這也是引出閏秒的原因之一，當然，指的不是日夜長短，而是日積月來帶來的年的長度變化。

3. UTC , Coordinated Universal Time

目前全球應用最廣泛的時間基準

https://en.wikipedia.org/wiki/Coordinated_Universal_Time

Coordinated Universal Time (French: Temps universel coordonné), abbreviated to UTC or CUT, is the primary time standard by which the world regulates clocks and time.

It is within about 1 second of mean solar time at 0° longitude. it does not observe daylight saving time.

It is one of several closely related successors to Greenwich Mean Time (GMT).

For most purposes, UTC is considered interchangeable with GMT, but GMT is no longer precisely defined by the scientific community.

雖然UTC基於平太陽時（靜態的基於平太陽時），但是由於地球自轉正在變慢，導致歷年以來，UTC為了貼近平太陽時，已經出現過多次微調。

Earth`s rotational speed is very slowly decreasing because of tidal deceleration;

this increases the length of the mean solar day.

The length of the SI second was calibrated on the basis of the second of ephemeris time and can now be seen to have a relationship with the mean solar day observed between 1750 and 1892, analysed by Simon Newcomb.

As a result, the SI second is close to 1/86400 of a mean solar day in the mid‑19th century.

In earlier centuries, the mean solar day was shorter than 86,400 SI seconds, and in more recent centuries it is longer than 86,400 seconds.

Near the end of the 20th century, the length of the mean solar day (also known simply as “length of day” or “LOD”) was approximately 86,400.0013 s.

For this reason, UT is now “slower” than TAI by the difference (or “excess” LOD) of 1.3 ms/day.

4. UT1 , Universal Time

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

UT1是基於地球自轉（太陽時）的時間標準，注意UT裡面除了UTC，其他都是基於太陽時的，UTC則是基於網際網路原子時鐘。

Universal Time (UT) is a time standard based on Earth`s rotation.

It is a modern continuation of Greenwich Mean Time (GMT), i.e., the mean solar time on the Prime Meridian at Greenwich, London, UK.

In fact, the expression “Universal Time” is ambiguous (when accuracy of better than a few seconds is required), as there are several versions of it, the most commonly used being Coordinated Universal Time (UTC) and UT1 (see below).

All of these versions of UT, except for UTC, are based on Earth`s rotation relative to distant celestial objects (stars and quasars), but with a scaling factor and other adjustments to make them closer to solar time.

UTC is based on International Atomic Time, with leap seconds added to keep it within 0.9 second of UT1.

因為UTC基於原子時鐘，不受地球自轉變慢的干擾，所以UTC會與UT1這種基於太陽時的計時標準產生偏差。

UTC 需要通過閏秒，確保UTC與UT1之間的誤差小於0.9秒。

也就是網際網路中所說的閏秒事件。

5. International Atomic Time

高精度時鐘，全球通用的UTC就基於它。

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

International Atomic Time (TAI, from the French name Temps Atomique International) is a high-precision atomic coordinate time standard based on the notional passage of proper time on Earth`s geoid。

TAI as a time scale is a weighted average of the time kept by over 400 atomic clocks in over 50 national laboratories worldwide.

The clocks are compared using GPS signals and two-way satellite time and frequency transfer.

Due to the signal averaging it is an order of magnitude more stable than its best clock alone would be.

The majority of the clocks are caesium clocks;

the definition of the SI second is written in terms of caesium.

6. unix time , POSIX time , Epoch time

https://en.wikipedia.org/wiki/Unix_time

為了計算機的使用，將時間數字化，以UTC為基準，計算偏移量，本質上也是基於網際網路原子時鐘，所以隨著地球自轉變慢，UTC與太陽時一定是會存在誤差的。

每天固定的86,400秒，基於UTC，UTC發生閏秒時，由於UNIX TIME的演算法是固定的每天86,400秒，所以閏秒時會出現重複值。

Unix time is a system for describing instants in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, not counting leap seconds.

It is used widely in Unix-like and many other operating systems and file formats.

Because it does not handle leap seconds, it is neither a linear representation of time nor a true representation of UTC.

Unix time may be checked on most Unix systems by typing date +%s on the command line.

Example:

1483297869 (ISO 8601: 2017-01-01T19:11:09Z)

the Unix time when this page was last generated

UTC中的閏秒例子，unix time的數字化表述為重複表現

leap second

前面已經說了，由於地球自轉變慢，導致UTC與UT1產生了誤差，閏秒是一種修正UTC與太陽時偏差的一種手段，歷史上已經經歷過很多次的閏秒動作。

Leap seconds are inserted or removed when necessary to keep UTC within one second of mean solar time (UT1).

Irregularities in the rotation of the Earth cause mean solar time to deviate from UTC.

Without leap seconds, UTC time would slowly drift apart from mean solar time.

https://time.is/time_zone_news/2016_will_end_with_a_leap_second

https://time.is/leapsecond

https://en.wikipedia.org/wiki/Leap_second

PostgreSQL如何處理閏秒?

PostgreSQL 基於UTC計時，所以UTC如何處理，它就如何處理，支援閏秒時60秒的寫法。

例如 `2016-12-31 23:59:60` 這種寫法相容，使用正常。

https://www.postgresql.org/docs/9.1/static/functions-datetime.html

測試

postgres=# create table leap_test(id int, ts timestamp, ut numeric, ts_text text);  
CREATE TABLE  
postgres=# insert into leap_test values (1, `2016-12-31 23:59:59 UTC`, extract(epoch from timestamp `2016-12-31 23:59:59 UTC`), `2016-12-31 23:59:59 UTC`);  
INSERT 0 1  
postgres=# insert into leap_test values (1, `2016-12-31 23:59:60 UTC`, extract(epoch from timestamp `2016-12-31 23:59:60 UTC`), `2016-12-31 23:59:60 UTC`);  
INSERT 0 1  
postgres=# insert into leap_test values (1, `2017-01-01 00:00:00 UTC`, extract(epoch from timestamp `2017-01-01 00:00:00 UTC`), `2017-01-01 00:00:00 UTC`);  
INSERT 0 1  
postgres=# select ctid,* from leap_test;  
 ctid  | id |         ts          |     ut     |         ts_text           
-------+----+---------------------+------------+-------------------------  
 (0,1) |  1 | 2016-12-31 23:59:59 | 1483228799 | 2016-12-31 23:59:59 UTC  
 (0,2) |  1 | 2017-01-01 00:00:00 | 1483228800 | 2016-12-31 23:59:60 UTC  // 閏秒識別   
 (0,3) |  1 | 2017-01-01 00:00:00 | 1483228800 | 2017-01-01 00:00:00 UTC  
(3 rows)

很顯然，PostgreSQL的處理與UTC一致。

如果你的系統需要識別閏秒，可以加一個觸發器，對於閏秒的記錄，包括Primary key記錄下來。儲存為TEXT即可。這樣就可以精準的還原當時的情況。

當然，你也可以再新增一列TEXT來儲存它，當閏秒是記錄，當不是閏秒時設定為空.

我們即能使用時間的索引，也能使用文字的索引, 還可以使用partial index, 表示式索引來達到特殊的目的。

MySQL 5.7 如何處理閏秒?

MySQL目前還不支援60秒的寫法，閏秒時必須使用unix time來表示時間，否則會報錯。

例如 `2008-12-31 23:59:60` 這種寫法會報錯

如下

http://dev.mysql.com/doc/refman/5.7/en/time-zone-leap-seconds.html

Leap second values are returned with a time part that ends with :59:59.

This means that a function such as NOW() can return the same value for two or three consecutive seconds during the leap second.

It remains true that literal temporal values having a time part that ends with :59:60 or :59:61 are considered invalid.

mysql> CREATE TABLE t1 (  
         a INT,  
         ts TIMESTAMP DEFAULT NOW(),  
         PRIMARY KEY (ts)  
       );  
Query OK, 0 rows affected (0.01 sec)  

mysql> -- change to UTC  
mysql> SET time_zone = `+00:00`;  
Query OK, 0 rows affected (0.00 sec)  

mysql> -- Simulate NOW() = `2008-12-31 23:59:59`  
mysql> SET timestamp = 1230767999;  
Query OK, 0 rows affected (0.00 sec)  

mysql> INSERT INTO t1 (a) VALUES (1);  
Query OK, 1 row affected (0.00 sec)  

mysql> -- Simulate NOW() = `2008-12-31 23:59:60`  
mysql> SET timestamp = 1230768000;  
Query OK, 0 rows affected (0.00 sec)  

mysql> INSERT INTO t1 (a) VALUES (2);  
Query OK, 1 row affected (0.00 sec)  

mysql> -- values differ internally but display the same  
mysql> SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t1;  
+------+---------------------+--------------------+  
| a    | ts                  | UNIX_TIMESTAMP(ts) |  
+------+---------------------+--------------------+  
|    1 | 2008-12-31 23:59:59 |         1230767999 |  
|    2 | 2008-12-31 23:59:59 |         1230768000 |  
+------+---------------------+--------------------+  
2 rows in set (0.00 sec)  

mysql> -- only the non-leap value matches  
mysql> SELECT * FROM t1 WHERE ts = `2008-12-31 23:59:59`;  
+------+---------------------+  
| a    | ts                  |  
+------+---------------------+  
|    1 | 2008-12-31 23:59:59 |  
+------+---------------------+  
1 row in set (0.00 sec)

MySQL 不支援60秒的寫法，如果使用者的應用程式使用UTC的話，就會因為閏秒產生問題。

mysql> -- the leap value with seconds=60 is invalid  
mysql> SELECT * FROM t1 WHERE ts = `2008-12-31 23:59:60`;  
Empty set, 2 warnings (0.00 sec)  
To work around this, you can use a comparison based on the UTC value actually stored in column, which has the leap second correction applied:  

mysql> -- selecting using UNIX_TIMESTAMP value return leap value  
mysql> SELECT * FROM t1 WHERE UNIX_TIMESTAMP(ts) = 1230768000;  
+------+---------------------+  
| a    | ts                  |  
+------+---------------------+  
|    2 | 2008-12-31 23:59:59 |  
+------+---------------------+  
1 row in set (0.00 sec)