PostgreSQL 9.3.2文書 | ||||
---|---|---|---|---|
前のページ | 上に戻る | 第 9章関数と演算子 | 次のページ |
PostgreSQLの書式設定関数は多彩なデータ型(日付/時刻データ型、整数データ型、浮動小数点数データ型、数値データ型)を整形された文字列に変換したり、整形された文字列を特定のデータ型に変換する強力なツールの一式を提供しています。表9-20にこれらを列挙しています。これら関数は共通の呼び出し規約を踏襲しています。最初の引数は整形される値で2番目の引数は入力書式または出力書式を定義するテンプレートです。
単一引数のto_timestamp
関数も利用可能です。
これはdouble precision型の引数を取り、Unix時刻(1970-01-01 00:00:00+00からの秒数)からtimestamp with time zoneに変換します。
(Integer型のUnix時刻は暗黙的にdouble precisionにキャストされます。)
表 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_number(text, text)
| numeric | 文字列を数値に変換します | to_number('12,454.8-', '99G999D9S') |
to_timestamp(text, text)
| timestamp with time zone | 文字列をタイムスタンプに変換 | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
to_timestamp(double precision) | timestamp with time zone | Unix時刻をタイムスタンプに変換 | to_timestamp(1284352323) |
(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、am、 PM、またはpm | 午前/午後の指定(ピリオドなし) |
A.M.、a.m.、 PM、またはp.m. | 午前/午後の指定(ピリオド付き) |
Y,YYY | 句読点(コンマ)付き年(4桁以上) |
YYYY | 年(4桁以上) |
YYY | 年の下3桁 |
YY | 年の下2桁 |
Y | 年の下1桁 |
IYYY | ISO年(4以上の桁) |
IYY | ISO年の下3桁 |
IY | ISO年の下2桁 |
I | ISO年の下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通年の日にち番号(001-371:通年 第1日は最初のISO周の月曜日) |
DD | ひと月通算の日にち番号(01〜31) |
D | 1週通算の曜日番号、日曜日(1)から土曜日(7)まで |
ID | ISO1週通算の曜日番号、月曜日(1)から日曜日(7)まで |
W | 月中の週番号(1〜5)(その月の初日がある週が第1週) |
WW | 年間を通じた週番号(1〜53)(元旦のある週が第1週) |
IW | 年間を通じたISO週番号(01 - 53:新年の最初の木曜日がある週が第1週) |
CC | 世紀(2桁。21世紀は2001-01-01から開始) |
J | ユリウス日(UTC紀元前4714年11月24日午前零時からの整数による通算経過日) |
Q | クウォータ(四半期 : to_date とto_timestamp により無視されます) |
RM | 大文字ローマ数字による月(I〜XII:I=1月) |
rm | 小文字ローマ数字による月(i〜xii:i=1月) |
TZ | 大文字による時間帯名 |
tz | 小文字による時間帯名 |
修飾子はどのようなテンプレートパターンに対しても、その振舞いを変更するために適用することができます。例えば、FMMonthはFM修飾子の付いたMonthパターンです。表9-22に、日付/時刻書式の修飾子パターンを示します。
表 9-22. 日付/時刻書式用のテンプレートパターン修飾子
修飾子 | 説明 | 例 |
---|---|---|
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には末尾の空白は含まれません。
FXオプションがテンプレートが使用されている場合を除き、to_timestamp
とto_date
は入力文字列の複数の空白スペースを無視します。例えば、to_timestamp
にはたった1つのスペースがあることになっているので、to_timestamp('2000 JUN', 'YYYY MON')は動作しますが、to_timestamp('2000 JUN','FXYYYY MON')はエラーを返します。FXはテンプレートの第1項目として指定される必要があります。
to_char
テンプレートでは、通常のテキストが許され、そのまま出力されます。部分文字列を二重引用符で括ることで、部分文字列にパターン用のキーワードがあったとしても、強制的にリテラルテキストとして解釈させることができます。例えば、'"Hello Year "YYYY'ではYYYYは年データに置換されてしまいますが、Year内のYは置換されません。
to_date
、to_number
、to_timestamp
では、二重引用符で括られた文字の数だけ入力された文字をスキップします。例えば"XX"が指定された場合は2文字がスキップされます。
出力に二重引用符を付けたい場合、E'\\"YYYY Month\\"'のようにその前にバックスラッシュを付けなければなりません。
もし、YYYの様に4桁未満の年書式が指定され、かつ与えられる年が4桁未満だった場合、年は2020年に最も近くなるよう調整されます。例えば、95の場合は1995年になります。
文字列をtimestamp型もしくはdate型にするYYYY変換は、4桁以上の年数値を処理するとき制限が加えられます。このような場合、数字以外の文字またはYYYYの後にテンプレートを使わなければなりません。 そうしないと年は常に4桁と解釈されます。例えば(20000年として)、to_date('200001131', 'YYYYMMDD')は4桁の年と解釈されるので、to_date('20000-1131', 'YYYY-MMDD')またはto_date('20000Nov31', 'YYYYMonDD')のように数字でない区切り符号の使用をお勧めします。
文字列からtimestampもしくはdateへの変換において、YYY、YYYY、もしくはY,YYYフィールドが存在するとCC(世紀)フィールドは無視されます。CCがYYもしくはYと共に使用されると、指定された世紀の年と計算されます。 世紀が指定され、年が指定されないときは、その世紀の最初の年と想定されます。
ISO週番号と週の曜日番号(グレゴリオ暦の日付とは異なって)はto_timestamp
とto_date
の2つの方法のうちのひとつで指定できます。
年、通年の週番号、週の曜日番号。 例えば、to_date('2006-42-4', 'IYYY-IW-ID')は、日付2006-10-19を返します。曜日番号を省略した場合、1(月曜日)と想定されます。
年と通年の日付番号。例えば、to_date('2006-291', 'IYYY-IDDD')のは、同様2006-10-19を返します。
ISO週番号とグレゴリアン暦日のフィールドを混在して使用して日付を構築する試みは無意味なことで、エラーの原因になります。ISO年の文脈によると、"月"、あるいは"月中日付番号"は意味を持ちません。グレゴリオ暦の年の文脈では、ISO週番号は意味を持ちません。グレゴリアンとISOの日付の仕様の混在を避けなければなりません。
文字列型から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により、例えば0時と36時を12とするような、12時間表示の出力をします。一方HH24はインターバル間で23を超えることが可能な24時間表示の出力をします。
表9-23に、数値型の値の書式設定に使用可能なテンプレートパターンを示します。
表 9-23. 数値書式用のテンプレートパターン
パターン | 説明 |
---|---|
9 | 指定された桁数での値 |
0 | 前にゼロが付いた値 |
.(ピリオド) | 小数点 |
, (コンマ) | 千単位で区切る符号 |
PR | 負の値の角括弧表示 |
S | (ロケール使用の)符号付き値 |
L | (ロケール使用の)通貨記号 |
D | (ロケール使用の)小数点 |
G | (ロケール使用の)グループ区切り文字 |
MI | (数値 < 0であれば)指定位置にマイナス記号 |
PL | (数値 > 0であれば)指定位置にプラス記号 |
SG | 指定された位置にプラス/マイナス記号 |
RN | (1〜3999の入力値による)ローマ数字 |
THまたはth | 序数接尾辞 |
V | n 桁シフト(注意事項を参照) |
EEEE | 科学技術表記法用の指数 |
数値型書式の使用上の注意事項は次のとおりです。
SG、PL、またはMIで整形された符号は、数値と関連付けられません。 例えば、to_char(-12, 'MI9999')は' -12'となる一方、to_char(-12, 'S9999')は' -12'となります。 Oracleの実装では9の前のMIが置かれてはならず、9の後にMIが置かれることを要求しています。
9は9が並んでいる数と同じ桁数の値を出力します。 桁が使用可能でない場合、スペースを出力します。
THはゼロ未満の値と小数は変換しません
PL、SG、およびTHはPostgreSQLの拡張です。
Vは入力値を実質的に10^n乗します。
ここでnはVに続く桁数です。
to_char
関数は小数点とVとの混在をサポートしません(例えば、99.9V99 は許可されません)。
EEEE(科学技術表記)は、他の書式パターンや桁と小数点のパターンを除く修飾子との組み合わせで使うことはできず、また必ず書式文字列の最後に位置しなければなりません。(例えば、9.99EEEEは正しい表記となります。)
ある修飾子をその動作を変えるために、任意のテンプレートに適用することができます。 例えば、FM9999はFM修飾子が付いた9999パターンです。 表9-24に、数値の書式用の修飾子パターンを示します。
表 9-24. 数値の書式用テンプレートパターン修飾子
修飾子 | 説明 | 例 |
---|---|---|
FM添え字 | 字詰めモード(空白パディングの無効、および0の除去) | FM9999 |
TH添え字 | 大文字による序数添え字 | 999TH |
th添え字 | 子文字による序数添え字 | 999th |
表9-25に、to_char
関数を使用した例をいくつか示します。
表 9-25. to_char
の例
Expression | Result |
---|---|
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' |
to_char(0.0004859, '9.99EEEE') | ' 4.86e-04' |