今週の名言

「知識に対する投資は常に一番の利益を生み出す」
－ベンジャミン・フランクリン－

Oracleと暦法

以下はOracleデータベースがサポートする暦法（カレンダー）です。

Oracle® Databaseグローバリゼーション・サポート・ガイド
11gリリース2 (11.2)
B56307-04
NLS_CALENDAR 参照

Arabic Hijrah(イスラム歴)
English Hijrah(英語版イスラム歴)
Gregorian(グレゴリオ暦)
Japanese Imperial(日本の元号暦)
Persian(ペルシャ暦)
ROC Official(台湾暦)
Thai Buddha(タイ仏教暦)
Ethiopian(エチオピア歴、12c〜）

こんなにも多くのカレンダーを使うことができるというのも驚きですが、我々日本人にとって元号歴がサポートされているというのは非常にありがたいことです。（でも、どの年号からサポートされているのでしょうか？まさか「大化」というとこはないでしょうが。）

昭和天皇が崩御された時、私は某官公庁で大型汎用計算機の仕事をしていました。（Oracleアーキテクチャをどのように理解するか参照）

官公庁における報告書等は基本的に和暦が使用されますので、元号が「昭和」から「平成」に変わるということは非常に大変でした。

西暦から和暦に変換するファンクションを作成していたのですが、「平成01年」ではなくて「平成元年」と表示させてくれという仕様変更があり大慌てで修正したことを今でもよく覚えています。

その当時の苦労から比べると、今回紹介する和暦変換は非常に簡単ですので、是非業務に活用していただければと思います。

検証環境

今回はOracle11g R2(Oracle Linux6)の環境を使用します。
NLS_LANGは日本語表示の設定であることを確認します。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> !uname -rs
Linux 3.8.13-44.el6uek.x86_64

SQL> !env|grep NLS_LANG
NLS_LANG=Japanese_Japan.AL32UTF8

テーブル作成とデータ作成

日付データを格納する単純なテーブルを作成し、特定の日付データ（西暦）を挿入します。

SQL> CREATE TABLE jcal (id NUMBER,sdate DATE);

Table created.

SQL> INSERT INTO jcal VALUES (1,to_date('20150101','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (2,to_date('20141231','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (3,to_date('19890108','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (4,to_date('19890107','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (5,to_date('19261225','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (6,to_date('19261224','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (7,to_date('19120730','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (8,to_date('19120729','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (9,to_date('18680908','yyyymmdd'));

1 row created.

SQL> INSERT INTO jcal VALUES (10,to_date('18680907','yyyymmdd'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  FROM jcal
  5  ORDER BY sdate
  6 ;

  ID A.D.
---- ----------
  10 1868.09.07
   9 1868.09.08
   8 1912.07.29
   7 1912.07.30
   6 1926.12.24
   5 1926.12.25
   4 1989.01.07
   3 1989.01.08
   2 2014.12.31
   1 2015.01.01

10 rows selected.

１０件のデータが作成されました。

和暦変換した日付を表示させる

とりあえず和暦変換した日付を表示させましょう。
ポイントは「NLS_CALENDAR=’Japanese Imperial’」の指定です。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  FROM jcal
  6  ORDER BY sdate
  7 ;
,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
 *
ERROR at line 4:
ORA-01863: この年は現行カレンダではサポートされていません

おっと！いきなりエラーとなってしまいました。

サポートされるのは、明治元年９月８日以降

明治天皇が即位されたのは、１８９８年９月８日（慶応４年９月８日）で、この日を「明治元年９月８日」としたということです。
また、遡って「慶応４年１月１日」を「明治元年１月１日」と正式に定めたそうです。（Wikipedia「明治」参照。）

「NLS_CALENDAR=’Japanese Imperial’」では、１８９８年１月１日から９月７日までを、和暦（明治元年）で表示させようとするとエラーになるようです。（当然「慶応」以前の元号はサポートしていません。）

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  FROM jcal
  6  ORDER BY sdate
  7 ;
,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
 *
ERROR at line 4:
ORA-01863: この年は現行カレンダではサポートされていません

日付範囲を指定し直して再挑戦

今度は、「１８９８年９月７日」のデータを除外して再挑戦してみましょう。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  FROM jcal
  6  WHERE id < 10
  7  ORDER BY sdate
  8 ;

  ID A.D.       WAREKI-S
---- ---------- ----------
   9 1868.09.08 M01.09.08
   8 1912.07.29 M45.07.29
   7 1912.07.30 T01.07.30
   6 1926.12.24 T15.12.24
   5 1926.12.25 S01.12.25
   4 1989.01.07 S64.01.07
   3 1989.01.08 H01.01.08
   2 2014.12.31 H26.12.31
   1 2015.01.01 H27.01.01

9 rows selected.

エラーとなる和暦変換データがないので、クエリーは正常な結果を返します。
「eyy」の日付書式は「年号（短縮表記）＋年（２桁）」を示します。

年号（漢字表記）を表示させる

「eeyy」の日付書式は「年号（漢字表記）＋年（２桁）」を示します。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  ,TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''') "WAREKI-L"
  6  FROM jcal
  7  WHERE id < 10
  8  ORDER BY sdate
  9 ;

  ID A.D.       WAREKI-S   WAREKI-L
---- ---------- ---------- ------------------
   9 1868.09.08 M01.09.08  明治01年09月08日
   8 1912.07.29 M45.07.29  明治45年07月29日
   7 1912.07.30 T01.07.30  大正01年07月30日
   6 1926.12.24 T15.12.24  大正15年12月24日
   5 1926.12.25 S01.12.25  昭和01年12月25日
   4 1989.01.07 S64.01.07  昭和64年01月07日
   3 1989.01.08 H01.01.08  平成01年01月08日
   2 2014.12.31 H26.12.31  平成26年12月31日
   1 2015.01.01 H27.01.01  平成27年01月01日

9 rows selected.

応用編：「01年」を「元年」と表示させる

DECODE関数を使って、「01年」のみ「元年」と表示させてみます。

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  ,DECODE(TO_CHAR(sdate,'yy"','nls_calendar = ''Japanese Imperial''')
  6        ,'01',TO_CHAR(sdate,'ee"元年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')
  7        ,     TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')) "WAREKI-L"
  8  FROM jcal
  9  WHERE id < 10
 10  ORDER BY sdate
 11 ;

  ID A.D.       WAREKI-S   WAREKI-L
---- ---------- ---------- ------------------
   9 1868.09.08 M01.09.08  明治元年09月08日
   8 1912.07.29 M45.07.29  明治45年07月29日
   7 1912.07.30 T01.07.30  大正元年07月30日
   6 1926.12.24 T15.12.24  大正15年12月24日
   5 1926.12.25 S01.12.25  昭和元年12月25日
   4 1989.01.07 S64.01.07  昭和64年01月07日
   3 1989.01.08 H01.01.08  平成元年01月08日
   2 2014.12.31 H26.12.31  平成26年12月31日
   1 2015.01.01 H27.01.01  平成27年01月01日

9 rows selected.

曜日も表示させる

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'yyyy.mm.dd') "A.D."
  4  ,TO_CHAR(sdate,'eyy.mm.dd','nls_calendar = ''Japanese Imperial''') "WAREKI-S"
  5  ,DECODE(TO_CHAR(sdate,'yy"','nls_calendar = ''Japanese Imperial''')
  6        ,'01',TO_CHAR(sdate,'ee"元年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')
  7        ,     TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"','nls_calendar = ''Japanese Imperial''')) "WAREKI-L"
  8  ,TO_CHAR(sdate,'day') day
  9  FROM jcal
 10  WHERE id < 10
 11  ORDER BY sdate
 12 ;

  ID A.D.       WAREKI-S   WAREKI-L           DAY
---- ---------- ---------- ------------------ --------
   9 1868.09.08 M01.09.08  明治元年09月08日    火曜日
   8 1912.07.29 M45.07.29  明治45年07月29日    月曜日
   7 1912.07.30 T01.07.30  大正元年07月30日    火曜日
   6 1926.12.24 T15.12.24  大正15年12月24日    金曜日
   5 1926.12.25 S01.12.25  昭和元年12月25日    土曜日
   4 1989.01.07 S64.01.07  昭和64年01月07日    土曜日
   3 1989.01.08 H01.01.08  平成元年01月08日    日曜日
   2 2014.12.31 H26.12.31  平成26年12月31日    水曜日
   1 2015.01.01 H27.01.01  平成27年01月01日    木曜日

9 rows selected.

セッション単位で暦法を変更する

NLS_CALENDARは、「TO_CHAR」SQL関数だけでなく、ALTER SESSION、初期化パラメータおよび環境変数で指定することが可能です。
ここでは、ALTER SESSIONで変更する方法を示します。

SQL> ALTER SESSION SET NLS_CALENDAR='Japanese Imperial';

Session altered.

SQL> SELECT
  2   id
  3  ,TO_CHAR(sdate,'eyy.mm.dd') "WAREKI-S"
  4  ,DECODE(TO_CHAR(sdate,'yy"')
  5        ,'01',TO_CHAR(sdate,'ee"元年"mm"月"dd"日"')
  6        ,     TO_CHAR(sdate,'eeyy"年"mm"月"dd"日"')) "WAREKI-L"
  7  ,TO_CHAR(sdate,'day') day
  8  FROM jcal
  9  WHERE id < 10
 10  ORDER BY sdate
 11 ;

  ID WAREKI-S   WAREKI-L           DAY
---- ---------- ------------------ --------
   9 M01.09.08  明治元年09月08日    火曜日
   8 M45.07.29  明治45年07月29日    月曜日
   7 T01.07.30  大正元年07月30日    火曜日
   6 T15.12.24  大正15年12月24日    金曜日
   5 S01.12.25  昭和元年12月25日    土曜日
   4 S64.01.07  昭和64年01月07日    土曜日
   3 H01.01.08  平成元年01月08日    日曜日
   2 H26.12.31  平成26年12月31日    水曜日
   1 H27.01.01  平成27年01月01日    木曜日

9 rows selected.

このテーマは１回限りの小ネタのつもりでしたが、ちょっと面白くなってきたので次回も遊んでみたいと思います。

続く

サイクル＆オラクル

サイクリングとデータベースを楽しむサイト

カレンダーで遊んでみる①

今週の名言

「知識に対する投資は常に一番の利益を生み出す」
－ベンジャミン・フランクリン－

Oracleと暦法

検証環境

テーブル作成とデータ作成

和暦変換した日付を表示させる

サポートされるのは、明治元年９月８日以降

日付範囲を指定し直して再挑戦

年号（漢字表記）を表示させる

応用編：「01年」を「元年」と表示させる

曜日も表示させる

セッション単位で暦法を変更する

2015年5月
月	火	水	木	金	土	日
				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

今週の名言

「知識に対する投資は常に一番の利益を生み出す」 － ベンジャミン・フランクリン －

Oracleと暦法

検証環境

テーブル作成とデータ作成

和暦変換した日付を表示させる

サポートされるのは、明治元年９月８日以降

日付範囲を指定し直して再挑戦

年号（漢字表記）を表示させる

応用編：「01年」を「元年」と表示させる

曜日も表示させる

セッション単位で暦法を変更する

「知識に対する投資は常に一番の利益を生み出す」
－ベンジャミン・フランクリン－