はい、承知いたしました。PostgreSQLの to_char 関数に関する詳細な解説記事を作成します。約5000語を目指し、日付、時刻、数値の整形に焦点を当て、具体的な使い方と例を豊富に盛り込みます。
PostgreSQLで日付や数値を思い通りに整形！to_char 関数の便利な使い方徹底解説
データベースに保存されたデータは、しばしば特定の形式で表示したり、レポートに出力したりする必要があります。特に日付、時刻、数値といったデータ型は、単にそのまま表示するだけではなく、「年-月-日」形式にしたり、「円」マークを付けたり、小数点以下の桁数を揃えたりといった整形（フォーマット）が必要になることが頻繁にあります。
PostgreSQLにおいて、このようなデータ整形を強力にサポートするのが to_char 関数です。この関数は、日付/時刻型 (date, time, timestamp, timestamptz)、数値型 (integer, numeric, double precisionなど)、さらにはインターバル型 (interval) のデータを、指定した書式文字列に従ってテキスト形式に変換します。
この記事では、PostgreSQLの to_char 関数をマスターするための、文法、様々な書式指定子（フォーマットコード）、修飾子、そして実用的な使用例を、豊富な具体例とともに詳細に解説します。これを読めば、あなたのデータ整形スキルが格段に向上することでしょう。
to_char 関数とは？なぜ重要なのか？
to_char 関数は、PostgreSQLが提供する型変換関数の一つです。その主な目的は、日付、時刻、数値、インターバルといった非文字列型のデータを、人間が読んだり、特定のシステムが処理したりしやすいように、整形された文字列に変換することです。
なぜこの関数が重要なのでしょうか？
- 表示形式の自由度: データベースに格納されている日付は
YYYY-MM-DD形式かもしれませんが、レポートではMM/DD/YYYYと表示したい、または「2023年12月25日(月)」のように表示したい場合があります。数値も、単なる12345.67ではなく、「¥12,345.67」や「123.46 %」のように表示したいことがあります。to_charはこれらの多様なニーズに応えられます。 - ロケール対応: 国や地域によって、日付の表示順序（MM/DD/YY vs DD/MM/YY）、小数点の記号（. vs ,）、桁区切りの記号（, vs . vs スペース）などが異なります。
to_charはPostgreSQLのロケール設定を利用して、これらの地域差に対応した整形が可能です。 - 特定情報の抽出: 日付から「曜日」だけを取り出したり、時刻から「午前/午後」を判定したりといった、特定の情報を文字列として抽出する用途にも使えます。
- 他の関数との連携:
to_charで整形した文字列を、さらに文字列操作関数（substring,replace,concatなど）と組み合わせて使用することも可能です。
to_char 関数の基本構文
to_char 関数の基本的な構文は非常にシンプルです。
sql
to_char(source, format)
source: 整形したい元のデータです。日付/時刻型、数値型、インターバル型のいずれかの式を指定します。format: 整形後の文字列の形式を指定する 書式文字列 です。この書式文字列の中に、特別な意味を持つ「書式指定子（フォーマットコード）」を記述します。書式指定子以外の文字は、ほとんどの場合、そのまま出力されます。
例:
sql
SELECT to_char(current_timestamp, 'YYYY/MM/DD HH24:MI:SS');
この例では、現在のタイムスタンプ (current_timestamp) を 'YYYY/MM/DD HH24:MI:SS' という書式で文字列に変換しています。YYYY, MM, DD, HH24, MI, SS が書式指定子です。/, :, (スペース) はそのまま出力されるリテラル文字です。
日付と時刻の整形 (to_char with Date/Time)
to_char が最も頻繁に使用される場面の一つが、日付や時刻の整形です。ここでは、日付/時刻型に使用できる様々な書式指定子と、その使い方を見ていきましょう。
日付/時刻の書式指定子（フォーマットコード）
利用可能な書式指定子は非常に多岐にわたります。主なものをカテゴリ別に見ていきましょう。
年 (Year)
YYYY: 西暦年 (4桁)。例:2023YYY: 西暦年 (下3桁)。例:023YY: 西暦年 (下2桁)。例:23Y,YYY: 西暦年 (下4桁、桁区切りあり)。例:2,023IYYY: ISO 8601 週番号年。週が属する年。ISO週の始まりは月曜日です。例:2023IYY: ISO 8601 週番号年 (下2桁)。例:23IY: ISO 8601 週番号年 (下2桁)。例:23I: ISO 8601 週番号年 (下1桁)。例:3
四半期 (Quarter)
Q: 年の四半期 (1-4)。例:4(10月〜12月の場合)
月 (Month)
MM: 月 (2桁、01-12)。例:12Month: 月のフル名 (ロケールに依存、右寄せ、スペースでパディング)。例:DecemberMonth: 月のフル名 (ロケールに依存、右寄せ、スペースでパディング)。例:December(FM 修飾子なしの場合)Mon: 月の省略名 (ロケールに依存、右寄せ、スペースでパディング)。例:DecWW: その年の週番号 (1-53)、週の始まりは年の最初の日。例:52W: その月の週番号 (1-5)、週の始まりは月の最初の日。例:5
日 (Day)
DD: 月の日 (2桁、01-31)。例:25DDDD: その年の日 (001-366)。例:359(12月25日の場合)DDD: その年の日 (001-366)。例:359DAY: 曜日のフル名 (ロケールに依存、右寄せ、スペースでパディング)。例:MondayDay: 曜日のフル名 (ロケールに依存、右寄せ、スペースでパディング)。例:Monday(FM 修飾子なしの場合)DY: 曜日の省略名 (ロケールに依存、右寄せ、スペースでパディング)。例:MonDy: 曜日の省略名 (ロケールに依存、右寄せ、スペースでパディング)。例:Mon(FM 修飾子なしの場合)D: 曜日の数字 (1-7)。1は日曜日。例:2(月曜日の場合)ID: ISO 8601 曜日の数字 (1-7)。1は月曜日。例:1(月曜日の場合)OF: Julian Day (ユリウス日)。例:2460302
週 (Week)
IW: ISO 8601 週番号 (01-53)、週の始まりは月曜日。例:52
時間 (Hour)
HH: 時 (12時間表示、01-12)。例:03(午後3時の場合)HH12: 時 (12時間表示、01-12)。例:03(午後3時の場合)HH24: 時 (24時間表示、00-23)。例:15(午後3時の場合)
分・秒 (Minute/Second)
MI: 分 (00-59)。例:05SS: 秒 (00-59)。例:08MS: ミリ秒 (000-999)。例:123(精度がミリ秒未満の場合、0埋め)US: マイクロ秒 (000000-999999)。例:123456(精度がマイクロ秒未満の場合、0埋め)
午前/午後 (AM/PM)
AMoram: 午前/午後 (ロケールに依存)。例:PMPMorpm: 午前/午後 (ロケールに依存)。例:PMA.M.ora.m.: 午前/午後 (ピリオド付き、ロケールに依存)。例:P.M.P.M.orp.m.: 午前/午後 (ピリオド付き、ロケールに依存)。例:P.M.
タイムゾーン (Time Zone)
TZ: タイムゾーン略称。例:JSTTZH: タイムゾーンオフセットの時。例:+09TZM: タイムゾーンオフセットの分。例:00OF: タイムゾーンオフセット (符号付き)。例:+09:00
紀元 (Era)
ADorad: 紀元 (AD or BC)。例:ADA.D.ora.d.: 紀元 (A.D. or B.C.)。例:A.D.BCorbc: 紀元 (BC or AD)。例:ADB.C.orb.c.: 紀元 (B.C. or A.D.)。例:A.D.
その他の指定子
CC: 世紀 (2桁)。例:21(2000年代の場合)J: ユリウス日。例:2460302RM: 月をローマ数字で (I-XII、大文字)。例:XIIrm: 月をローマ数字で (i-xii、小文字)。例:xiiSP: 数字をスペルアウト (例:FOUR)SPTH: 数字をスペルアウトし序数サフィックス付き (例:FOURTH)TM: ロケール依存の標準日付/時刻形式。TMは後述の修飾子です。TH: 日に序数サフィックスを付加 (例:25TH)。THは後述の修飾子です。
日付/時刻の書式指定子の例
いくつかの一般的な例を見てみましょう。現在の日付/時刻が 2023-12-25 15:05:08.123456+09 であると仮定します。
“`sql
— 基本的な形式
SELECT to_char(current_timestamp, ‘YYYY-MM-DD HH24:MI:SS’);
— 結果: 2023-12-25 15:05:08
SELECT to_char(current_timestamp, ‘MM/DD/YYYY’);
— 結果: 12/25/2023
SELECT to_char(current_timestamp, ‘DD Mon YYYY’);
— 結果: 25 Dec 2023
— 曜日の表示
SELECT to_char(current_timestamp, ‘YYYY-MM-DD DAY’);
— 結果: 2023-12-25 Monday
SELECT to_char(current_timestamp, ‘YYYY-MM-DD DY’);
— 結果: 2023-12-25 Mon
— 日本語ロケールでの曜日・月名 (ロケールの設定が必要です)
— 例: SET lc_time = ‘ja_JP.UTF-8’;
SELECT to_char(current_timestamp, ‘YYYY年MM月DD日 (DY)’);
— 結果例: 2023年12月25日 (月)
SELECT to_char(current_timestamp, ‘Month YYYY’);
— 結果例: 12月 2023 (ロケール、FM修飾子なしの場合のパディングによる)
— 12時間表示と午前/午後
SELECT to_char(current_timestamp, ‘YYYY-MM-DD HH:MI:SS AM’);
— 結果: 2023-12-25 03:05:08 PM
— 世紀と年の日の表示
SELECT to_char(current_timestamp, ‘CC YYYY DDD’);
— 結果: 21 2023 359
— 週番号
SELECT to_char(current_timestamp, ‘YYYY-WW IW’);
— 結果: 2023-52 52
— ミリ秒、マイクロ秒
SELECT to_char(current_timestamp, ‘YYYY-MM-DD HH24:MI:SS.MSUS’);
— 結果: 2023-12-25 15:05:08.123456
— タイムゾーン情報
SELECT to_char(current_timestamp, ‘YYYY-MM-DD HH24:MI:SS TZH:TZM OF’);
— 結果: 2023-12-25 15:05:08 +09:00 +09:00
“`
日付/時刻の書式修飾子
書式指定子単体では実現できない、より柔軟な整形のために修飾子があります。書式指定子の前に置くことで、その指定子の振る舞いを変更します。
FM(Format Modifier): Leading (先頭) および Trailing (末尾) のスペースやゼロを抑制します。- 日付/時刻の場合: 月名や曜日名、数字の先頭のゼロパディングや末尾のスペースパディングを削除します。
TM(Text Month/Day/Time): ロケール依存の標準的な日付、時刻、またはタイムスタンプの形式をテキストで出力します。TMは単独の指定子ではなく、TM Day,TM Month,TM Time,TMDate,TMDelta,TMFormatの形で使用します。
TH: 数字に序数サフィックス (英語の ‘th’, ‘st’, ‘nd’, ‘rd’) を付けます。DDやDと組み合わせて使用します。SP: 数字をスペルアウトします (例:1->ONE)。DDやDなどと組み合わせて使用します。SPTH: 数字をスペルアウトし、さらに序数サフィックスを付けます (例:1->FIRST)。DDやDなどと組み合わせて使用します。FMDY,FMMon,FMDAY,FMMonth:FMと月/曜日の指定子を組み合わせたものです。先頭のスペースやゼロを抑制します。
日付/時刻の書式修飾子の例
日付が 2023-12-05、時刻が 08:05:08 であると仮定します。
“`sql
— FM 修飾子 (パディング抑制)
SELECT to_char(date ‘2023-12-05’, ‘Month DD, YYYY’);
— 結果例: December 05, 2023 (Month と DD にパディングがある場合)
SELECT to_char(date ‘2023-12-05’, ‘FMMonth FMDD, YYYY’);
— 結果例: December 5, 2023 (パディングが抑制される)
SELECT to_char(date ‘2023-12-05’, ‘FMMonth DD, YYYY’); — DDにはFMがない場合
— 結果例: December 05, 2023
— TH 修飾子 (序数サフィックス)
SELECT to_char(date ‘2023-12-01’, ‘DDTH’);
— 結果: 01ST
SELECT to_char(date ‘2023-12-02’, ‘DDTH’);
— 結果: 02ND
SELECT to_char(date ‘2023-12-03’, ‘DDTH’);
— 結果: 03RD
SELECT to_char(date ‘2023-12-25’, ‘DDTH’);
— 結果: 25TH
SELECT to_char(date ‘2023-12-01’, ‘FMDDTH’); — FMと組み合わせる
— 結果: 1ST
— SP 修飾子 (スペルアウト)
SELECT to_char(date ‘2023-12-05’, ‘DDSP’);
— 結果: 05 FIVE
— SPTH 修飾子 (スペルアウト + 序数サフィックス)
SELECT to_char(date ‘2023-12-01’, ‘DDSPTH’);
— 結果: 01 FIRST
SELECT to_char(date ‘2023-12-05’, ‘FMDDSPTH’); — FMと組み合わせる
— 結果: 5 FIFTH
— TM 修飾子 (ロケール依存標準形式) – ロケール設定による
— 例: SET lc_time = ‘en_US.UTF-8’;
SELECT to_char(current_timestamp, ‘TM’); — これはエラーになるかも。TMは単独では使わない
— 正しい使い方
SELECT to_char(current_timestamp, ‘TMDate’); — 標準的な日付形式
— 結果例 (en_US): 12/25/2023
SELECT to_char(current_timestamp, ‘TMTime’); — 標準的な時刻形式
— 結果例 (en_US): 3:05:08 PM
SELECT to_char(current_timestamp, ‘TMDateTime’); — 標準的な日時形式
— 結果例 (en_US): Mon Dec 25 15:05:08 2023 PST — タイムゾーンによっては変わる
— 例: SET lc_time = ‘ja_JP.UTF-8’;
SELECT to_char(current_timestamp, ‘TMDate’);
— 結果例 (ja_JP): 2023/12/25
SELECT to_char(current_timestamp, ‘TMTime’);
— 結果例 (ja_JP): 15:05:08
SELECT to_char(current_timestamp, ‘TMDateTime’);
— 結果例 (ja_JP): 2023-12-25(月) 15:05:08 JST
“`
TM 修飾子は、ロケールに応じた標準的な日付/時刻表現を簡単に取得できるため便利です。
リテラル文字の扱い
書式文字列内のほとんどの文字は、書式指定子として認識されない限り、そのまま出力されます。ただし、二重引用符 "" を使用することで、書式指定子として認識される可能性のある文字や、特殊な文字をリテラルとして強制的に出力させることができます。
“`sql
SELECT to_char(current_date, ‘”Today is” YYYY-MM-DD’);
— 結果: Today is 2023-12-25
SELECT to_char(current_date, ‘YYYY-MM-DD (“Day” DAY)’);
— 結果: 2023-12-25 (Day Monday)
SELECT to_char(current_timestamp, ‘HH24″h”MI”m”SS”s”‘);
— 結果: 15h05m08s
“`
数値の整形 (to_char with Numeric)
to_char 関数は数値の整形にも非常に強力です。桁区切り、小数点以下の桁数調整、ゼロ埋め、通貨記号の付加など、様々なニーズに対応できます。
数値の書式指定子（フォーマットコード）
数値型に使用できる主な書式指定子です。
9: 数値の桁を表示します。表示する桁数よりも値の桁数が多い場合、#になります。先行する9が0の場合はスペースで埋められます (後述の0との違い)。例:99(値5->5)0: 数値の桁を表示します。表示する桁数よりも値の桁数が多い場合、#になります。0を指定した位置に数字がない場合、先行/後続に関わらずゼロで埋められます (ゼロ埋め)。例:09(値5->05).: 小数点の位置を指定します。ロケールによっては,になります。,: 桁区切りの位置を指定します。ロケールによっては.やスペースになります。$: 通貨記号を指定します。ロケールによっては他の記号になります。書式文字列の先頭または末尾にのみ使用可能です。L: ロケール依存の通貨記号。ロケールによっては複数の文字になる場合があります。PR: 負の値の場合、< >で囲みます。MI: 負の値の場合、-を末尾に付けます。PL: 正の値の場合、+を末尾に付けます。SG: 正の値の場合は+、負の値の場合は-を末尾に付けます。S: 正の値の場合は+、負の値の場合は-を先頭に付けます。EEEE: 指数表記（科学的記数法）。例:1.234E+05V:Vの位置に基づいて数値をスケーリングします。to_char(number, '99V99')は、number * 10^(小数桁数)となります。RN: 数値をローマ数字に変換します (1-3999)。例:XLII。to_char(42, 'RN')rn: 数値を小文字のローマ数字に変換します (1-3999)。例:xlii。to_char(42, 'rn')TH: 序数サフィックス (例:1ST,2ND) を付加します。整数部分に対してのみ機能します。SP: 数値をスペルアウトします (例:ONE,TWO)。整数部分に対してのみ機能します。SPTH: 数値をスペルアウトし、序数サフィックスを付加します (例:FIRST,SECOND)。整数部分に対してのみ機能します。
数値の書式指定子の例
いくつかの例を見てみましょう。
“`sql
— 基本的な桁表示 (9 と 0 の違い)
SELECT to_char(123, ‘999’); — 十分な桁数
— 結果: 123
SELECT to_char(123, ’99’); — 桁数不足 -> # になる
— 結果: ##
SELECT to_char(5, ’99’); — 先頭の9はスペース埋め
— 結果: 5
SELECT to_char(5, ’09’); — 先頭の0はゼロ埋め
— 結果: 05
SELECT to_char(123.45, ‘999.9’); — 小数点以下を切り捨て (丸めではない)
— 結果: 123.4
SELECT to_char(123.45, ‘999.99’); — 小数点以下をそのまま表示
— 結果: 123.45
SELECT to_char(123.45, ‘999.990’); — 小数点以下をゼロ埋め
— 結果: 123.450
SELECT to_char(123.4, ‘999.99’); — 小数点以下をスペース埋め
— 結果: 123.40 (通常は0埋めされることが多いが、小数点以下の9はデフォルトで0埋め)
— 桁区切り
SELECT to_char(1234567, ‘9,999,999’);
— 結果: 1,234,567
SELECT to_char(1234567.89, ‘9,999,999.99’);
— 結果: 1,234,567.89
— 通貨記号
SELECT to_char(12345.67, ‘$9,999.99’);
— 結果: $12,345.67
SELECT to_char(12345.67, ‘L9,999.99’); — ロケール依存通貨記号
— 結果例 (en_US): $12,345.67
— 結果例 (ja_JP): ¥12,345.67
— 符号表示
SELECT to_char(123, ‘S999’); — 正の値に +
— 結果: +123
SELECT to_char(-123, ‘S999’); — 負の値に –
— 結果: -123
SELECT to_char(123, ‘999SG’); — 末尾に +
— 結果: 123+
SELECT to_char(-123, ‘999SG’); — 末尾に –
— 結果: 123-
SELECT to_char(-123, ‘999MI’); — 負の値に末尾 –
— 結果: 123-
SELECT to_char(-123, ‘999PR’); — 負の値に < >
— 結果: <123>
— 指数表記
SELECT to_char(123456789, ‘9.99EEEE’);
— 結果: 1.23E+08
SELECT to_char(0.000123, ‘9.99EEEE’);
— 結果: 1.23E-04
— スケーリング (V)
SELECT to_char(123.45, ’99V99′);
— 結果: 12345 (123.45 * 10^2)
SELECT to_char(1.2, ‘999V99’);
— 結果: 00120 (1.2 * 10^2 = 120, 999指定子で0埋め)
— ローマ数字
SELECT to_char(42, ‘RN’);
— 結果: XLII
SELECT to_char(999, ‘rn’);
— 結果: cccxcix
— 序数サフィックス (TH)
SELECT to_char(25, ’99TH’);
— 結果: 25TH
SELECT to_char(101, ‘999TH’);
— 結果: 101ST
— スペルアウト (SP)
SELECT to_char(15, ’99SP’);
— 結果: 15 FIFTEEN
— スペルアウト + 序数サフィックス (SPTH)
SELECT to_char(1, ‘9SPTH’);
— 結果: 1 FIRST
SELECT to_char(15, ’99SPTH’);
— 結果: 15 FIFTEENTH
“`
数値の書式修飾子 (FM)
数値型に対しても FM 修飾子は有効です。数値の場合、FM は 先頭 のスペースおよび 末尾 のスペースを抑制します。これは、9 指定子で生じる先頭のスペースパディングや、形式によっては末尾に付加されるスペースを削除するのに役立ちます。
“`sql
SELECT to_char(5, ‘999’);
— 結果: 5 (スペース3桁分)
SELECT to_char(5, ‘FM999’);
— 結果: 5 (先頭のスペースが削除される)
SELECT to_char(123.45, ‘999.99 ‘); — 書式文字列に意図的に末尾スペースを入れた場合
— 結果: 123.45 (スペースがそのまま出力される)
SELECT to_char(123.45, ‘FM999.99 ‘);
— 結果: 123.45 (FMは末尾のスペースも抑制する)
“`
ただし、FM は 0 によるゼロ埋めは抑制しません。ゼロ埋めが必要な場合は 0 を使用し、スペース埋めを避けたい場合は FM を使用します。
ロケールと数値整形
日付と同様に、数値の整形もロケール設定に影響されます。具体的には、小数点記号 (.) と桁区切り記号 (,) がロケール依存の記号に置き換わります。
“`sql
— デフォルト (C or en_US) の場合
SELECT to_char(12345.67, ‘9G999D99’); — G: 桁区切り、D: 小数点
— 結果: 12,345.67
— ドイツ語ロケール (小数点に ,, 桁区切りに . を使う場合)
— 例: SET lc_numeric = ‘de_DE.UTF-8’;
SELECT to_char(12345.67, ‘9G999D99’);
— 結果例: 12.345,67
— フランス語ロケール (小数点に ,, 桁区切りにスペースを使う場合)
— 例: SET lc_numeric = ‘fr_FR.UTF-8’;
SELECT to_char(12345.67, ‘9G999D99’);
— 結果例: 12 345,67
“`
書式文字列中の . と , は、常に小数点と桁区切りの 位置 を示す記号であり、出力される記号はロケールによって決まります。明示的に記号を指定したい場合は、D (Decimal point) と G (Group separator) を使用する方がロケールに依存しない書式文字列を書く上で安全かもしれません。ただし、to_char のドキュメントでは . と , が一般的な記号として説明されています。ロケール対応を重視する場合は、L (LC_MONETARY の通貨記号)、G (LC_NUMERIC の桁区切り)、D (LC_NUMERIC の小数点) を使うのがより正確です。
インターバル型の整形 (to_char with Interval)
to_char は INTERVAL 型のデータも整形できます。インターバルは期間を表すため、使用できる書式指定子は日付/時刻のものと似ていますが、意味合いが異なります。
インターバル型の書式指定子
インターバルに使用できる主な書式指定子です。インターバルは「年」「月」「日」「時」「分」「秒」などの要素を持つため、対応する指定子を使います。
YYYY: 年数MM: 月数DD: 日数HH24: 時数 (00-23)HH: 時数 (01-12)MI: 分数 (00-59)SS: 秒数 (00-59)MS: ミリ秒 (000-999)US: マイクロ秒 (000000-999999)SS.MS,SS.US: 秒とミリ秒/マイクロ秒
インターバル型の書式指定子の例
“`sql
— 基本的なインターバル
SELECT to_char(INTERVAL ‘5 days 3 hours 20 minutes 15 seconds’, ‘DD HH24:MI:SS’);
— 結果: 05 03:20:15
SELECT to_char(INTERVAL ‘2 years 3 months’, ‘YYYY YYYY “years” MM “months”‘); — リテラル文字の使用
— 結果: 0002 0002 years 03 months
— ミリ秒を含むインターバル
SELECT to_char(INTERVAL ‘1 minute 5.123 seconds’, ‘MI:SS.MS’);
— 結果: 01:05.123
— インターバルでの FM 修飾子
SELECT to_char(INTERVAL ‘3 hours 5 minutes’, ‘HH24:MI’);
— 結果: 03:05
SELECT to_char(INTERVAL ‘3 hours 5 minutes’, ‘FMHH24:FMMI’);
— 結果: 3:5
— TM 修飾子 (TMDelta)
— これはロケール依存でインターバルを整形します
— 例: SET lc_messages = ‘en_US.UTF-8’;
SELECT to_char(INTERVAL ‘1 day 2 hours 30 minutes’, ‘TMDelta’);
— 結果例 (en_US): 1 day 02:30:00
SELECT to_char(INTERVAL ‘5 minutes’, ‘TMDelta’);
— 結果例 (en_US): 00:05:00
— 例: SET lc_messages = ‘ja_JP.UTF-8’;
SELECT to_char(INTERVAL ‘1 day 2 hours 30 minutes’, ‘TMDelta’);
— 結果例 (ja_JP): 1日 02:30:00
“`
インターバルの整形は、特に期間の表示や計算結果を人間が見やすい形式にする場合に便利です。
NULL値の扱い
to_char 関数の入力 (source) が NULL の場合、結果も通常 NULL になります。
“`sql
SELECT to_char(NULL::date, ‘YYYY-MM-DD’);
— 結果: NULL
SELECT to_char(NULL::numeric, ‘999.99’);
— 結果: NULL
“`
もし NULL の場合に特定の文字列（例: ‘N/A’ や ‘-‘）を表示したい場合は、COALESCE 関数などと組み合わせて使用します。
“`sql
SELECT COALESCE(to_char(NULL::date, ‘YYYY-MM-DD’), ‘N/A’);
— 結果: N/A
SELECT COALESCE(to_char(date ‘2023-12-25’, ‘YYYY-MM-DD’), ‘N/A’);
— 結果: 2023-12-25
“`
書式文字列 (format) が NULL の場合、to_char 関数はエラーを発生させます。
sql
SELECT to_char(current_date, NULL);
-- エラー: format string must not be null
to_char のパフォーマンスに関する考慮事項
to_char 関数自体は通常、比較的高速な操作です。しかし、大規模なテーブルに対して to_char を使用する際に注意すべき点があります。
- インデックスの使用:
WHERE句でto_charを使用してカラムをフィルタリングしようとする場合、そのカラムに通常のインデックスがついていても、そのインデックスは使用されません。例えば、WHERE to_char(timestamp_column, 'YYYY-MM-DD') = '2023-12-25'のようなクエリは、timestamp_columnにインデックスがあってもフルスキャンになる可能性が高いです。これは、to_char関数がカラムの値を変形させてしまうため、元のインデックス順序が維持されないからです。 - 解決策:
- 可能な限り、元のデータ型で比較を行うようにクエリを書き換えます。上記の例なら、
WHERE timestamp_column >= '2023-12-25'::date AND timestamp_column < '2023-12-26'::dateのようにします。 - どうしても
to_charの結果でフィルタリングしたい場合は、式インデックス を作成することを検討します。例えばCREATE INDEX timestamp_formatted_idx ON your_table (to_char(timestamp_column, 'YYYY-MM-DD'));のようなインデックスです。ただし、式インデックスは通常のインデックスよりも管理が複雑になることがあります。
- 可能な限り、元のデータ型で比較を行うようにクエリを書き換えます。上記の例なら、
- SELECT句での使用:
SELECT to_char(timestamp_column, '...') FROM your_tableのように、単に結果を表示するためにSELECT句でto_charを使用することは、通常パフォーマンスの問題にはなりません。
to_char と関連する関数
to_char は、PostgreSQLの型変換関数群の一部です。これに関連する関数として、その逆変換を行う関数があります。
to_date(text, format): 書式文字列に従って文字列を日付型に変換します。to_timestamp(text, format): 書式文字列に従って文字列をタイムスタンプ型に変換します。to_number(text, format): 書式文字列に従って文字列を数値型に変換します。
これらの関数は to_char と同じ書式指定子を使用しますが、その役割は逆になります。to_char がデータを「人が読むための文字列」にするのに対し、これらの関数は「人が書いた文字列」を「データベースが理解するデータ型」に変換します。
また、日付や時刻の特定の部分（年、月、日、時など）だけを数値として取り出したい場合は、date_part(field, source) または extract(field from source) 関数を使用する方がシンプルでパフォーマンスも良い場合があります。これらの関数は数値を返すのに対し、to_char は常に文字列を返します。
例:
sql
SELECT date_part('year', current_date); -- 結果: 2023.0 (numeric)
SELECT extract(year from current_date); -- 結果: 2023 (integer)
SELECT to_char(current_date, 'YYYY'); -- 結果: 2023 (text)
実践的な応用例
to_char 関数は、様々な実用的な場面で活躍します。
例1: レポート用の日時フォーマット
売上データにタイムスタンプカラム sale_time があるとして、レポートで「YYYY年MM月DD日 HH時MI分」形式で表示したい場合。
sql
SELECT
product_name,
price,
to_char(sale_time, 'YYYY"年"MM"月"DD"日" HH24"時"MI"分"') AS formatted_sale_time
FROM
sales
WHERE
sale_time >= '2023-12-01' AND sale_time < '2024-01-01';
例2: 会計レポートでの数値フォーマット
amount カラムが数値型で、これを日本の通貨形式「¥#,###」で表示したい場合。
“`sql
— ロケールがja_JP.UTF-8に設定されている前提
SELECT
item_name,
to_char(amount, ‘L9G999D99’) AS formatted_amount
FROM
expenses
WHERE
expense_date >= ‘2023-12-01’ AND expense_date < ‘2024-01-01’;
— もしくは、ロケール設定に依存せず強制的に「¥」と「,」を使用する場合（非推奨だが参考として）
— SELECT
— item_name,
— ‘¥’ || to_char(amount, ‘FM9,999,999.99’) AS formatted_amount — FMで先頭スペース除去
— FROM
— expenses;
``L
※はロケール依存の通貨記号を使用するため、日本のロケール設定(LC_MONETARY)が正しく行われていれば「¥」になります。GとD`も同様にロケール依存の桁区切り・小数点記号になります。FMがないと、金額の桁数に応じて先頭にスペースが入る場合があります。
例3: ログファイル名の生成
日々の処理で生成するログファイル名に、処理を実行した日付を「YYYYMMDD」形式で含めたい場合。
sql
SELECT 'log_' || to_char(current_date, 'YYYYMMDD') || '.txt';
-- 結果例: log_20231225.txt
例4: 誕生日リストでの月日のみ表示
ユーザーの誕生日データ birth_date から、「MM月DD日」形式で表示したい場合。
sql
SELECT
user_name,
to_char(birth_date, 'FMMM"月"FMDD"日"') AS birthday_md
FROM
users
ORDER BY
to_char(birth_date, 'MMDD'); -- 誕生日順に並べ替えたい場合にもto_charは便利
※ FMMM と FMDD は、月の数字や日の数字が一桁の場合に先頭のゼロを抑制します。
例5: 期間の表示フォーマット
イベントの期間をインターバルで保存しているテーブルで、「X日Y時間Z分」のように表示したい場合。
sql
SELECT
event_name,
to_char(duration, 'FMDD"日" FMHH24"時間" FMMI"分"') AS formatted_duration
FROM
events;
※ インターバルから年や月を取り出す場合、インターバルの定義によっては期待通りにならないことがあります（例: INTERVAL '1 month 3 days' から月だけ、日だけを取り出す場合など）。インターバルの表現形式と書式指定子の対応関係に注意が必要です。
よくある落とし穴とトラブルシューティング
- 書式指定子の誤字・スペルミス:
YYYYをYYYやYYと間違える、MMをMと間違えるなど。ドキュメントで正確な指定子を確認しましょう。 - データ型と書式指定子の不一致: 数値型のデータに日付/時刻用の書式指定子を使ったり、その逆を行ったりするとエラーになります。
- ロケール設定: 月名や曜日名、数値の桁区切り記号、小数点記号、通貨記号などはロケールに依存します。期待通りの出力にならない場合は、データベースまたはセッションのロケール設定（
LC_TIME,LC_NUMERIC,LC_MONETARY）を確認してください。SHOW lc_time;などで確認できます。 - 数値の
#表示:9指定子で表示しようとした数値が、指定子で確保した桁数を超えている場合に###...のように表示されます。より多くの9を使うか、ゼロ埋めしたいなら0を使用します。 - 小数点以下の丸め:
to_charは小数点以下の桁数を指定した場合、切り捨て を行います。数値として丸めたい場合は、事前にROUND()関数などを使用してからto_charを適用します。
sql
SELECT to_char(123.456, '999.99'); -- 結果: 123.45 (切り捨て)
SELECT to_char(round(123.456, 2), '999.99'); -- 結果: 123.46 (丸め) FM修飾子の効果:FMは先頭/末尾のスペースやゼロを抑制しますが、意図しない場所（特に数字の場合）でスペースが詰まりすぎることがあります。出力される文字列の幅を揃えたい場合は、FMを使用しないか、lpad/rpadなどの文字列関数と組み合わせることを検討します。
まとめ
PostgreSQLの to_char 関数は、日付、時刻、数値、インターバルといった様々なデータ型を、目的に応じた柔軟な形式の文字列に変換するための非常に強力で不可欠なツールです。
- 基本構文は
to_char(source, format)です。 format引数には、多様な 書式指定子 と 修飾子 を組み合わせて使用します。- 日付/時刻の整形では、年、月、日、時、分、秒、曜日、月名、タイムゾーンなどを様々な形式で表示できます。
FM,TH,SP,TMといった修飾子が表現の幅を広げます。 - 数値の整形では、桁区切り、小数点以下の桁数制御、ゼロ埋め、通貨記号、符号表示、指数表記、ローマ数字、序数などを制御できます。数値に対しても
FM修飾子は有効です。 - ロケール設定 (
LC_TIME,LC_NUMERIC,LC_MONETARY) は、月名、曜日名、桁区切り記号、小数点記号、通貨記号などの出力に影響を与えます。 - インターバル型も整形可能ですが、書式指定子の意味合いは期間に対応します。
NULL入力に対する振る舞いや、パフォーマンスに関する注意点（特にWHERE句での使用）を理解しておくことが重要です。to_date,to_timestamp,to_numberは逆の変換を行う関数であり、date_part/extractは特定要素を数値で取り出す関数です。
to_char は非常に多くの書式指定子と修飾子を持つため、最初はすべてを覚える必要はありません。必要な表現に合わせてドキュメントを参照しながら、少しずつ使いこなせるようになりましょう。特に、豊富な例を通じて、それぞれの指定子や修飾子がどのように機能するかを体感することが理解への近道です。
この記事が、あなたのPostgreSQLでのデータ整形作業の一助となれば幸いです。
付録: 主要な書式指定子一覧 (再掲・まとめ)
日付/時刻
| 指定子 | 説明 | 例 (2023-12-25 15:05:08.123456+09) |
|---|---|---|
YYYY |
西暦年 (4桁) | 2023 |
YY |
西暦年 (下2桁) | 23 |
MM |
月 (01-12) | 12 |
DD |
月の日 (01-31) | 25 |
HH24 |
時 (24時間表示, 00-23) | 15 |
HH / HH12 |
時 (12時間表示, 01-12) | 03 |
MI |
分 (00-59) | 05 |
SS |
秒 (00-59) | 08 |
MS |
ミリ秒 (000-999) | 123 |
US |
マイクロ秒 (000000-999999) | 123456 |
AM/PM |
午前/午後 | PM |
A.M./P.M. |
午前/午後 (ピリオド付き) | P.M. |
DAY |
曜日のフル名 (パディングあり) | Monday |
Day |
曜日のフル名 (FMなしでパディングあり) | Monday |
DY |
曜日の省略名 (パディングあり) | Mon |
Dy |
曜日の省略名 (FMなしでパディングあり) | Mon |
Month |
月のフル名 (パディングあり) | December |
Mon |
月の省略名 (パディングあり) | Dec |
DDD |
その年の日 (001-366) | 359 |
D |
曜日の数字 (1-7, 日曜始まり) | 2 |
ID |
ISO 8601 曜日の数字 (1-7, 月曜始まり) | 1 |
WW |
その年の週番号 (1-53, 年初始まり) | 52 |
IW |
ISO 8601 週番号 (01-53, 月曜始まり) | 52 |
Q |
四半期 (1-4) | 4 |
TZ |
タイムゾーン略称 | JST |
OF |
タイムゾーンオフセット (符号付き) | +09:00 |
J |
ユリウス日 | 2460302 |
CC |
世紀 | 21 |
RM/rm |
月をローマ数字 (大文字/小文字) | XII / xii |
TH |
日に序数サフィックス | 25TH |
SP |
日をスペルアウト | FIVE |
SPTH |
日をスペルアウトし序数サフィックス | FIFTH |
数値
| 指定子 | 説明 | 例 (値 12345.67, ロケール ja_JP) |
|---|---|---|
9 |
数値の桁。先行9はスペース埋め。桁数不足で#。 | to_char(5, '99') -> 5 |
0 |
数値の桁 (ゼロ埋め)。桁数不足で#。 | to_char(5, '09') -> 05 |
. |
小数点の位置 (ロケール依存Dにマップ) | 999.99 -> 12345,67 (ja_JP) |
, |
桁区切りの位置 (ロケール依存Gにマップ) | 9,999 -> 12.345 (ja_JP) |
D |
ロケール依存の小数点記号 | 999D99 -> 12345,67 (ja_JP) |
G |
ロケール依存の桁区切り記号 | 9G999 -> 12.345 (ja_JP) |
$ |
通貨記号 (固定 $) | $999 -> $123 |
L |
ロケール依存の通貨記号 | L999 -> ¥123 (ja_JP) |
PR |
負の値の場合 < > |
999PR (値 -123) -> <123> |
MI |
負の値の場合 末尾に - |
999MI (値 -123) -> 123- |
PL |
正の値の場合 末尾に + |
999PL (値 123) -> 123+ |
SG |
正/負の値の場合 末尾に +/- |
999SG (値 -123) -> 123- |
S |
正/負の値の場合 先頭に +/- |
S999 (値 -123) -> -123 |
EEEE |
指数表記 | 9.99EEEE (値 12345) -> 1.23E+04 |
V |
スケーリング | 99V99 (値 123.45) -> 12345 |
RN/rn |
ローマ数字 (大文字/小文字) | RN (値 42) -> XLII |
TH |
整数部分に序数サフィックス | 99TH (値 25) -> 25TH |
SP |
整数部分をスペルアウト | 99SP (値 15) -> 15 FIFTEEN |
SPTH |
整数部分をスペルアウトし序数サフィックス | 9SPTH (値 1) -> 1 FIRST |
修飾子 (日付/時刻 & 数値)
| 修飾子 | 適用対象 | 説明 |
|---|---|---|
FM |
日付/時刻, 数値 | Leading/Trailing のスペースやゼロを抑制。日付名/曜日のパディングも抑制。 |
TM |
日付/時刻 | ロケール依存の標準形式 (TMDate, TMTime, TMDelta などと組み合わせる)。 |
TH |
日付/時刻, 数値 | 序数サフィックス (TH)。 |
SP |
日付/時刻, 数値 | スペルアウト (SP)。 |
SPTH |
日付/時刻, 数値 | スペルアウト + 序数サフィックス (SPTH)。 |