データドリブン経営の前提として考えるMVV：ミッション・ビジョン・バリューと戦略・戦術・戦法
前回の記事「Excelだけに存在する日付」では、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年問題」ですね。
2038年問題（にせんさんじゅうはちねんもんだい）は、2038年1月19日3時14分7秒（UTC、以下同様）を過ぎると、コンピュータが誤動作する可能性があるとされる年問題。
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の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の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の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」から見て
となります。
日付時刻型が扱える範囲の終了年月日である「9999/12/31 23:59:59」でも、
です。
内部で符号無し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
の範囲を扱えるようになっています。これらはいずれも、全期間にわたってグレゴリオ暦として扱われます。
（データベースではありませんが、前回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の日付/時刻型は、
100/01/01 00:00:00～9999/12/31 23:59:59
の範囲です。
Accessの場合、内部数値の「0」は「1899/12/30」を表します。Accessの日付/時刻型に時刻だけ入れたときに、「1899/12/30」が表示されたことのある方もいらっしゃると思います。
Excelと1日分ずれている理由は、
です。
それ以前の日にちは、マイナスの内部数値で持っています。
（内部数値的には扱えるのに、）西暦100年以降しか扱えないのは、西暦2桁入力のためだと思われます。
＜例＞99/12/30→1999/12/30と認識
（確証はありませんが、Lotusの日付の扱いもそうであったので、おそらく）。
Microsoft Accessでは、全期間にわたってグレゴリオ暦として扱われます。
ユリウス暦とグレゴリオ暦の扱いについては、データベースによって動作が異なり、
|
1582年9月
|
||||||
|
日
|
月
|
火
|
水
|
木
|
金
|
土
|
|
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月
|
||||||
|
日
|
月
|
火
|
水
|
木
|
金
|
土
|
|
1
|
||||||
|
2
|
3
|
4
|
15
|
16
|
17
|
18
|
|
19
|
20
|
21
|
22
|
23
|
24
|
25
|
|
26
|
27
|
28
|
29
|
30
|
3
|
|
となります。
Oracle DatabaseとPostgreSQLでは、表現できる西暦紀元前の日付に1年の差があるように見えます。この1年のズレは、次のような理由から起きているものです。
|
postgres=# SELECT "DATE1" AS "DATE", to_char("DATE",'J') AS "JULIUS" FROM "DATETEST"; |
ユリウス通日から、PostgreSQLでは、西暦紀元前1年12月31日の次の日が西暦1年1月1日であることがわかります。
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')); |
ならばと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日に、旧暦からグレゴリオ暦への改暦があったので、そちらの方が影響する機会が多いでしょうね。
20年以上の実績に裏打ちされた信頼のデータ連携ツール「Waha! Transformer」で、自社に眠るデータを有効活用。まずは無料のハンズオンセミナーや体験版で効果を実感していただけます。
Rankingランキング
New arrival新着
Keywordキーワード
