Вся предоставленная на этом сервере информация собрана нами из разных источников. Если Вам кажется, что публикация каких-то документов нарушает чьи-либо авторские права, сообщите нам об этом.
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
Function
Returns
Description
Example
to_char(timestamp, text)
text
convert timestamp to string
to_char(timestamp 'now','HH12:MI:SS')
to_char(int, text)
text
convert int4/int8 to string
to_char(125, '999')
to_char(float, text)
text
convert float4/float8 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)
date
convert string to timestamp
to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)
numeric
convert string to numeric
to_number('12,454.8-', '99G999D9S')
Table 5-8. Templates for date/time conversions
Template
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)
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.
year indicator (upper case)
bc or b.c. or ad or a.d.
year indicator (lower case)
MONTH
full upper case month name (9 chars)
Month
full mixed case month name (9 chars)
month
full lower case month name (9 chars)
MON
upper case abbreviated month name (3 chars)
Mon
abbreviated mixed case month name (3 chars)
mon
abbreviated lower case month name (3 chars)
MM
month (01-12)
DAY
full upper case day name (9 chars)
Day
full mixed case day name (9 chars)
day
full lower case day name (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
WW
week number of year
CC
century (2 digits)
J
Julian Day (days since January 1, 4712 BC)
Q
quarter
RM
month in Roman Numerals (I-XII; I=JAN) - upper case
rm
month 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()
Suffix
Description
Example
FM
fill mode prefix
FMMonth
TH
upper ordinal number suffix
DDTH
th
lower ordinal number suffix
DDTH
FX
FiXed format global option (see below)
FX Month DD Day
SP
spell mode (not yet implemented)
DDSP
Usage notes:
to_timestamp and to_date
skip blank space if the FX option is
not used. FX must be specified as the first item
in the template.
Backslash ("\") must be specified with a double backslash
("\\"); for example '\\HH\\MI\\SS'.
A double quote ('"') between quotation marks is skipped and is not parsed.
If you want to write a double quote to output you must preceed
it with a double backslash ('\\"), for
example '\\"YYYY Month\\"'.
to_char supports text without a leading
double quote ('"'), but any string
between a quotation marks is rapidly handled and you are
guaranteed that it will not be interpreted as a template
keyword (example: '"Hello Year: "YYYY').
Table 5-10. Templates for to_char(numeric)
Template
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 (use locales)
L
currency symbol (use locales)
D
decimal point (use locales)
G
group separator (use locales)
MI
minus sign on specified position (if number < 0)
PL
plus sign on specified position (if number > 0)
SG
plus/minus sign on specified position
RN
roman numeral (input between 1 and 3999)
TH or th
convert to ordinal number
V
Shift n digits (see
notes)
EEEE
science numbers. Now not supported.
Usage notes:
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 preceeds
MI.
PL, SG, and
TH are Postgres
extensions.
9 specifies a value with the same number of
digits as there are 9s. If a digit is
not available use blank space.
TH does not convert values less than zero
and does not convert decimal numbers. TH is
a Postgres extension.
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).
