PostgreSQL to_char() Function

The PostgreSQL to_char() function converts timestamp values, interval values, or numeric values ​​to strings according to the specified format and return the result.

to_char() Syntax

This is the syntax of the PostgreSQL to_char () function:

to_char(value, format TEXT) -> TEXT

Parameters

value

Required. A value to be convert to a string. It can be used for values ​​of types: TIMESTAMP, TIMESTAMPTZ, INTERVAL, INTEGER, BIGINT, NUMERIC, REAL, DOUBLE.

format

Required. The format used for converting this value. Templates consist of some placeholders and modifiers. Placeholders define the format of the output string, and modifiers can change the behavior of the template.

Date/Time Format Template

You can use the template patterns in the following table to construct date/time format templates.

Pattern Description
HH Hour of the day (01–12)
HH12 Hour of the day (01–12)
HH24 Hour of the day (00–23)
MI Minutes (00–59)
SS seconds (00–59)
MS milliseconds (000–999)
US Microseconds (000000–999999)
FF1 tenths of a second (0–9)
FF2 hundredths of a second (00–99)
FF3 milliseconds (000–999)
FF4 Tenth of a millisecond (0000–9999)
FF5 hundredths of a millisecond (00000–99999)
FF6 Microseconds (000000–999999)
SSSS,SSSSS Seconds after midnight (0–86399)
AM, am, PM,pm AM, PM (no period)
A.M., a.m., P.M.,p.m. AM, PM (with periods)
Y,YYY Year (4 or more digits) with commas
YYYY year (4 or more digits)
YYY Last 3 digits of year
YY Last 2 digits of year
Y last digit of year
IYYY ISO 8601 week number year (4 or more digits)
IYY Last 3 digits of ISO 8601 week number year
IY Last 2 digits of ISO 8601 week number year
I ISO 8601 week number last digit of year
BC, bc, AD,ad BC, AD (no period)
B.C., b.c., A.D.,a.d. BC, AD (with periods)
MONTH All-caps month name (blank padded to 9 characters)
Month All-caps month name (blank padded to 9 characters)
month All lowercase month name (blank padded to 9 characters)
MON Abbreviated uppercase month name (3 characters in English, localized lengths vary)
Mon Abbreviated uppercase month name (3 characters in English, localized lengths vary)
mon Abbreviated lowercase month name (3 characters in English, localized lengths vary)
MM Number of months (01–12)
DAY Date name in all caps (blank padded to 9 characters)
Day Date name in all caps (blank padded to 9 characters)
day All lowercase date name (9 characters padded with blanks)
DY Abbreviated uppercase date name (3 English characters, localized lengths vary)
Dy Abbreviated uppercase date name (3 English characters, localized lengths vary)
dy Abbreviated lowercase date name (3 English characters, localized lengths vary)
DDD day of the year (001–366)
IDDD ISO 8601 week number day of the year (001–371; the 1st day of the year is the Monday of the first ISO week)
DD day of the 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 the month (1–5) (the first week starts on the first day of the month)
WW Week number of the year (1–53) (the first week starts on the first day of the year)
IW ISO 8601 week number Week number of the year (01–53; the first Thursday of the year is in week 1)
CC Century (2 digits) (21st century started on 2001-01-01)
J Julian calendar date (integer days since local midnight on November 24, 4714 BC)
Q quarter
RM Month in uppercase Roman numerals (I to XII; I is January)
rm Month in lowercase Roman numerals (i to xii; i is January)
TZ Uppercase time zone abbreviation ( to_char supported )
tz Lowercase time zone abbreviation (only to_char supported )
TZH time zone hours
TZM time zone minutes
OF Timezone offset from UTC ( to_char supported )

You can use the modifiers in the following table with date/time template patterns.

Modifier Description
FM prefix fill mode (suppress leading zeroes and padding blanks)
TH suffix upper case ordinal number suffix
th suffix lower case ordinal number suffix
FX prefix fixed format global option (see usage notes)
TM prefix translation mode (use localized day and month names based on lc_time)
SP suffix spell mode (not implemented)

Number Format Template

You can use the template patterns in the following table to construct numeric format strings.

Pattern Description
9 digit position (can be discarded if it doesn’t matter)
0 digit position (will not be removed, even if it doesn’t matter)
. decimal point
, group (thousands) separator
PR negative values in angle brackets
S currency symbol (uses locale)
L Currency symbol (uses locale)
D decimal point (uses locale)
G group separator (uses locale)
MI minus sign in specified position (if number < 0)
PL plus sign in specified position (if number > 0)
SG plus/minus sign in specified position
RN Roman numerals (between 1 and 3999)
TH,th ordinal number suffix
V shift specified number of digits
EEEE exponent for scientific notation

You can use the modifiers in the following table within numeric format strings.

Modifier Description
FM prefix fill mode (suppress trailing zeroes and padding blanks)
TH suffix upper case ordinal number suffix
th suffix lower case ordinal number suffix

Return value

The PostgreSQL to_char() function returns a string converted from the specified timestamp, interval, or number according to the specified format.

to_char() Examples

This following example shows how to use the PostgreSQL to_char() function to output a timestamp value in a specified format.

SELECT to_char(timestamp '2022-05-17 17:31:12.112', 'YYYY/MM/DD HH12:MI:SS');

       to_char
---------------------
 2022/05/17 05:31:12

This following example shows how to use the PostgreSQL to_char() function to output a date value in a specified format.

SELECT to_char(date '2022-05-17', 'YYYY/MM/DD');

  to_char
------------
 2022/05/17

This following example shows how to use the PostgreSQL to_char() function to output an interval value in a specified format.

SELECT to_char(interval '20 hours 20 minutes', 'HH24:MI:SS');

 to_char
----------
 20:20:00

This following example shows how to use the PostgreSQL to_char() function to output a number in a specified format.

SELECT to_char(12345678.9, '999G999G999G999G999.900');

         to_char
--------------------------
           12,345,678.900

Since the format string is logger that the number, so the output is lef-padded with spaces. You can remove the leading spaces by using the modifier FM, for example:

SELECT to_char(12345678.9, 'FM999G999G999G999G999.900');

    to_char
----------------
 12,345,678.900