2014年12月16日火曜日

グレゴリオ暦?ユリウス暦? データベースによって異なる、日付時刻型が扱える範囲

こんにちは。近藤です。




前回、Excelに存在する、実在しない日付について書きました。


今回も日付つながりで、データベースの日付時刻型の話を書きたいと思います。

ほとんどのデータベースは、日付時刻型を持っています。日付時刻型は、数値型や文字型のようにどのデータベースでもほぼ共通、ということはなく、利用可能な範囲、分解能(秒単位、ミリ秒単位、マイクロ秒単位…)から内部の持ち方まで、データベースや型によって様々です(「範囲」が決まっているのは、日付時刻型に限りませんが)。
日付時刻型の内部値は、前回お伝えしたとおり『ある特定の日付を基準として置き、そこからの経過で表す』ものです。前回は「経過日数」と書きましたが、データベースによっては「経過秒数」だったり、現在のOracle Databaseのように全く異なる内部管理を行っている例もあったりします。

データの移行時に、まれに問題になったりするこの「日付時刻型」、各データベースが扱える「範囲」を並べてみます。


一昔前のデータベース

一昔前のデータベースでは、
1970/01/01 00:00:00 ~ 2038/01/19 03:14:07
などの範囲しか扱えませんでした。データベースによっては、この範囲を扱う日付時刻型を互換性のために残している場合もあります。
この範囲を扱うことができる日付時刻型は、(範囲の開始/終了日時から見て)UNIXエポックである1970/01/01 00:00:00からの秒数を符号付きの4バイト数値で持っていることがわかります。
なので、範囲の上限は、基準日時の2,147,483,647秒後となる、2038/01/19 03:14:07となる訳です。
これと同じ理由で起きるものが、「2038年問題」ですね。

Microsoft SQL Server

Microsoft SQL Serverのdatetime型は、
1753/01/01 00:00:00 ~ 9999/12/31 23:59:59
の範囲です。
1753/01/01という日付は、開始年月日としてはちょっと不思議な感じがしますね。この日付は、Microsoft SQL Serverの生き別れの兄であるSybase SQL Serverから引き継がれているものです。
さて、この日付は何でしょうか?
この日付は、コンピュータの容量から来る制限ではなく、暦が影響しています。
そのむかし、ヨーロッパではユリウス暦が使用されていましたが、近世に現在の暦であるグレゴリオ暦に改暦されました。この改暦なのですが、国によってタイミングが異なり、グレゴリオ暦が制定された1582年から次々改暦されています。
では、1753年ごろにどこの国が改暦したのかというと、大英帝国が改暦しています。大英帝国では、1752年の9月にユリウス暦からグレゴリオ暦に改暦しています。そのため、1752年9月には19日しか存在しません。改暦を跨いで対応できるようにすると大変なので、Sybase SQL Serverでは、グレゴリオ暦になってからの日付を扱うように、改暦の翌年である1753年を日付時刻型が扱える範囲の開始年としました。そして、それをMicrosoft SQL Serverも引き継いでいる、ということです。

Oracle Database

Oracle DatabaseのDate型が扱える範囲は、
-4712/01/01 00:00:00 ~ 9999/12/31 23:59:59
です。(Oracle Databaseの日本語マニュアルでは「西暦前4712年」という記載ですが、ここではマイナス表記で記載します)
-4712年1月1日…これまた不思議な日付から始まっています。この日付の近辺にある日付を探してみると、ユリウス暦の基準日となるユリウス日があります。このユリウス日は、西暦紀元前4713年1月1日の昼12時が基準となっています。
ですが、Oracle Databaseの開始日は、-4712年1月1日。なぜOracle DatabaseのDate型は-4712年から始まっているのでしょうか?
それは…下の方で。
なお、SQL Serverのところで出てきた、ユリウス暦からグレゴリオ暦への改暦は、1582年10月に行われたものとして処理され、ユリウス暦時代の年月日は、ユリウス暦に従った日付で扱われ、グレゴリオ暦へ改暦されたのちは、グレゴリオ暦として扱われます。

PostgreSQL

