PostgreSQL TO_CHAR() Function

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

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:

The following table shows the valid timestamp format strings:

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.

1) 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:

2) 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:

3) 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

4) 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:

5) 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

6) 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;

The result is

Summary

• Use the PostgreSQL TO_CHAR() function to convert a timestamp or a numeric value to a string.