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:

FormatDescription
9Numeric value with the specified number of digits
0Numeric value with leading zeros
. (period)decimal point
Ddecimal point that uses locale
, (comma)group (thousand) separator
FMFill mode, which suppresses padding blanks and leading zeroes.
PRNegative value in angle brackets.
SSign anchored to a number that uses locale
LCurrency symbol that uses locale
GGroup separator that uses locale
MIMinus sign in the specified position for numbers that are less than 0.
PLPlus sign in the specified position for numbers that are greater than 0.
SGPlus / minus sign in the specified position
RNRoman numeral that ranges from 1 to 3999
TH or thUpper case or lower case ordinal number suffix

The following table shows the valid timestamp format strings:

PatternDescription
Y,YYYyear in 4 digits with comma
YYYYyear in 4 digits
YYYlast 3 digits of year
YYlast 2 digits of year
YThe last digit of year
IYYYISO 8601 week-numbering year (4 or more digits)
IYYLast 3 digits of ISO 8601 week-numbering year
IYLast 2 digits of ISO 8601 week-numbering year
ILast digit of ISO 8601 week-numbering year
BC, bc, AD or adEra indicator without periods
B.C., b.c., A.D. ora.d.Era indicator with periods
MONTHEnglish month name in uppercase
MonthFull capitalized English month name
monthFull lowercase English month name
MONAbbreviated uppercase month name e.g., JAN, FEB, etc.
MonAbbreviated capitalized month name e.g, Jan, Feb,  etc.
monAbbreviated lowercase month name e.g., jan, feb, etc.
MMmonth number from 01 to 12
DAYFull uppercase day name
DayFull capitalized day name
dayFull lowercase day name
DYAbbreviated uppercase day name
DyAbbreviated capitalized day name
dyAbbreviated lowercase day name
DDDDay of year (001-366)
IDDDDay of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week)
DDDay of month (01-31)
DDay of the week, Sunday (1) to Saturday (7)
IDISO 8601 day of the week, Monday (1) to Sunday (7)
WWeek of month (1-5) (the first week starts on the first day of the month)
WWWeek number of year (1-53) (the first week starts on the first day of the year)
IWWeek number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1)
CCCentury e.g, 21, 22, etc.
JJulian Day (integer days since November 24, 4714 BC at midnight UTC)
RMMonth in upper case Roman numerals (I-XII; >
rmMonth in lowercase Roman numerals (i-xii; >
HHHour of day (0-12)
HH12Hour of day (0-12)
HH24Hour of day (0-23)
MIMinute (0-59)
SSSecond (0-59)
MSMillisecond (000-999)
USMicrosecond (000000-999999)
SSSSSeconds past midnight (0-86399)
AM, am, PM or pmMeridiem 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;Code language: JavaScript (javascript)

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;Code language: JavaScript (javascript)

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'
    );Code language: JavaScript (javascript)

The result is:

22:30:59Code language: CSS (css)

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;Code language: JavaScript (javascript)

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'
    );Code language: JavaScript (javascript)

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;Code language: JavaScript (javascript)

The result is

PostgreSQL TO_CHAR Function - example

Leave a Reply

Your email address will not be published. Required fields are marked *