The PostgreSQL formatting functions
provide a powerful set of tools for converting various data types
(date/time, integer, floating point, numeric) to formatted strings
and for converting from formatted strings to specific data types.
Table 6-12 lists them.
These functions all follow a common calling convention: the first
argument is the value to be formatted and the second argument is a
template that defines the output or input format.
Table 6-12. Formatting Functions
Function
Returns
Description
Example
to_char(timestamp, text)
text
convert time stamp to string
to_char(timestamp 'now','HH12:MI:SS')
to_char(interval, text)
text
convert interval to string
to_char(interval '15h 2m 12s','HH24:MI:SS')
to_char(int, text)
text
convert integer to string
to_char(125, '999')
to_char(double precision,
text)
text
convert real/double precision to string
to_char(125.8, '999D9')
to_char(numeric, text)
text
convert numeric to string
to_char(numeric '-125.8', '999D99S')
to_date(text, text)
date
convert string to date
to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)
timestamp
convert string to time stamp
to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)
numeric
convert string to numeric
to_number('12,454.8-', '99G999D9S')
In an output template string, there are certain patterns that are
recognized and replaced with appropriately-formatted data from the value
to be formatted. Any text that is not a template pattern is simply
copied verbatim. Similarly, in an input template string, template patterns
identify the parts of the input data string to be looked at and the
values to be found there.
Table 6-13 shows the
template patterns available for formatting date and time values.
Table 6-13. Template patterns for date/time conversions
Pattern
Description
HH
hour of day (01-12)
HH12
hour of day (01-12)
HH24
hour of day (00-23)
MI
minute (00-59)
SS
second (00-59)
MS
millisecond (000-999)
US
microsecond (000000-999999)
SSSS
seconds 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,YYY
year (4 and more digits) with comma
YYYY
year (4 and more digits)
YYY
last 3 digits of year
YY
last 2 digits of year
Y
last digit of year
BC or B.C. or
AD or A.D.
era indicator (upper case)
bc or b.c. or
ad or a.d.
era indicator (lower case)
MONTH
full upper case month name (blank-padded to 9 chars)
Month
full mixed case month name (blank-padded to 9 chars)
month
full lower case month name (blank-padded to 9 chars)
MON
abbreviated upper case month name (3 chars)
Mon
abbreviated mixed case month name (3 chars)
mon
abbreviated lower case month name (3 chars)
MM
month number (01-12)
DAY
full upper case day name (blank-padded to 9 chars)
Day
full mixed case day name (blank-padded to 9 chars)
day
full lower case day name (blank-padded to 9 chars)
DY
abbreviated upper case day name (3 chars)
Dy
abbreviated mixed case day name (3 chars)
dy
abbreviated lower case day name (3 chars)
DDD
day of year (001-366)
DD
day of month (01-31)
D
day of week (1-7; SUN=1)
W
week of month (1-5) where first week start on the first day of the month
WW
week number of year (1-53) where first week start on the first day of the year
IW
ISO week number of year (The first Thursday of the new year is in week 1.)
CC
century (2 digits)
J
Julian Day (days since January 1, 4712 BC)
Q
quarter
RM
month in Roman Numerals (I-XII; I=January) - upper case
rm
month in Roman Numerals (I-XII; I=January) - lower case
TZ
time-zone name - upper case
tz
time-zone name - lower case
Certain modifiers may be applied to any template pattern to alter its
behavior. For example, "FMMonth"
is the "Month" pattern with the
"FM" prefix.
Table 6-14 shows the
modifier patterns for date/time formatting.
Table 6-14. Template pattern modifiers for date/time conversions
Modifier
Description
Example
FM prefix
fill mode (suppress padding blanks and zeroes)
FMMonth
TH suffix
add upper-case ordinal number suffix
DDTH
th suffix
add lower-case ordinal number suffix
DDth
FX prefix
fixed format global option (see usage notes)
FX Month DD Day
SP suffix
spell mode (not yet implemented)
DDSP
Usage notes for the date/time formatting:
FM suppresses leading zeroes and trailing blanks
that would otherwise be added to make the output of a pattern be
fixed-width.
to_timestamp and to_date
skip multiple blank spaces in the input string if the FX option
is not used. FX must be specified as the first item
in the template; for example
to_timestamp('2000 JUN','YYYY MON') is right, but
to_timestamp('2000 JUN','FXYYYY MON') returns an error,
because to_timestamp expects one blank space only.
If a backslash ("\") is desired
in a string constant, a double backslash
("\\") must be entered; for
example '\\HH\\MI\\SS'. This is true for
any string constant in PostgreSQL.
Ordinary text is allowed in to_char
templates and will be output literally. You can put a substring
in double quotes to force it to be interpreted as literal text
even if it contains pattern keywords. For example, in
'"Hello Year "YYYY', the YYYY
will be replaced by the year data, but the single Y in "Year"
will not be.
If you want to have a double quote in the output you must
precede it with a backslash, for example '\\"YYYY
Month\\"'.
YYYY conversion from string to timestamp or
date is restricted if you use a year with more than 4 digits. You must
use some non-digit character or template after YYYY,
otherwise the year is always interpreted as 4 digits. For example
(with year 20000):
to_date('200001131', 'YYYYMMDD') will be
interpreted as a 4-digit year; better is to use a non-digit
separator after the year, like
to_date('20000-1131', 'YYYY-MMDD') or
to_date('20000Nov31', 'YYYYMonDD').
Millisecond MS and microsecond US
values in a conversion from string to time stamp are used as part of the
seconds after the decimal point. For example
to_timestamp('12:3', 'SS:MS') is not 3 milliseconds,
but 300, because the conversion counts it as 12 + 0.3.
This means for the format SS:MS, the input values
12:3, 12:30, and 12:300 specify the
same number of milliseconds. To get three milliseconds, one must use
12:003, which the conversion counts as
12 + 0.003 = 12.003 seconds.
Here is a more
complex example:
to_timestamp('15:12:02.020.001230','HH:MI:SS.MS.US')
is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
1230 microseconds = 2.021230 seconds.
Table 6-15 shows the
template patterns available for formatting numeric values.
Table 6-15. Template patterns for numeric conversions
Pattern
Description
9
value with the specified number of digits
0
value with leading zeros
. (period)
decimal point
, (comma)
group (thousand) separator
PR
negative value in angle brackets
S
negative value with minus sign (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 numeral (input between 1 and 3999)
TH or th
convert to ordinal number
V
shift n digits (see
notes)
EEEE
scientific notation (not implemented yet)
Usage notes for the numeric formatting:
A sign formatted using SG, PL, or
MI is not an anchor in
the number; for example,
to_char(-12, 'S9999') produces ' -12',
but to_char(-12, 'MI9999') produces '- 12'.
The Oracle implementation does not allow the use of
MI ahead of 9, but rather
requires that 9 precede
MI.
9 specifies a value with the same number of
digits as there are 9s. If a digit is
not available it outputs a space.
TH does not convert values less than zero
and does not convert decimal numbers.
PL, SG, and
TH are PostgreSQL
extensions.
V effectively
multiplies the input values by
10^n, where
n is the number of digits following
V.
to_char does not support the use of
V combined with a decimal point.
(E.g., 99.9V99 is not allowed.)
Table 6-16 shows some
examples of the use of the to_char function.
