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 the 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
The last 3 digits of ISO 8601 week-numbering year
YY
last 3 digits of the year
Y
last 2 digits of the year
IYYY
ISO 8601 week-numbering year (4 or more digits)
IYY
The last 2 digits of ISO 8601 week-numbering year
IY
The last digit of ISO 8601 week-numbering year
I
Abbreviated lowercase month name e.g., Jan, feb, etc.
BC, bc, AD or ad
Era indicator without periods
B.C., b.c., A.D. or a.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
Week number of the year (1-53) (the first week starts on the first day of the year)
mon
Abbreviated capitalized 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
Century e.g., 21, 22, etc.
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; >
rm
Month in lowercase Roman numerals (i-xii; >
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.
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 paymentORDER 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:
