PostgreSQLの書式設定関数は多彩なデータ型(日付/時刻データ型、整数データ型、浮動小数点数データ型、数値データ型)を整形された文字列に変換したり、整形された文字列を特定のデータ型に変換する強力なツールの一式を提供しています。 表 9.23にこれらを列挙しています。 これら関数は共通の呼び出し規約を踏襲しています。最初の引数は整形される値で2番目の引数は入力書式または出力書式を定義するテンプレートです。
表9.23 書式設定関数
引数が1つのto_timestamp
関数もあります。
表 9.30を参照して下さい。
to_timestamp
とto_date
は、単純なキャストでは変換できない入力フォーマットを処理するために存在します。
ほとんどの標準的日付および時刻のフォーマットに対しては、入力文字列を必要なデータ型に単純にキャストすれば動作し、その方がずっと簡単です。
同様に、to_number
も標準的な数値表現に対しては不要です。
to_char
用の出力テンプレート文字列には、値に基づいて認識され、適切に整形されたデータで置き換えられるパターンがあります。
テンプレートパターンではない全てのテキストは単にそのままコピーされます。
同様に、(その他の関数用の)入力テンプレート文字列では、テンプレートパターンは入力されたデータ文字列で供給される値を特定します。
テンプレート文字列中にテンプレートパターンではない文字があれば、(テンプレート文字列の文字と同じかどうかにかかわらず)入力文字列データ中の該当文字は単にスキップされます。
表 9.24に、日付/時刻型の値の書式に使用可能なテンプレートパターンを示します。
表9.24 日付/時刻型の書式テンプレートパターン
パターン | 説明 |
---|---|
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 、am 、
PM 、またはpm | 午前/午後の指定(ピリオドなし) |
A.M. 、a.m. 、
P.M. 、またはp.m. | 午前/午後の指定(ピリオド付き) |
Y,YYY | コンマ付き年(4桁以上) |
YYYY | 年(4桁以上) |
YYY | 年の下3桁 |
YY | 年の下2桁 |
Y | 年の下1桁 |
IYYY | ISO 8601週番号年(4桁以上) |
IYY | ISO 8601週番号年の下3桁 |
IY | ISO 8601週番号年の下2桁 |
I | ISO 8601週番号年の下1桁 |
BC 、bc 、
AD 、またはad | 紀元前後の指定(ピリオドなし) |
B.C. 、b.c. 、
A.D. 、または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 | 通年の日にち番号(001〜366) |
IDDD | ISO 8601週番号年の日にち番号(001-371:通年 第1日は最初のISO週の月曜日) |
DD | 月内の日にち番号(01〜31) |
D | 曜日番号、日曜日(1 )から土曜日(7 )まで |
ID | |
ISO 8601の曜日番号、月曜日(1 )から日曜日(7 )まで | |
W | 月中の週番号(1〜5)(その月の初日がある週が第1週) |
WW | 年間を通じた週番号(1〜53)(元日のある週が第1週) |
IW | ISO 8601週番号年の年間を通じた週番号(01 - 53:新年の最初の木曜日がある週が第1週) |
CC | 世紀(2桁。21世紀は2001-01-01から開始) |
J | ユリウス日(UTC紀元前4714年11月24日午前零時からの整数による通算経過日) |
Q | 四半期 |
RM | 大文字ローマ数字による月(I〜XII、Iは1月) |
rm | 小文字ローマ数字による月(i〜xii、iは1月) |
TZ | 大文字による時間帯省略名(to_char 内でのみサポートされる) |
tz | 小文字による時間帯省略名(to_char 内でのみサポートされる) |
TZH | time-zoneの時間 |
TZM | time-zoneの分 |
OF | UTCからの時間帯オフセット(to_char 内でのみサポートされる) |
どのようなテンプレートパターンに対しても、その振舞いを変更するために修飾子を適用できます。
例えば、FMMonth
はFM
修飾子の付いたMonth
パターンです。
表 9.25に、日付/時刻書式の修飾子パターンを示します。
表9.25 日付/時刻書式用のテンプレートパターン修飾子
修飾子 | 説明 | 例 |
---|---|---|
FM 接頭辞 | 字詰めモード(先頭の0、およびを空白のパディングを無効) | FMMonth |
TH 接尾辞 | DDTH 、例えば12TH | |
th 接尾辞 | DDth 、例えば12th | |
FX 接頭辞 | 固定書式のグローバルオプション(使用上の注意事項を参照) | FX Month DD Day |
TM 接頭辞 | 翻訳モード(lc_timeに基づき、現地語化された曜日、月名を表示します) | TMMonth |
SP 接尾辞 | スペルモード(未実装) | DDSP |
日付/時刻型書式の使用上の注意事項は次のとおりです。
FM
は、先頭にはゼロ、末尾には空白を追加してパターンを固定長にする機能を無効にします。
PostgreSQLでは、FM
はその次に記述されたものだけを変更します。一方Oracleでは、FM
はそれに続く全ての記述に対して影響し、FM
修飾詞を繰り返すと、ゼロや空白を埋めるモードのオンとオフが切り替わります。
TM
には末尾の空白は含まれません。
to_timestamp
とto_date
はTM
修飾詞を無視します。
FX
オプションが使用されていない場合、to_timestamp
とto_date
は入力文字列内の複数の空白スペースを無視します。
例えば、to_timestamp('2000 JUN', 'YYYY MON')
は動作しますが、to_timestamp('2000 JUN','FXYYYY MON')
はエラーを返します。
後者のto_timestamp
はスペースが1つだけあることを期待するからです。
FX
はテンプレートの第1項目として指定される必要があります。
to_char
テンプレートには、通常のテキストを入れることができ、それはそのまま出力されます。
部分文字列を二重引用符で括ることで、部分文字列にテンプレートパターンがあったとしても、強制的にリテラルテキストとして解釈させることができます。
例えば、'"Hello Year "YYYY'
ではYYYY
は年データに置換されてしまいますが、Year
内のY
は置換されません。
to_date
、to_number
、to_timestamp
では、二重引用符で括られた文字の数だけ入力された文字をスキップします。例えば"XX"
は2文字の入力文字(それがXX
であるかどうかにかかわらず)をスキップします。
出力に二重引用符を付けたい場合、'\"YYYY Month\"'
のようにその前にバックスラッシュを付けなければなりません。
バックスラッシュは、二重引用符の外側では特別扱いされません。
二重引用符の内側では、バックスラッシュによって次の文字が何であれ文字通りに扱われるようになります。
(しかし、次の文字が二重引用符であるか、あるいは別のバックスラッシュでない限り、これは特別な効果をもたらしません。)
to_timestamp
においてto_date
、YYY
の様に4桁未満の年書式が指定され、かつ与えられる年が4桁未満だった場合、年は2020年に最も近くなるよう調整されます。例えば、95
の場合は1995年になります。
to_timestamp
およびto_date
においてYYYY
変換は、5桁以上の年数値を処理するときに制限事項があります。
このような場合、YYYY
の後に数字以外の文字またはテンプレートを使わなければなりません。 そうしないと年は常に4桁と解釈されます。
例えば(20000年として)、to_date('200001131', 'YYYYMMDD')
は4桁の年と解釈されるので、代わりにto_date('20000-1131', 'YYYY-MMDD')
またはto_date('20000Nov31', 'YYYYMonDD')
のように数字でない区切り文字を使用してください。
to_timestamp
およびto_date
においてYYY
、YYYY
、もしくはY,YYY
フィールドが存在するとCC
(世紀)フィールドは受け入れられますが、無視されます。
CC
がYY
もしくはY
と共に使用されると、結果は指定された世紀のその年として計算されます。
世紀が指定され、年が指定されないときは、その世紀の最初の年と想定されます。
to_timestamp
およびto_date
において、曜日の名前や数字(DAY
、D
および関連したフィールドの型)は受け付けられますが、結果を計算するという目的においては無視されます。
同じことは四半期(Q
)フィールドにも当てはまります。
to_timestamp
およびto_date
において、
ISO 8601週番号日は(グレゴリオ暦の日付とは異なって)以下の2つの方法のうちのひとつで指定できます。
年、通年の週番号、曜日番号。
例えば、to_date('2006-42-4', 'IYYY-IW-ID')
は、日付2006-10-19
を返します。
曜日番号を省略した場合、1(月曜日)と想定されます。
年と通年の日付番号。例えば、to_date('2006-291', 'IYYY-IDDD')
も2006-10-19
を返します。
ISO 8601週番号とグレゴリオ暦日のフィールドを混在して使用して日付を構築する試みは無意味なことで、エラーの原因になります。 ISO 8601週番号年の文脈では、「月」、あるいは「月内の日付番号」は意味を持ちません。 グレゴリオ暦の年の文脈では、ISO週番号は意味を持ちません。
to_date
はグレゴリオとISO週番号日のフィールドの混在を拒否しますが、to_char
はそうではありません。YYYY-MM-DD (IYYY-IDDD)
のような出力書式指定が有用な場合があるからです。
しかし、IYYY-MM-DD
のような書き方は避けてください。年の初めの近くで驚くべき結果になるでしょう。
(より詳細な情報は9.9.1を参照してください。)
to_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秒です。
to_char(..., 'ID')
の曜日番号付けはextract(isodow from ...)
関数に一致しますが、to_char(..., 'D')
の曜日番号付けはextract(dow from ...)
の曜日番号付けに一致しません。
to_char(interval)
関数は、HH
とHH12
を12時間の時計に表示されるように整形します。
例えば0時間と36時間はいずれも12
として出力します。
一方HH24
は時間の値をそのまま出力し、interval
の値であれば23を超えることも可能です。
表 9.26に、数値の書式設定に使用可能なテンプレートパターンを示します。
表9.26 数値書式用のテンプレートパターン
パターン | 説明 |
---|---|
9 | 数字の位置(必要ないときは表示しない) |
0 | 数字の位置(必要ないときでも表示する) |
. (ピリオド) | 小数点 |
, (コンマ) | 千単位で区切る符号 |
PR | 負の値の角括弧表示 |
S | 符号付き値(ロケールを使用) |
L | 通貨記号(ロケールを使用) |
D | 小数点(ロケールを使用) |
G | グループ区切り文字(ロケールを使用) |
MI | (数値 < 0であれば)指定位置にマイナス記号 |
PL | (数値 > 0であれば)指定位置にプラス記号 |
SG | 指定された位置にプラス/マイナス記号 |
RN | ローマ数字(入力は1~3999) |
TH またはth | 序数接尾辞 |
V | n 桁シフト(注意事項を参照) |
EEEE | 科学技術表記法用の指数 |
数値型書式の使用上の注意事項は次のとおりです。
0
は、それが先頭あるいは末尾のゼロであっても必ず表示する数字の位置を指定します。
9
も数字の位置を指定しますが、先頭のゼロであればそれは空白で置換され、また末尾のゼロで字詰めモードが指定されているときは削除されます。
(to_number()
では、これら2つのパターン文字は同じ意味になります。)
パターン文字S
、L
、D
、G
はそれぞれ現在のロケールで定義された符号、通貨記号、小数点、3桁区切り文字を表します(lc_monetaryおよびlc_numericを参照)。
パターン文字のピリオドとカンマはいずれもその文字そのものを表し、ロケールとは関係なく小数点と3桁区切り文字の意味を持ちます。
to_char()
のパターンで符号について明示的な条件付けがない場合、符号のために一桁が予約され、それは数に繋げられます(すぐ左側に置かれます)。
S
がいくつかの9
のすぐ左に置かれた場合、同様に数に繋げられます。
SG
、PL
、またはMI
で整形された符号は、数値と関連付けられません。
例えば、to_char(-12, 'MI9999')
は'- 12'
となる一方、to_char(-12, 'S9999')
は' -12'
となります。
(Oracleの実装では9
の前にMI
が置かれてはならず、9
の後にMI
が置かれることを要求しています。)
TH
はゼロ未満の値と小数は変換しません
PL
、SG
、およびTH
はPostgreSQLの拡張です。
to_number
において、L
あるいはTH
のように非データテンプレートが使われた場合には、それがデータ文字(すなわち、数字、符号、10進小数点あるいはカンマ)でない限りテンプレートパターンにマッチするかどうかにかかわらず、該当する数分だけの入力文字がスキップされます。
V
をto_char
につけると、入力値を10^
倍します。
ここでn
n
はV
に続く桁数です。
V
をto_number
につけると、同じように割り算をします。
to_char
およびto_number
は、小数点とV
との混在をサポートしません(例えば、99.9V99
とはできません)。
EEEE
(科学技術表記)は、桁と小数点のパターンを除き、他の書式パターンや修飾子と組み合わせて使うことはできず、また必ず書式文字列の最後に位置しなければなりません(例えば、9.99EEEE
は正しい表記となります)。
すべてのテンプレートについて、その動作を変えるために、いくつかの修飾子を適用できます。
例えば、FM99.99
はFM
修飾子が付いた99.99
パターンです。
表 9.27に、数値の書式用の修飾子パターンを示します。
表9.27 数値の書式用テンプレートパターン修飾子
修飾子 | 説明 | 例 |
---|---|---|
FM 添え字 | 字詰めモード(末尾の0と空白の埋め字を無効にする) | FM99.99 |
TH 添え字 | 大文字による序数添え字 | 999TH |
th 添え字 | 子文字による序数添え字 | 999th |
表 9.28に、to_char
関数を使用した例をいくつか示します。
表9.28 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, 'FM90.99') | '-0.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' |
to_char(0.0004859, '9.99EEEE') | ' 4.86e-04' |