Author: Written by Karel Zak on 2000-01-24.
The Postgres formatting functions provide a powerful set of tools for converting various datetypes (date/time, int, float, numeric) to formatted strings and for converting from formatted strings to specific datetypes.
Note: The second argument for all formatting functions is a template to be used for the conversion.
Table 5-7. Formatting Functions
| Function | Returns | Description | Example |
|---|---|---|---|
| to_char(timestamp, text) | text | convert timestamp to string | to_char(timestamp 'now','HH12:MI:SS') |
| to_char(int, text) | text | convert int4/int8 to string | to_char(125, '999') |
| to_char(float, text) | text | convert float4/float8 to string | to_char(125.8, '999D9') |
| to_char(numeric, text) | text | convert numeric to string | to_char(numeric '-125.8', '999D99S') |
| to_date(text, text) | date | convert string to date | to_date('05 Dec 2000', 'DD Mon YYYY') |
| to_timestamp(text, text) | date | convert string to timestamp | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
| to_number(text, text) | numeric | convert string to numeric | to_number('12,454.8-', '99G999D9S') |
Table 5-8. Templates for date/time conversions
| Template | Description |
|---|---|
| HH | hour of day (01-12) |
| HH12 | hour of day (01-12) |
| HH24 | hour of day (00-23) |
| MI | minute (00-59) |
| SS | second (00-59) |
| SSSS | seconds past midnight (0-86399) |
| AM or A.M. or PM or P.M. | meridian indicator (upper case) |
| am or a.m. or pm or p.m. | meridian indicator (lower case) |
| Y,YYY | year (4 and more digits) with comma |
| YYYY | year (4 and more digits) |
| YYY | last 3 digits of year |
| YY | last 2 digits of year |
| Y | last digit of year |
| BC or B.C. or AD or A.D. | year indicator (upper case) |
| bc or b.c. or ad or a.d. | year indicator (lower case) |
| MONTH | full upper case month name (9 chars) |
| Month | full mixed case month name (9 chars) |
| month | full lower case month name (9 chars) |
| MON | upper case abbreviated month name (3 chars) |
| Mon | abbreviated mixed case month name (3 chars) |
| mon | abbreviated lower case month name (3 chars) |
| MM | month (01-12) |
| DAY | full upper case day name (9 chars) |
| Day | full mixed case day name (9 chars) |
| day | full lower case day name (9 chars) |
| DY | abbreviated upper case day name (3 chars) |
| Dy | abbreviated mixed case day name (3 chars) |
| dy | abbreviated lower case day name (3 chars) |
| DDD | day of year (001-366) |
| DD | day of month (01-31) |
| D | day of week (1-7; SUN=1) |
| W | week of month |
| WW | week number of year |
| CC | century (2 digits) |
| J | Julian Day (days since January 1, 4712 BC) |
| Q | quarter |
| RM | month in Roman Numerals (I-XII; I=JAN) - upper case |
| rm | month in Roman Numerals (I-XII; I=JAN) - lower case |
All templates allow the use of prefix and suffix modifiers. Modifiers are always valid for use in templates. The prefix 'FX' is a global modifier only.
Table 5-9. Suffixes for templates for date/time to_char()
| Suffix | Description | Example |
|---|---|---|
| FM | fill mode prefix | FMMonth |
| TH | upper ordinal number suffix | DDTH |
| th | lower ordinal number suffix | DDTH |
| FX | FiXed format global option (see below) | FX Month DD Day |
| SP | spell mode (not yet implemented) | DDSP |
Usage notes:
to_timestamp and to_date skip blank space if the FX option is not used. FX must be specified as the first item in the template.
Backslash ("\") must be specified with a double backslash ("\\"); for example '\\HH\\MI\\SS'.
A double quote ('"') between quotation marks is skipped and is not parsed. If you want to write a double quote to output you must preceed it with a double backslash ('\\"), for example '\\"YYYY Month\\"'.
to_char supports text without a leading double quote ('"'), but any string between a quotation marks is rapidly handled and you are guaranteed that it will not be interpreted as a template keyword (example: '"Hello Year: "YYYY').
Table 5-10. Templates for to_char(numeric)
| Template | Description |
|---|---|
| 9 | value with the specified number of digits |
| 0 | value with leading zeros |
| . (period) | decimal point |
| , (comma) | group (thousand) separator |
| PR | negative value in angle brackets |
| S | negative value with minus sign (use locales) |
| L | currency symbol (use locales) |
| D | decimal point (use locales) |
| G | group separator (use locales) |
| MI | minus sign on specified position (if number < 0) |
| PL | plus sign on specified position (if number > 0) |
| SG | plus/minus sign on specified position |
| RN | roman numeral (input between 1 and 3999) |
| TH or th | convert to ordinal number |
| V | Shift n digits (see notes) |
| EEEE | science numbers. Now not supported. |
Usage notes:
A sign formatted using 'SG', 'PL' or 'MI' is not an anchor in the number; for example, to_char(-12, 'S9999') produces ' -12', but to_char(-12, 'MI9999') produces '- 12'. The Oracle implementation does not allow the use of MI ahead of 9, but rather requires that 9 preceeds MI.
PL, SG, and TH are Postgres extensions.
9 specifies a value with the same number of digits as there are 9s. If a digit is not available use blank space.
TH does not convert values less than zero and does not convert decimal numbers. TH is a Postgres extension.
V effectively multiplies the input values by 10^n, where n is the number of digits following V. to_char does not support the use of V combined with a decimal point (e.g. "99.9V99" is not allowed).
Table 5-11. to_char Examples
| Input | Output |
|---|---|
| to_char(now(),'Day, HH12:MI:SS') | 'Tuesday , 05:39:18' |
| to_char(now(),'FMDay, HH12:MI:SS') | 'Tuesday, 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' |