Formatting Functions

Author: Written by Karel Zak on 2000-01-24.

The Postgres formatting functions provide a powerful set of tools for converting various datetypes (date/time, int, float, numeric) to formatted strings and for converting from formatted strings to specific datetypes.

Note: The second argument for all formatting functions is a template to be used for the conversion.

Table 5-7. Formatting Functions

to_char(timestamp, text)textconvert timestamp to stringto_char(timestamp 'now','HH12:MI:SS')
to_char(int, text)textconvert int4/int8 to stringto_char(125, '999')
to_char(float, text)textconvert float4/float8 to stringto_char(125.8, '999D9')
to_char(numeric, text)textconvert numeric to stringto_char(numeric '-125.8', '999D99S')
to_date(text, text)dateconvert string to dateto_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)dateconvert string to timestampto_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)numericconvert string to numericto_number('12,454.8-', '99G999D9S')

Table 5-8. Templates for date/time conversions

HHhour of day (01-12)
HH12hour of day (01-12)
HH24hour of day (00-23)
MIminute (00-59)
SSsecond (00-59)
SSSSseconds past midnight (0-86399)
AM or A.M. or PM or P.M.meridian indicator (upper case)
am or a.m. or pm or p.m.meridian indicator (lower case)
Y,YYYyear (4 and more digits) with comma
YYYYyear (4 and more digits)
YYYlast 3 digits of year
YYlast 2 digits of year
Ylast digit of year
BC or B.C. or AD or A.D.year indicator (upper case)
bc or b.c. or ad or a.d.year indicator (lower case)
MONTHfull upper case month name (9 chars)
Monthfull mixed case month name (9 chars)
monthfull lower case month name (9 chars)
MONupper case abbreviated month name (3 chars)
Monabbreviated mixed case month name (3 chars)
monabbreviated lower case month name (3 chars)
MMmonth (01-12)
DAYfull upper case day name (9 chars)
Dayfull mixed case day name (9 chars)
dayfull lower case day name (9 chars)
DYabbreviated upper case day name (3 chars)
Dyabbreviated mixed case day name (3 chars)
dyabbreviated lower case day name (3 chars)
DDDday of year (001-366)
DDday of month (01-31)
Dday of week (1-7; SUN=1)
Wweek of month
WWweek number of year
CCcentury (2 digits)
JJulian Day (days since January 1, 4712 BC)
RMmonth in Roman Numerals (I-XII; I=JAN) - upper case
rmmonth in Roman Numerals (I-XII; I=JAN) - lower case

All templates allow the use of prefix and suffix modifiers. Modifiers are always valid for use in templates. The prefix 'FX' is a global modifier only.

Table 5-9. Suffixes for templates for date/time to_char()

FMfill mode prefixFMMonth
THupper ordinal number suffixDDTH
thlower ordinal number suffixDDTH
FXFiXed format global option (see below)FX Month DD Day
SPspell mode (not yet implemented)DDSP

Usage notes:

Table 5-10. Templates for to_char(numeric)

9value with the specified number of digits
0value with leading zeros
. (period)decimal point
, (comma)group (thousand) separator
PRnegative value in angle brackets
Snegative value with minus sign (use locales)
Lcurrency symbol (use locales)
Ddecimal point (use locales)
Ggroup separator (use locales)
MIminus sign on specified position (if number < 0)
PLplus sign on specified position (if number > 0)
SGplus/minus sign on specified position
RNroman numeral (input between 1 and 3999)
TH or thconvert to ordinal number
VShift n digits (see notes)
EEEEscience numbers. Now not supported.

Usage notes:

Table 5-11. to_char Examples

to_char(now(),'Day, HH12:MI:SS')'Tuesday , 05:39:18'
to_char(now(),'FMDay, HH12:MI:SS')'Tuesday, 05:39:18'
to_char(-0.1,'99.99')' -.10'
to_char(0.1,'0.9')' 0.1'
to_char(12,'9990999.9')' 0012.0'
to_char(485,'999')' 485'
to_char(485,'9 9 9')' 4 8 5'
to_char(1485,'9,999')' 1,485'
to_char(1485,'9G999')' 1 485'
to_char(148.5,'999.999')' 148.500'
to_char(148.5,'999D999')' 148,500'
to_char(3148.5,'9G999D999')' 3 148,500'
to_char(485,'L999')'DM 485
to_char(485,'RN')' CDLXXXV'
to_char(482,'999th')' 482nd'
to_char(485, '"Good number:"999')'Good number: 485'
to_char(485.8,'"Pre-decimal:"999" Post-decimal:" .999')'Pre-decimal: 485 Post-decimal: .800'
to_char(12,'99V999')' 12000'
to_char(12.4,'99V999')' 12400'
to_char(12.45, '99V9')' 125'