PostgreSQLのtimestamp型が扱える範囲は、バージョン8.4以降では、
西暦紀元前4713/01/01 ~ 294276/12/31
です。
「5874897年まで扱える」としていたバージョンもありましたが、PostgreSQL 8.4から、timestamp型の内部型を倍精度浮動小数点数から8バイト整数に変更したことで、現在の範囲になりました。今の範囲でも、他のデータベースに比べて十分大きいのですけれど。
内部的には、「2000/01/01 00:00:00」からの経過秒数を持っています。
この開始日時も、開始年はユリウス日を元にしていることがわかります。こちらは、範囲の開始日として西暦紀元前4713/01/01が使われています。
PostgreSQLでは、Oracle Databaseと異なり、全期間にわたってグレゴリオ暦として扱われます。

MySQL

MySQLのdatetime型が扱える範囲は、
1000/01/01 00:00:00 ~ 9999/12/31 23:59:59
です。
この開始日時は「サポートしている範囲」なので、それ以前の日付も入力できます。
MySQLでは、全期間にわたってグレゴリオ暦として扱われます。

現在の多くのデータベース

Microsoft SQL Serverのdatetime2型やDB2のtimestamp型など、現在の多くのデータベースでは、
0001/01/01 00:00:00 ~ 9999/12/31 23:59:59
の範囲を扱うことができる日付時刻型が用意されています。
この範囲は、紀元後の4桁までの西暦全てが扱える、ということですね。
この範囲を扱うことができる日付時刻型の多くは、ある時点の日時、例えば「0001/01/01 00:00:00」を「0」などと置き、経過日数や経過秒数などで管理しています(内部データでの管理方法はデータベースによって異なりますので、あくまで一例です)。
例えば、「2014/12/31 00:00:00」は、「0001/01/01 00:00:00」から見て

  • 経過日数で考えた場合、「735,597」日後
  • 経過秒数で考えた場合、「63,555,580,800」秒後

となります。
日付時刻型が扱える範囲の終了年月日である「9999/12/31 23:59:59」でも、たかだか

  • 経過日数で考えた場合、「3,652,058」日後
  • 経過秒数で考えた場合、「315,537,897,599」秒後

です。
内部で符号無し16進数を使って管理していると考えると、経過秒数で持ったとしても「0x497786387F」の5バイトで済んでしまいます。ですので、内部型を8バイトにしておき、秒数以上を5バイト、秒数以下を残り3バイト、として持っているデータベースや、日付を4バイト、時刻を4バイト、として持っているデータベースなどもあります。
また、0001/01/01から扱えるということで、ユリウス暦とグレゴリオ暦の改暦を跨ぐ期間となります。このあたりの処理は、データベースによって異なります。
例えば、Microsoft SQL Server 2008以降では、datetime2型という0001/01/01 00:00:00 ~ 9999/12/31 23:59:59の範囲を扱える日付時刻型が追加されています。また、Microsoft SQL Serverの生き別れた兄の子にあたるSybase Adaptive Server Enterprise(Sybase ASE)では、datetime型自体が拡張されていて、0001/01/01 00:00:00 ~ 9999/12/31 23:59:59の範囲を扱えるようになっています。これらはいずれも、全期間にわたってグレゴリオ暦として扱われます。

Microsoft Excel

(データベースではありませんが、前回Excelのことを書いたので、ついでに記載します)
Excelの日付型の範囲は、前回も書いたとおり
1900/01/01 00:00:00 ~ 9999/12/31 23:59:59
です(「1900年から計算する」設定の場合)。
1900/01/01から開始の理由(1899/12/31を内部数値「0」と置いた理由)は、「Lotus 1-2-3互換」のためです。

Microsoft Access

Microsoft Accessの日付/時刻型は、
100/01/01 00:00:00~9999/12/31 23:59:59
の範囲です。
Accessの場合、内部数値の「0」は「1899/12/30」を表します。Accessの日付/時刻型に時刻だけ入れたときに、「1899/12/30」が表示されたことのあるかたもいらっしゃると思います。
Excelと1日分ずれている理由は、

  • 1900/03/01から9999/12/31までの日付は、ExcelもAccessも同じ内部数値
  • 1900/02/29はAccessに存在しないので、1900/02/28以前は内部数値が1ずれる
  • そのため、1900/01/01は、Excelは「1」で、Accessは「2」
  • そこから2日さかのぼると、「1899/12/30」

です。
それ以前の日にちは、マイナスの内部数値で持っています。
(内部数値的には扱えるのに、)西暦100年以降しか扱えないのは、西暦2桁入力のためだと思います<例>99/12/30→1999/12/30と認識
(確証はありませんが、Lotusの日付の扱いもそうであったので、おそらく)。
Microsoft Accessでは、全期間にわたってグレゴリオ暦として扱われます。

