TO_CHAR

This function converts date, time, timestamp, numeric, and boolean expressions to character strings. TO_CHAR is intended for cases where explicit casting is not feasible.

TO_CHAR( { date | time | timestamp | timestamptz }, datetime_format)
TO_CHAR( { smallint | integer | bigint | real | float | decimal }, numeric_format [, decsep, groupsep ] )
TO_CHAR(boolean, boolean_format)

You can use column references or literal values for the first argument to the function. The second argument must be a format string that specifies how to interpret each input value. For example, valid date formats for 2017-12-19 include YYYY-MM-DD and DD-MM-YYYY. The INTERVAL data type is not supported.

datetime_format
See Formats for Datetime Values.
Note: TO_CHAR(time) only supports a subset of the datetime_format patterns, specifically:
Pattern 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)
MS Millisecond (000-999)
US Microsecond (000000-999999)
AM, am, PM, pm Meridiem indicator (without periods)
A.M., a.m., P.M., p.m. Meridiem indicator (with periods)
numeric_format
See Numeric Formatting.
decsep, groupsep
TO_CHAR with numeric data types supports optional characters for the decimal separator and group separator (D and G in the numeric format string). For example, you can use a comma for the decimal point and commas to separate groups. The default decimal separator is a period, and the default group separator is a comma. You must specify both or none of these separators. These arguments only apply if the appropriate characters ('G','D', ',', '.') appear in the format string.
boolean_format
See Boolean Formatting

In a format string, patterns are recognized and replaced with appropriately formatted data; any literal text in the string that is not recognized as a template pattern is copied into the result as is.

Examples with Dates and Timestamps

premdb=# select to_char(timestamp '1992-08-01 00:00:00', 'YYYY-MM-DD HH12:MI:SS') 
from sys.const;
       to_char       
---------------------
 1992-08-01 12:00:00
(1 row)
premdb=# select to_char(matchday, 'YYYY-MM-DD') from match 
where seasonid=14 and htid=32 order by 1;
  to_char   
------------
 2005-08-13
 2005-08-23
 2005-09-17
 2005-10-01
 2005-10-22
...
premdb1=# select matchday, to_char(matchday, 'FMDay, Month DD, YYYY') from match where seasonid=21 and htid=2 order by 1;
      matchday       |           to_char            
---------------------+------------------------------
 2012-08-18 00:00:00 | Saturday, August 18, 2012
 2012-09-15 00:00:00 | Saturday, September 15, 2012
 2012-09-29 00:00:00 | Saturday, September 29, 2012
 2012-10-27 00:00:00 | Saturday, October 27, 2012
 2012-11-10 00:00:00 | Saturday, November 10, 2012
...

Examples with Numbers

Return character strings for avg_att values in the team table:
premdb=# select name, to_char(avg_att, '99.99') from team where avg_att>0.0 order by 2 desc;
         name         | to_char 
----------------------+---------
 Manchester United    |  75.29
 Arsenal              |  59.94
 Manchester City      |  54.04
 Newcastle United     |  49.75
 Liverpool            |  43.91
 Sunderland           |  43.07
...
Return the top five capacity values from the team table as exponents:
premdb=# select capacity, to_char(capacity,'EEEE') from team order by 1 desc limit 5;
 capacity | to_char 
----------+---------
    75635 |  8e+04
    60260 |  6e+04
    55097 |  6e+04
    52405 |  5e+04
    49000 |  5e+04
(5 rows)
Specify non-default decimal and group separators for a numeric constant. In this example, the decimal separator is a comma and the group separator is a space.
premdb=# select to_char(1234567.77, '9G999G999D99', ',', ' ');
    to_char    
---------------
  1 234 567,77
(1 row)

Example with Boolean

The following example returns a 1 (true) or a 0 (false) for the values in a boolean column:
premdb=# select c1, to_char(c1, '1') from booltable;
 c1 | to_char 
----+---------
 t  | 1
 f  | 0
 t  | 1
 f  | 0
(4 rows)

Numeric Formatting

Pattern Description
9 Digit position (can be dropped if insignificant)
0 Digit position (will not be dropped, even if insignificant)
. (period) Decimal point
, (comma) Thousands separator
PR Angle brackets for negative values. For example: to_char(-12, '9999PR') returns <12>.
S Sign anchored to number (uses locale)
D Decimal point (uses locale)
FM Fill mode prefix (a pattern modifier). When toggled on, the leading zeroes of all numbers will be suppressed until the modifier is used again. The modifier is not applied to fractional numbers, such as milliseconds and microseconds.
G Group separator (uses locale)
MI Minus sign in specified position (if number < 0). The MI and PL patterns cannot both be used in the same TO_CHAR function call.
PL Plus sign in specified position (if number > 0). The MI and PL patterns cannot both be used in the same TO_CHAR function call.
SG Plus/minus sign in specified position
TH or th Ordinal number suffix (a pattern modifier).
EEEE Exponent for scientific notation
Note: The currency symbol (L) is not supported.

Boolean Formatting

Pattern Description
T or F T for true, F for false
t or f t for true, f for false
TRUE, FALSE TRUE for true, FALSE for false
true, false true for true, false for false
1 or 0 1 for true, 0 for false
Y or N Y for true, N for false
y or n y for true, n for false
YES or NO YES for true, NO for false
yes or no yes for true, no for false