新元号へのデータ置換（SQL）

プロジェクトの改修で「新元号への対応」があり、一番苦労した「DBに2019年5月1日以降の日付が存在した場合に、平成を新元号に置換する」SQLを、備忘録代わりに残します。

【環境】

postgresql9.5

【DBの設定】

今回テーブルのカラムについては３つ、西暦、和暦（例：平成～年）、和暦略記（例：H.～年）を用意。１つ年月日を入力し登録したら３パターンで格納することを想定します。
カラムについては下記の通り設定する。

テーブル名：datesample

論理名	物理名	データ型
西暦	seireki	date
和暦	wareki	character varying(10)
和暦略記	wareki_ryaku	character varying(22)

更にサンプルとして下記のレコードを登録

カラム名	seireki	wareki	wareki_ryaku
レコード１	2019-07-01	平成31年7月1日	H.31.7.1
レコード２	2021-01-01	平成33年1月1日	H.33.1.1

この２つのレコードを今回置換することにします。

【コードを書く前に注意点】

置換にあたり、注意事項があります

・今回の置換は、西暦から和暦を計算し置換を行う
和暦を分解し、計算・元号変換して置換する方法もできないことはないですが、月・日が１桁の場合と２桁の場合があり、それぞれに対応するとコードが複雑になるので、西暦を基に計算する方が確実かつ短く書けます。

・カラムのデータ型に注意
今回はサンプルでテーブルを作成し汎用的なカラムの設定を行いましたが、実際のプロジェクトのDBでは例外的な設定もあります（私はこれで引っかかりました…）。例えば西暦は本来date型で設定していることが多いのですが、稀に文字列型で設定している場合があります。状況としてありえるのが、年月日を入れるパターンと年月を入れるパターンがあるからdate型にできない、といった場合。この場合は今回書くコードを修正して頂く必要があるので気をつけて下さい。

【やってみましょう】

では早速コードを見てみましょう。
ちなみに今回は、新元号が分からないので、仮に’元号’、’G’としております。

UPDATE
  datesample
SET
  wareki =
CASE
  WHEN seireki >= '2019/05/01 00:00:00' THEN '元号' || TO_CHAR(TO_NUMBER(TO_CHAR(seireki, 'YYYY'), '9999') - 2018, 'FM99') || TO_CHAR(seireki, '年FMMM月FMDD日')
  ELSE wareki
END,
  wareki_ryaku =
CASE
  WHEN seireki >= '2019/05/01 00:00:00' THEN 'G.' || TO_CHAR(TO_NUMBER(TO_CHAR(seireki, 'YYYY'), '9999') - 2018, 'FM99') || TO_CHAR(seireki, '.FMMM.FMDD')
  ELSE wareki_ryaku
END,
  wareki = REPLACE(seireki, '元号1年', '元号元年');

結果は以下の通りになります。

カラム名	seireki	wareki	wareki_ryaku
レコード１	2019-07-01	元号元年7月1日	G.1.7.1
レコード２	2021-01-01	元号3年1月1日	G.3.1.1

処理は２つ。
①まず西暦を利用し、和暦、和暦略記を作成。
②その後和暦に関しては、新元号「１年」の場合は「元年」にする必要があるので変換する。

１つ目の処理が少しややこしいので解説します。
まずTO_CHARで西暦から年だけを抜き取り、計算できるようにTO_NUMBERで整数型に変換します。抜き取った年から2018年を引き、新元号と結合。更に、TO_CHARで西暦から月と日を抜き取り年～月～日を結合します年月日を抜き取る際に’FM’と入れているのは空白を入れないためです。
また、date型に関しては、DBには’2019-07-01’としか表示されておりませんが、実際には’2019-07-01 00:00:00’という形で入っております。date型を比較する際は気をつけましょう。
ELSEについては、条件に該当しなかった場合に元のデータに戻す処理になるのですが、これがなかったら条件に該当しないものはnullになりますので注意が必要です。