postgresql , linux , posix , ut1 , utc , leap second , 閏秒 , epoch , solar time , 太陽時 , unix time , universal time , coordinated universal time , international atomic time
什麼是閏秒，為什麼需要閏秒，閏秒會不會對資料庫産生影響？
要搞清楚這個問題，可以查一下維基百科，對閏秒的解釋，牽扯出了一堆的概念，比如
posix , ut1 , utc , leap second , 閏秒 , epoch , solar time , 太陽時 , unix time , universal time , coordinated universal time , international atomic time
下面我小結一下。
<a href="https://en.wikipedia.org/wiki/solar_time">https://en.wikipedia.org/wiki/solar_time</a>
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.
<a href="http://baike.baidu.com/view/37435.htm">http://baike.baidu.com/view/37435.htm</a>
平太陽時
mt（mean solar time），簡稱“平時”，也就是我們日常生活中所使用的時間。
太陽連續兩次經過上中天的時間間隔，稱為真太陽日。我們知道，地球沿着橢圓形軌道運動的，太陽位于該橢圓的一個焦點上，是以，在一年中，日地距離不斷改變。根據開普勒第二定律，行星在軌道上運動的方式是它和太陽所聯結的直線在相同時間内所劃過的面積相等，可見，地球在軌道上做的是不等速運動，這樣一來，一年之内真太陽日的長度便不斷改變，不易選做計時機關，于是引進平太陽的概念。天文學上假定由一個太陽（平太陽）在天赤道上（而不是在黃赤道上）作等速運作，其速度等于運作在黃赤道上真太陽的平均速度，這個假想的太陽連續兩次上中天的時間間隔，叫做一個平太陽日，這也相當于把一年中真太陽日的平均稱為平太陽日[1] ，并且把1/24平太陽日取為1平太陽時。通常所謂的“日”和“時”，就是平太陽日和平太陽時的簡稱。
計時方法
地球自轉周期為基準的一種時間計量系統
簡稱平時。由于真太陽的運作速度和時角變化率不均勻，不适于作為計量均勻時間的基準，在天文學中引入平太陽。它在天赤道上作勻速運動，其速度與真太陽的平均速度相一緻。一個地方的平太陽時以平太陽對于該地子午圈的時角來度量。平太陽在該地下中天的瞬間作為平太陽時零時。平太陽時與平恒星時之間有互相換算關系。真太陽時與平太陽時的時刻之差即為時差。
一年四季的真太陽日長短不等
由于太陽在黃道上作變速運動，而黃道又向赤道傾斜，是以一年四季的真太陽日長短不等，在日常生活中使用不便。天文學上假設一個假想點，它每年和真太陽同時從春分點出發，也同時回到春分點來；不過它是從西向東在天球赤道上以均勻速度運作。這樣的一個假想點叫平太陽。平太陽連續兩次經過上中天的時間間隔，叫做平太陽日。1平太陽日有分為24平太陽時……等等。這個施加系統稱為平太陽時，簡稱平時。平時是以平太陽下中天起算的，平太陽時定義為：平太陽的時角加12小時。
雖然這樣變化，但是生物鐘可以很好的适應這種變化，但是電腦就不行了，這也是引出閏秒的原因之一，當然，指的不是日夜長短，而是日積月來帶來的年的長度變化。
目前全球應用最廣泛的時間基準
<a href="https://en.wikipedia.org/wiki/coordinated_universal_time">https://en.wikipedia.org/wiki/coordinated_universal_time</a>
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.
<a href="https://en.wikipedia.org/wiki/universal_time">https://en.wikipedia.org/wiki/universal_time</a>
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秒。
也就是網際網路中所說的閏秒事件。
高精度時鐘，全球通用的utc就基于它。
<a href="https://en.wikipedia.org/wiki/international_atomic_time">https://en.wikipedia.org/wiki/international_atomic_time</a>
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.
<a href="https://en.wikipedia.org/wiki/unix_time">https://en.wikipedia.org/wiki/unix_time</a>
為了計算機的使用，将時間數字化，以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的數字化表述為重複表現
前面已經說了，由于地球自轉變慢，導緻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.
<a href="https://time.is/time_zone_news/2016_will_end_with_a_leap_second">https://time.is/time_zone_news/2016_will_end_with_a_leap_second</a>
<a href="https://time.is/leapsecond">https://time.is/leapsecond</a>
<a href="https://en.wikipedia.org/wiki/leap_second">https://en.wikipedia.org/wiki/leap_second</a>
postgresql 基于utc計時，是以utc如何處理，它就如何處理，支援閏秒時60秒的寫法。
例如 '2016-12-31 23:59:60' 這種寫法相容，使用正常。
<a href="https://www.postgresql.org/docs/9.1/static/functions-datetime.html">https://www.postgresql.org/docs/9.1/static/functions-datetime.html</a>
測試
很顯然，postgresql的處理與utc一緻。
如果你的系統需要識别閏秒，可以加一個觸發器，對于閏秒的記錄，包括primary key記錄下來。存儲為text即可。 這樣就可以精準的還原當時的情況。
當然，你也可以再添加一列text來存儲它，當閏秒是記錄，當不是閏秒時設定為空.
我們即能使用時間的索引，也能使用文本的索引, 還可以使用partial index, 表達式索引來達到特殊的目的。
mysql目前還不支援60秒的寫法，閏秒時必須使用unix time來表示時間，否則會報錯。
例如 '2008-12-31 23:59:60' 這種寫法會報錯
如下
<a href="http://dev.mysql.com/doc/refman/5.7/en/time-zone-leap-seconds.html">http://dev.mysql.com/doc/refman/5.7/en/time-zone-leap-seconds.html</a>
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 不支援60秒的寫法，如果使用者的應用程式使用utc的話，就會因為閏秒産生問題。
<a href="http://stackoverflow.com/questions/16539436/unix-time-and-leap-seconds">http://stackoverflow.com/questions/16539436/unix-time-and-leap-seconds</a>
<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=675684fc23fd4287966694b1f108846bc14b6895">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=675684fc23fd4287966694b1f108846bc14b6895</a>
<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4fd8d6b3e77eb00cfd7bb8d3d130b147ba0d60f3">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4fd8d6b3e77eb00cfd7bb8d3d130b147ba0d60f3</a>
<a href="https://www.postgresql.org/message-id/flat/84bc7ab0d621a74893ec9c9e151293b022653ddd%40esessmb207.ericsson.se#[email protected]">https://www.postgresql.org/message-id/flat/84bc7ab0d621a74893ec9c9e151293b022653ddd%40esessmb207.ericsson.se#[email protected]</a>