ユリウス暦とグレゴリオ暦

ユリウス暦とグレゴリオ暦の扱いについては、データベースによって動作が異なり、

  • 全ての日付をグレゴリオ暦として処理(ISO8601準拠、ANSI SQL標準)

    15829



    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31


  • グレゴリオ暦とユリウス暦を認識して処理(たいていの場合は、グレゴリオ暦が制定された1582年9月で切り替え。1582年9月5日から14日までは存在しない。)

    15829






    1
    2
    3
    4
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31


となります。

西暦紀元前の表記

Oracle DatabaseとPostgreSQLでは、表現できる西暦紀元前の日付に1年の差があるように見えます。この1年のズレは、次のような理由から起きているものです。

  • PostgreSQL
    PostgreSQLは、西暦0年は存在しないものとして扱っています。
    →西暦1年の前の年は、西暦紀元前1年
    PostgreSQLで、西暦紀元前1年12月31日と西暦1年1月1日をユリウス通日(西暦紀元前4713年1月1日から何日目か)と共に表示してみます。

    postgres=# SELECT "DATE1" AS "DATE", to_char("DATE",'J') AS "JULIUS" FROM "DATETEST";
             DATE          | JULIUS
    -----------------------+---------
    0001-12-31 00:00:00 BC | 1721425
    0001-01-01 00:00:00    | 1721426


    ユリウス通日から、PostgreSQLでは、西暦紀元前1年12月31日の次の日が西暦1年1月1日であることがわかります。

  • Oracle Database
    Oracle Databaseのマニュアルには次のように記載されています。
    『Oracle Databaseでは、ユリウス日の計算に天文学方式を使用しています。この方式では、紀元前4713年は-4712として計算されます。これに対し、歴史学方式では、紀元前4713年は-4713として計算されます。Oracleのユリウス日を、歴史学方式で計算した値と比較する場合には、紀元前の日付に365日の違いがあることに注意してください。』
    ( https://docs.oracle.com/cd/E57425_01/121/SQLRF/sql_elements001.htm )
    天文学方式で計算する、ということなので、表記も天文学方式。
    これをPostgreSQLの説明のときと同じように書くと、
    →西暦1年の前の年は、0年(=西暦紀元前1年)
    →西暦1年の2年前の年は、-1年(=西暦紀元前2年)
    となり、Oracle Databaseには西暦0年が存在するように読み取れます。
    Oracle Databaseのマニュアルでは、この「-1年」を「西暦前1年」と記載しています。なので、説明がややこしいことになっているのです。
    Oracle Databaseが言う「西暦前1年」
    =Oracle Databaseの「-1年」
    =暦的には「西暦紀元前2年」
    となります。
    …なのですが、西暦紀元前1年のデータを入れようと考えて、「0000/01/01」(西暦に0)を指定するとエラーになります…

    SQL> insert into datetest values(to_date('0000/01/01','syyyy/mm/dd'));
    insert into datetest values(to_date('0000/01/01','syyyy/mm/dd'))
                                        *
    行1でエラーが発生しました。:
    ORA-01841: (周)年は-4713と+9999の間の0以外の数字を指定する必要があります

    ならば、とPostgreSQLと同じように「-0001/12/31」と「0001/01/01」のユリウス通日を表示してみると、

    SQL> select to_char(date1,'syyyy/mm/dd') as "DATE" ,to_char(date1,'j') as "JULIUS" from datetest;

    DATE        JULIUS
    ----------- -------
    -0001/12/31 1721057
     0001/01/01 1721424

    (西暦0年が存在しないので)隣り合っているはずの西暦紀元前1年12月31日と西暦1年1月1日の間に、366日あることがわかります。ここからも、西暦0年が存在するようなユリウス通日が返ってきます。でも、「西暦0年」は扱えないのです。

    このあたり、Oracleは非常に説明しづらい動作をするのです。西暦0年が扱えるようになっていればわかりやすいのですが…

ということで、今回は各データベースの日付時刻型が扱える範囲をまとめてみました。グレゴリオ暦とユリウス暦の話や、西暦0年の話が出てきましたが、実際に扱うデータでは、ほとんどこれらの日付が出てくることはないと思いますので、あまり気にしたことはなかったと思います。
日本の場合は、それらが影響するよりももっと近い1873年1月1日に、旧暦からグレゴリオ暦への改暦があったので、そっちの方が影響ありますし。


0 件のコメント:

コメントを投稿