今週の名言
「知識に対する投資は常に一番の利益を生み出す」
- ベンジャミン・フランクリン -
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.
10件のデータが作成されました。
和暦変換した日付を表示させる
とりあえず和暦変換した日付を表示させましょう。
ポイントは「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: この年は現行カレンダではサポートされていません
おっと!いきなりエラーとなってしまいました。
サポートされるのは、明治元年9月8日以降
明治天皇が即位されたのは、1898年9月8日(慶応4年9月8日)で、この日を「明治元年9月8日」としたということです。
また、遡って「慶応4年1月1日」を「明治元年1月1日」と正式に定めたそうです。(Wikipedia「明治」参照。)
「NLS_CALENDAR=’Japanese Imperial’」では、1898年1月1日から9月7日までを、和暦(明治元年)で表示させようとするとエラーになるようです。(当然「慶応」以前の元号はサポートしていません。)
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: この年は現行カレンダではサポートされていません
日付範囲を指定し直して再挑戦
今度は、「1898年9月7日」のデータを除外して再挑戦してみましょう。
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」の日付書式は「年号(短縮表記)+年(2桁)」を示します。
年号(漢字表記)を表示させる
「eeyy」の日付書式は「年号(漢字表記)+年(2桁)」を示します。
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.
このテーマは1回限りの小ネタのつもりでしたが、ちょっと面白くなってきたので次回も遊んでみたいと思います。
続く