フォーマット関数(to_char)

PostgreSQLのフォーマット関数は、あらゆるデータ型(date/time,integer, floating point, numeric)をフォーマットされた文字列に変換したり、フォーマットされた文字列から特定のデータ型に変換できる、強力なツールです。これらの関数はすべて共通の呼び出し規約に従います。最初の引数はフォーマットされる値で、2つめの引数は出力または入力のフォーマットを定義するテンプレートです。

出力のテンプレート文字列には、ある一定のパターンがあり、それが認識され、フォーマットする値の大まかにフォーマットされたもので上書きします。テンプレートのパターンでないあらゆるテキストはそのままコピーされます。同様に、入力テンプレート文字列のテンプレートパターンは入力されたデータの文字列の部分を確かめ、そこにあるべき値を識別します。

★	関数	返り値	説明	例
★	to_char(timestamp, text)	text	timestampをstringに変換	to_char(timestamp 'now',' YYYY-MM-DD HH24:MI:SS')
	to_char(int, text)	text	int4/int8をstringに変換	to_char(125, '999')
	to_char(double precision, text)	text	real/double precisionをstringに変換	to_char(125.8, '999D9')
	to_char(numeric, text)	text	numericをstringに変換	to_char(numeric '-125.8', '999D99S')
	to_date(text, text)	date	stringをdateに変換	to_date('05 Dec 2000', 'DD Mon YYYY')
	to_timestamp(text, text)	timestamp	stringをtimestampに変換	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
	to_number(text, text)	numeric	stringをnumericに変換	to_number('12,454.8-','99G999D9S')

date/time変換のテンプレートパターン

★	パターン	説明
★	YYYY	年(4桁以上)
★	MM	月の数 (01-12)
★	DD	月の日にち (01-31)
★	D	曜日(1-7; 日曜日=1)
★	HH24	時(00-23) これは、よくHHと間違えるので注意が必要！！また、間違えていることに気づかないことが多い！！
★	MI	分(00-59)
★	SS	秒(00-59)
	HH	時 (01-12)
	HH12	時(01-12)
	SSSS	真夜中を過ぎた秒(0-86399)
	AM or A.M. or PM or P.M.	午前午後指定(大文字)
	am or a.m. or pm or p.m.	午前午後指定(小文字)
	Y,YYY	カンマ付き年(4桁以上)
	YYY	年の最後の3桁
	YY	年の最後の2桁
	Y	年の最後の桁
	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文字)
	DAY	すべて大文字の曜日(9文字になるようにスペースが入ります)
	Day	大文字/小文字の曜日(9文字になるようにスペースが入ります)
	day	すべて小文字の曜日(9文字になるようにスペースが入ります)
	DY	すべて大文字の短縮された曜日(3文字)
	Dy	短縮された大文字/小文字の曜日(3文字)
	dy	短縮された小文字の曜日(3文字)
	DDD	1年間の通算の日にち (001-366)
	W	月の週(1-5)。月の初日がある週が第1週
	WW	年通算の週(1-53)。年の初日がある週が第1週
	IW	ISO形式の年通算の週。(年最初の木曜日がある週が第1週)
	CC	世紀(2桁)
	J	ユリウス暦(紀元前4712年1月1日からの経過日)
	Q	四半期
	RM	大文字のローマ数字の月(I-XII; I=1月)
	rm	小文字のローマ数字の月(I-XII; I=1月)
	TZ	大文字のタイムゾーン名
	tz	小文字のタイムゾーン名

すべてのテンプレートパターンには、その動作を変更するために、添字を伴うことが可能です。例えば、 "FMMonth"は "Month"と修正子、 "FM"が一緒になったものです。

date/time変換のテンプレートパターン添字

添字	説明	例
`FM` 接頭辞	字詰めモード (スペース/ゼロを挿入するのを無効にする)	FMMonth
`TH` 接尾語	大文字の序数を追加	DDTH
`th` 接尾語	小文字の序数を追加	DDth
`FX` 接頭語	FX(固定長形式)テンプレート全体のスイッチ(下記参照)	FX Month DD Day
`SP` suffix	スペルモード(まだ実装されていません)	DDSP

使用上の注意：

FMは一定の長さにするために付いている余分なゼロや、スペースを自動的に挿入する機能を無効にするものです。
to_timestampとto_dateにおいて、FXオプションが指定されていない場合は、入力された文字のスペースを無視してしまいます。FXは必ずテンプレートの始めに記述されている必要があります。例えば、 to_timestampは1つのスペースがあることになっているので、 to_timestamp('2000 JUN','YYYY MON')は正しいのですが、 to_timestamp('2000 JUN','FXYYYY MON')ではエラーとなってしまいます。
文字列の中に"\"を含めたい場合は '\\HH\\MI\\SS'のように、必ず2つ重ねて下さい。これはPostgresで文字列を扱う場合には共通して言えることです。
通常のテキストをto_charのテンプレートで使用することが可能で、そのまま出力されます。パターンキーワードを含んでいる場合でもサブストリングを2重引用符内に記述し、強制的にテキストとして認識させることができます。たとえば、 '"Hello Year: "YYYY'では、YYYYは年データで置き換えられますが、1つのYでは置き換わりません。
出力結果に2重引用符を付けたい場合、'\\"YYYY Month\\"'のように、その前に必ずバックスラッシュを記述する必要があります。
YYYY変換で、文字列をtimestampやdateにするときに、 4桁以上の数字が使用されている場合は制約があります。 YYYYの後に数字以外の文字やテンプレートを置く必要があります。それを行わなければ、4桁の年と解釈されてしまいます。例えば、20000年を扱う場合、to_date('200001131', 'YYYYMMDD')は 4桁の年と解釈されてしまいます。年の後に数字でない区切りをおいた方が良いでしょう to_date('20000-1131', 'YYYY-MMDD')、またはto_date('20000Nov31', 'YYYYMonDD')なら 5桁の年と解釈されます。

数値(numeric)変換用のテンプレート

パターン	説明
9	指定した桁数の数字
0	一定の長さにするために付けられたゼロ
. (ピリオド)	小数点
, (カンマ)	1000毎のセパレータ
PR	角括弧内に負の値を出力
S	負の値は符号付きで出力(ロケールを使用します)
L	通貨記号(ロケールを使用します)
D	小数点(ロケールを使用します)
G	グループのセパレータ(ロケールを使用します)
MI	数字が0より小さい場合、指定された場所にマイナスの符号
PL	数字が0より大きい場合、指定された場所にプラスの符号
SG	指定された場所に符号
RN	ローマ数字(入力可能値は1から3999)[訳注：7.1では動作が不安定となっています。本家ですでにバグ報告されています。]
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は同じ桁数の数字を意味します。数字でない場合はスペースになります。
THは0以下の値と小数は変換しません。
PL、 SG、 THはPostgresの拡張です。
Vは効率良く入力値を10^n 倍します。nはVの後に来る数字です。 Vには小数点付き値の数値は使用できません。なお、 to_charではVと小数点を同時には(例：99.9V99) 使用できません。

to_char使用例

入力	出力
to_char(now(),'Day, DD HH12:MI:SS')	`'Tuesday , 06 05:39:18'`
to_char(now(),'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,'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,'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-decimal:"999" Post-decimal:" .999')	`'Pre-decimal: 485 Post-decimal: .800'`
to_char(12,'99V999')	`' 12000'`
to_char(12.4,'99V999')	`' 12400'`
to_char(12.45, '99V9')	`' 125'`