PostgreSQL Tutorial: TO_CHAR Function

September 19, 2023

Summary: The PostgreSQL TO_CHAR() function converts a timestamp, an interval, an integer, a double precision, or a numeric value to a string.

Table of Contents

Syntax

The following illustrates the syntax of the PostgreSQL TO_CHAR() function:

TO_CHAR(expression, format)

Arguments

The PostgreSQL TO_CHAR() function requires two arguments:

1) expression

The expression can be a timestamp, an interval, an integer, a double precision, or a numeric value that is converted to a string according to a specific format.

2) format

The format for the result string.

The following table illustrates the valid numeric format strings:

Format	Description
9	Numeric value with the specified number of digits
0	Numeric value with leading zeros
. (period)	decimal point
D	decimal point that uses locale
, (comma)	group (thousand) separator
FM	Fill mode, which suppresses padding blanks and leading zeroes.
PR	Negative value in angle brackets.
S	Sign anchored to a number that uses locale
L	Currency symbol that uses locale
G	Group separator that uses locale
MI	Minus sign in the specified position for numbers that are less than 0.
PL	Plus sign in the specified position for numbers that are greater than 0.
SG	Plus / minus sign in the specified position
RN	Roman numeral that ranges from 1 to 3999
TH or th	Upper case or lower case ordinal number suffix

The following table shows the valid timestamp format strings:

Pattern	Description
Y,YYY	year in 4 digits with comma
YYYY	year in 4 digits
YYY	last 3 digits of year
YY	last 2 digits of year
Y	The last digit of year
IYYY	ISO 8601 week-numbering year (4 or more digits)
IYY	Last 3 digits of ISO 8601 week-numbering year
IY	Last 2 digits of ISO 8601 week-numbering year
I	Last digit of ISO 8601 week-numbering year
BC, bc, AD or ad	Era indicator without periods
B.C., b.c., A.D. ora.d.	Era indicator with periods
MONTH	English month name in uppercase
Month	Full capitalized English month name
month	Full lowercase English month name
MON	Abbreviated uppercase month name e.g., JAN, FEB, etc.
Mon	Abbreviated capitalized month name e.g, Jan, Feb, etc.
mon	Abbreviated lowercase month name e.g., jan, feb, etc.
MM	month number from 01 to 12
DAY	Full uppercase day name
Day	Full capitalized day name
day	Full lowercase day name
DY	Abbreviated uppercase day name
Dy	Abbreviated capitalized day name
dy	Abbreviated lowercase day name
DDD	Day of year (001-366)
IDDD	Day of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week)
DD	Day of month (01-31)
D	Day of the week, Sunday (1) to Saturday (7)
ID	ISO 8601 day of the week, Monday (1) to Sunday (7)
W	Week of month (1-5) (the first week starts on the first day of the month)
WW	Week number of year (1-53) (the first week starts on the first day of the year)
IW	Week number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1)
CC	Century e.g, 21, 22, etc.
J	Julian Day (integer days since November 24, 4714 BC at midnight UTC)
RM	Month in upper case Roman numerals (I–XII; I=January)
rm	Month in lowercase Roman numerals (i–xii; i=January)
HH	Hour of day (0-12)
HH12	Hour of day (0-12)
HH24	Hour of day (0-23)
MI	Minute (0-59)
SS	Second (0-59)
MS	Millisecond (000-999)
US	Microsecond (000000-999999)
SSSS	Seconds past midnight (0-86399)
AM, am, PM or pm	Meridiem indicator (without periods)
A.M., a.m., P.M. or p.m.	Meridiem indicator (with periods)

Return value

The TO_CHAR() function returns a string in TEXT data type that represents the first argument formatted according to the specified format.

Examples

We will use the payment table in the sample database for the demonstration.

payment table

A) Converting a timestamp to a string example

The following statement uses the TO_CHAR() function to format the payment date that consists of hours, minutes, and seconds:

SELECT
    payment_date,
    TO_CHAR(
        payment_date,
        'HH12:MI:SS'
    ) payment_time
FROM
    payment
ORDER BY
    payment_date;

Here is the result:

PostgreSQL TO_CHAR Function - format timestamp example

B) Converting an entire timestamp value into a different format example

The following example converts the payment date into a different format:

SELECT
    payment_id,
    payment_date,
    TO_CHAR(
        payment_date,
        'MON-DD-YYYY HH12:MIPM'
    ) payment_time
FROM
    payment
ORDER BY
    payment_date;

The output is:

C) Converting a timestamp literal to a string example

The following statement converts a timestamp literal to a string:

SELECT
    TO_CHAR(
        TIMESTAMP '2017-08-18 22:30:59',
        'HH24:MI:SS'
    );

The result is:

22:30:59

D) Adding currency symbol to the amount example

The following example adds US dollar to the paid amounts:

SELECT
    payment_id,
    amount,
    TO_CHAR(
        amount,
        'l99999D99'
    ) amount_format
FROM
    payment
ORDER BY
    payment_date;

The following picture illustrates the output:

PostgreSQL TO_CHAR Function - add currency symbol example

E) Converting an integer to a string example

The following example converts an integer to a string:

SELECT
    TO_CHAR(
        2017,
        '9,999'
    );

Result

  2,017

F) Putting it all together example

The following example converts a numeric and timestamp to a string:

SELECT
    first_name || ' ' || last_name 
    || ' paid ' || 
    TO_CHAR(
        amount,
        'l99D99'
    ) 
    || ' at ' || 
    TO_CHAR(
        payment_date,
        'HH24:MI:SS'
    )
    || ' on ' || 
    TO_CHAR(
        payment_date,
        'Mon-DD-YYYY'
    ) payment_info
FROM
    payment
INNER JOIN customer USING(customer_id)
ORDER BY
    rental_id;