PostgreSQLの書式設定関数は多彩なデータ型(日付/時刻データ型、整数データ型、浮動小数点数データ型、数値データ型)を整形された文字列に変換したり、整形された文字列を特定のデータ型に変換する強力なツールの一式を提供しています。 表9-20にこれらを列挙しています。 これら関数は共通の呼び出し規約を踏襲しています。 最初の引数は整形される値で二番目の引数は入力書式または出力書式を定義するテンプレートです。
表 9-20. 書式設定関数
| 関数 | 返り値 | 説明 | 例 |
|---|---|---|---|
| to_char(timestamp, text) | text | タイムスタンプを文字列に変換 | to_char(current_timestamp, 'HH12:MI:SS') |
| to_char(interval, text) | text | 時間間隔を文字列に変換 | to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
| to_char(int, text) | text | 整数データを文字列に変換 | to_char(125, '999') |
| to_char(double precision, text) | text | 実数、倍精度数を文字列に変換 | to_char(125.8::real, '999D9') |
| to_char(numeric, text) | text | 数値を文字列に変換 | to_char(-125.8, '999D99S') |
| to_date(text, text) | date | 文字列を日付に変換 | to_date('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(text, text) | timestamp | 文字列をタイムスタンプに変換 | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_number(text, text) | numeric | 文字列を数値に変換 | to_number('12,454.8-', '99G999D9S') |
警告:to_char(interval, text) は廃れたもので、新規に作成するコードでは使用すべきではありません。 次のバージョンで削除する予定です。
(to_char用)出力テンプレート文字列には、それが認識され、整形される値から適切に整形されたデータで置き換えられるパターンがあります。 テンプレートパターンではないすべてのテキストは単にそのままコピーされます。 同様に、(to_char以外用)入力テンプレート文字列では、テンプレートパターンは入力されたデータ文字列の捜し出される部分と、そこで見つけ出される値を特定します。
表9-21に、日付/時刻型の値の書式に使用可能なテンプレートパターンを示します。
表 9-21. 日付/時刻型の書式テンプレートパターン
| パターン | 説明 |
|---|---|
| HH | 時（01-12） |
| HH12 | 時（01-12） |
| HH24 | 時（00-23） |
| MI | 分（00-59） |
| SS | 秒（00-59） |
| MS | ミリ秒(000-999) |
| US | マイクロ秒(000000-999999) |
| SSSS | 深夜0時からの秒数 (0-86399) |
| AM or A.M. or PM or P.M. | 午前/午後の指定(大文字) |
| am or a.m. or pm or p.m. | 午前/午後の指定(小文字) |
| Y,YYY | コンマ付き年(4桁以上) |
| YYYY | 年（4桁以上） |
| YYY | 年の下3桁 |
| YY | 年の下2桁 |
| Y | 年の下1桁 |
| BC or B.C. or AD or A.D. | 紀元前後の指定(大文字) |
| bc or b.c. or ad or a.d. | 紀元前後の指定(小文字) |
| MONTH | 完全な大文字での月名(9文字になるように空白でパッド) |
| Month | 完全な大文字小文字混在した月名(9文字になるように空白でパッド) |
| month | 完全な小文字での月名(9文字になるように空白でパッド) |
| MON | 短縮形の大文字での月名(3文字) |
| Mon | 短縮形の大文字小文字混在した月名(3文字) |
| mon | 短縮形の小文字での月名(3文字) |
| MM | 月番号 (01-12) |
| DAY | 完全な大文字での曜日 (9 文字になるように空白でパッド) |
| Day | 完全な大文字小文字混在した曜日 (9 文字になるように空白でパッド) |
| day | 完全な小文字での曜日 (9 文字になるように空白でパッド) |
| DY | 短縮形の大文字での曜日 (3 文字) |
| Dy | 短縮形の大文字小文字混在した曜日 (3 文字) |
| dy | 短縮形の小文字での曜日 (3 文字) |
| DDD | 1 年通算の日にち (001-366) |
| DD | 1 月通算の日にち (01-31) |
| D | 1 週通算の日にち (1-7： 日曜日=1) |
| W | 月中の週番号 (1-5) (その月の初日がある週が第 1 週) |
| WW | 年間を通じた週番号 (1-53) (元旦のある週が第 1 週) |
| IW | ISO 週番号 (新年の最初の木曜日がある週が第 1 週) |
| CC | 世紀 (2 桁) |
| J | ユリウス日 (紀元前 4713 年 1 月 1 日からの通算経過日) (訳注: 原文では4712BCとなっていますが、修正しています。) |
| Q | quarter (四半期) |
| RM | ローマ数字による月 (I-XII： I=1月) (大文字) |
| rm | ローマ数字による月 (i-xii： i=1月) (小文字) |
| TZ | 時間帯名称(大文字) |
| tz | 時間帯名称(小文字) |
ある種の修飾子はどのようなテンプレートパターンに対しても、その振舞いを変更するために適用することができます。 例えば、FMMonthはFM修飾子の付いたMonthパターンです。 表9-22に、日付/時刻書式の修飾子パターンを示します。
表 9-22. 日付/時刻書式用のテンプレートパターン修飾子
| 修飾子 | 説明 | 例 |
|---|---|---|
| FM 接頭辞 | 字詰めモード (空白およびゼロのパディングを無効) | FMMonth |
| TH 接尾辞 | 大文字の序数を追加 | DDTH |
| th 接尾辞 | 小文字の序数を追加 | DDth |
| FX 接頭辞 | 固定書式のグローバルオプション (使用上の注意事項参照) | FX Month DD Day |
| SP 接尾辞 | スペルモード (未実装) | DDSP |
日付/時刻型書式の使用上の注意事項
FMはパターンの出力を固定長にするため、先頭にはゼロ、後尾には空白を追加してしまう機能を無効にします。
FXオプションがテンプレートが使用されていない場合には、to_timestampとto_date は入力文字列の複数の空白スペースを無視します。 FXはテンプレートの第一項目として指定される必要があります。 例えば、to_timestampにはたった一つの空白があることになっているので、to_timestamp('2000 JUN', 'YYYY MON') が正しくto_timestamp('2000 JUN','FXYYYY MON') はエラーを返します。
to_charテンプレートでは、通常のテキストが許され、そのまま出力されます。 部分文字列を二重引用符で括ることで、部分文字列にパターン用のキーワードがあったとしても、強制的にリテラルテキストとして解釈させることができます。 例えば、'"Hello Year "YYYY'ではYYYYは年データに置換されてしまいますが、Year内のYは置換されません。
出力に二重引用符を付けたい場合、'\\"YYYY Month\\"'のようにその前にバックスラッシュを付けなければなりません。 (文字列定数内のバックスラッシュは既に特別な意味を持つため、2つのバックスラッシュが必要です。)
文字列をtimestamp型もしくはdate型にするYYYY変換は、年の値として4桁以上の数字を使用していると制限が加えられます。 このような場合、数字以外の文字またはYYYYの後にテンプレートを使わなければなりません。 そうしないと年は常に4桁と解釈されます。 例えば(20000年として)、to_date('200001131', 'YYYYMMDD')は4桁の年と解釈されるので、to_date('20000-1131', 'YYYY-MMDD')またはto_date('20000Nov31', 'YYYYMonDD')のように数字でない区切り符号の使用をお勧めします。
文字列型からtimestamp型への変換に際し、ミリ秒MSおよびマイクロ秒USの値は小数点の位置のあとの秒の部分として使用されます。 例えば、to_timestamp('12:3', 'SS:MS')は3ミリ秒ではなく300ミリ秒です。 なぜなら変換においてこれは12 + 0.3と計算されるからです。 ということは、SS:MS書式に対して入力値である12:3、12:30、および 12:300は同じミリ秒数を指定します。 3ミリ秒数が必要な場合には12:003のようにしなければなりません。 この時、変換において12 + 0.003 = 12.003秒と計算します。
もう少し複雑な例をあげます。 to_timestamp('15:12:02.020.001230','HH:MI:SS.MS.US')は15時間12分と2秒+20ミリ秒+1230マイクロ秒 = 2.021230秒です。
表9-23に、数値型の値の書式設定に使用可能なテンプレートパターンを示します。
表 9-23. 数値書式用のテンプレートパターン
| パターン | 説明 |
|---|---|
| 9 | 指定された桁数での値 |
| 0 | 前にゼロが付いた値 |
| . (period) | 小数点 |
| , (comma) | グループ(3桁)区切り符号 |
| PR | 負の値の角括弧表示 |
| S | (ロケール使用)マイナス記号付き負の値 |
| L | (ロケール使用)通貨記号 |
| D | (ロケール使用)小数点 |
| G | (ロケール使用)グループ区切り文字 |
| MI | (数値 < 0 であれば)指定位置にマイナス記号 |
| PL | (数値 > 0 であれば)指定位置にプラス記号 |
| SG | 指定された位置にプラス/マイナス記号 |
| RN | (1 〜 3999 の入力値による)ローマ数字 |
| TH or th | 序数変換 |
| V | n 桁シフト (注意事項を参照) |
| EEEE | 科学技術表記法 (未実装) |
数値型書式の使用上の注意事項
SG、PL、または MIで整形された符号は、数値と関連付けられません。 例えば、to_char(-12, 'S9999')は' -12'となる一方、to_char(-12, 'MI9999')は'- 12'となります。 Oracleの実装では9に先行してMIが置かれてはならず、9の後にMIが置かれることを要求しています。
9は9が並んでいる数と同じ桁数の値を出力します。 桁が使用可能でない場合、空白を出力します。
THはゼロ未満の値と小数は変換しません。
PL、SG、およびTHはPostgreSQLの拡張です。
Vは入力値を有効的に10^n乗します。 ここでnはVに続く桁数です。 to_char関数は小数点を含む数値とVとの混在をサポートしません。 (例えば、99.9V99 は許可されません。)
表9-24に、to_char関数を使用した例をいくつか示します。
表 9-24. to_char の例
| 式 | 出力 |
|---|---|
| to_char(current_timestamp, 'Day, DD HH12:MI:SS') | 'Tuesday , 06 05:39:18' |
| to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS') | 'Tuesday, 6 05:39:18' |
| to_char(-0.1, '99.99') | ' -.10' |
| to_char(-0.1, 'FM9.99') | '-.1' |
| to_char(0.1, '0.9') | ' 0.1' |
| to_char(12, '9990999.9') | ' 0012.0' |
| to_char(12, 'FM9990999.9') | '0012.' |
| to_char(485, '999') | ' 485' |
| to_char(-485, '999') | '-485' |
| to_char(485, '9 9 9') | ' 4 8 5' |
| to_char(1485, '9,999') | ' 1,485' |
| to_char(1485, '9G999') | ' 1 485' |
| to_char(148.5, '999.999') | ' 148.500' |
| to_char(148.5, 'FM999.999') | '148.5' |
| to_char(148.5, 'FM999.990') | '148.500' |
| to_char(148.5, '999D999') | ' 148,500' |
| to_char(3148.5, '9G999D999') | ' 3 148,500' |
| to_char(-485, '999S') | '485-' |
| to_char(-485, '999MI') | '485-' |
| to_char(485, '999MI') | '485 ' |
| to_char(485, 'FM999MI') | '485' |
| to_char(485, 'PL999') | '+485' |
| to_char(485, 'SG999') | '+485' |
| to_char(-485, 'SG999') | '-485' |
| to_char(-485, '9SG99') | '4-85' |
| to_char(-485, '999PR') | '<485>' |
| to_char(485, 'L999') | 'DM 485 |
| to_char(485, 'RN') | ' CDLXXXV' |
| to_char(485, 'FMRN') | 'CDLXXXV' |
| to_char(5.2, 'FMRN') | 'V' |
| to_char(482, '999th') | ' 482nd' |
| to_char(485, '"Good number:"999') | 'Good number: 485' |
| to_char(485.8, '"Pre:"999" Post:" .999') | 'Pre: 485 Post: .800' |
| to_char(12, '99V999') | ' 12000' |
| to_char(12.4, '99V999') | ' 12400' |
| to_char(12.45, '99V9') | ' 125' |