1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
5
>Data Type Formatting Functions</TITLE
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="Functions and Operators"
16
HREF="functions.html"><LINK
18
TITLE="Pattern Matching"
19
HREF="functions-matching.html"><LINK
21
TITLE="Date/Time Functions and Operators"
22
HREF="functions-datetime.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="Pattern Matching"
57
HREF="functions-matching.html"
66
TITLE="Functions and Operators"
74
>Chapter 9. Functions and Operators</TD
80
TITLE="Functions and Operators"
89
TITLE="Date/Time Functions and Operators"
90
HREF="functions-datetime.html"
104
NAME="FUNCTIONS-FORMATTING"
105
>9.8. Data Type Formatting Functions</A
111
> formatting functions
112
provide a powerful set of tools for converting various data types
113
(date/time, integer, floating point, numeric) to formatted strings
114
and for converting from formatted strings to specific data types.
116
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-TABLE"
119
These functions all follow a common calling convention: the first
120
argument is the value to be formatted and the second argument is a
121
template that defines the output or input format.
124
> A single-argument <CODE
128
available; it accepts a
131
>double precision</TT
132
> argument and converts from Unix epoch
133
(seconds since 1970-01-01 00:00:00+00) to
136
>timestamp with time zone</TT
141
> Unix epochs are implicitly cast to
144
>double precision</TT
150
NAME="FUNCTIONS-FORMATTING-TABLE"
154
>Table 9-20. Formatting Functions</B
159
><COL><COL><COL><COL><THEAD
195
>convert time stamp to string</TD
199
>to_char(current_timestamp, 'HH12:MI:SS')</TT
223
>convert interval to string</TD
227
>to_char(interval '15h 2m 12s', 'HH24:MI:SS')</TT
251
>convert integer to string</TD
255
>to_char(125, '999')</TT
267
>double precision</TT
280
>convert real/double precision to string</TD
284
>to_char(125.8::real, '999D9')</TT
308
>convert numeric to string</TD
312
>to_char(-125.8, '999D99S')</TT
338
>convert string to date</TD
342
>to_date('05 Dec 2000', 'DD Mon YYYY')</TT
368
>convert string to numeric</TD
372
>to_number('12,454.8-', '99G999D9S')</TT
395
>timestamp with time zone</TT
398
>convert string to time stamp</TD
402
>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</TT
413
>double precision</TT
420
>timestamp with time zone</TT
423
>convert Unix epoch to time stamp</TD
427
>to_timestamp(1284352323)</TT
437
> output template string, there are certain
438
patterns that are recognized and replaced with appropriately-formatted
439
data based on the given value. Any text that is not a template pattern is
440
simply copied verbatim. Similarly, in an input template string (for the
441
other functions), template patterns identify the values to be supplied by
442
the input data string.
446
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE"
449
template patterns available for formatting date and time values.
454
NAME="FUNCTIONS-FORMATTING-DATETIME-TABLE"
458
>Table 9-21. Template Patterns for Date/Time Formatting</B
479
>hour of day (01-12)</TD
488
>hour of day (01-12)</TD
497
>hour of day (00-23)</TD
524
>millisecond (000-999)</TD
533
>microsecond (000000-999999)</TD
542
>seconds past midnight (0-86399)</TD
561
>meridiem indicator (without periods)</TD
580
>meridiem indicator (with periods)</TD
589
>year (4 and more digits) with comma</TD
598
>year (4 and more digits)</TD
607
>last 3 digits of year</TD
616
>last 2 digits of year</TD
625
>last digit of year</TD
634
>ISO year (4 and more digits)</TD
643
>last 3 digits of ISO year</TD
652
>last 2 digits of ISO year</TD
661
>last digit of ISO year</TD
680
>era indicator (without periods)</TD
699
>era indicator (with periods)</TD
708
>full upper case month name (blank-padded to 9 chars)</TD
717
>full capitalized month name (blank-padded to 9 chars)</TD
726
>full lower case month name (blank-padded to 9 chars)</TD
735
>abbreviated upper case month name (3 chars in English, localized lengths vary)</TD
744
>abbreviated capitalized month name (3 chars in English, localized lengths vary)</TD
753
>abbreviated lower case month name (3 chars in English, localized lengths vary)</TD
762
>month number (01-12)</TD
771
>full upper case day name (blank-padded to 9 chars)</TD
780
>full capitalized day name (blank-padded to 9 chars)</TD
789
>full lower case day name (blank-padded to 9 chars)</TD
798
>abbreviated upper case day name (3 chars in English, localized lengths vary)</TD
807
>abbreviated capitalized day name (3 chars in English, localized lengths vary)</TD
816
>abbreviated lower case day name (3 chars in English, localized lengths vary)</TD
825
>day of year (001-366)</TD
834
>ISO day of year (001-371; day 1 of the year is Monday of the first ISO week.)</TD
843
>day of month (01-31)</TD
852
>day of the week, Sunday(<TT
867
>ISO day of the week, Monday(<TT
882
>week of month (1-5) (The first week starts on the first day of the month.)</TD
891
>week number of year (1-53) (The first week starts on the first day of the year.)</TD
900
>ISO week number of year (01 - 53; the first Thursday of the new year is in week 1.)</TD
909
>century (2 digits) (The twenty-first century starts on 2001-01-01.)</TD
918
>Julian Day (days since November 24, 4714 BC at midnight)</TD
927
>quarter (ignored by <CODE
942
>month in upper case Roman numerals (I-XII; I=January)</TD
951
>month in lower case Roman numerals (i-xii; i=January)</TD
960
>upper case time-zone name</TD
969
>lower case time-zone name</TD
975
> Modifiers can be applied to any template pattern to alter its
976
behavior. For example, <TT
989
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE"
992
modifier patterns for date/time formatting.
997
NAME="FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE"
1001
>Table 9-22. Template Pattern Modifiers for Date/Time Formatting</B
1006
><COL><COL><COL><THEAD
1024
>fill mode (suppress padding blanks and trailing zeroes)</TD
1038
>upper case ordinal number suffix</TD
1055
>lower case ordinal number suffix</TD
1072
>fixed format global option (see usage notes)</TD
1076
>FX Month DD Day</TT
1086
>translation mode (print localized day and month names based on
1088
HREF="runtime-config-client.html#GUC-LC-TIME"
1104
>spell mode (not implemented)</TD
1115
> Usage notes for date/time formatting:
1125
> suppresses leading zeroes and trailing blanks
1126
that would otherwise be added to make the output of a pattern be
1127
fixed-width. In <SPAN
1134
> modifies only the next specification, while in
1138
> affects all subsequent
1139
specifications, and repeated <TT
1143
toggle fill mode on and off.
1151
> does not include trailing blanks.
1163
skip multiple blank spaces in the input string unless the
1167
> option is used. For example,
1170
>to_timestamp('2000 JUN', 'YYYY MON')</TT
1174
>to_timestamp('2000 JUN', 'FXYYYY MON')</TT
1179
> expects one space only.
1183
> must be specified as the first item in
1189
> Ordinary text is allowed in <CODE
1193
templates and will be output literally. You can put a substring
1194
in double quotes to force it to be interpreted as literal text
1195
even if it contains pattern key words. For example, in
1198
>'"Hello Year "YYYY'</TT
1203
will be replaced by the year data, but the single <TT
1210
will not be. In <CODE
1220
>, double-quoted strings skip the number of
1221
input characters contained in the string, e.g. <TT
1225
skips two input characters.
1230
> If you want to have a double quote in the output you must
1231
precede it with a backslash, for example <TT
1236
(Two backslashes are necessary because the backslash
1237
has special meaning when using the escape string syntax.)
1245
> conversion from string to <TT
1252
> has a restriction when processing years with more than 4 digits. You must
1253
use some non-digit character or template after <TT
1257
otherwise the year is always interpreted as 4 digits. For example
1258
(with the year 20000):
1261
>to_date('200001131', 'YYYYMMDD')</TT
1263
interpreted as a 4-digit year; instead use a non-digit
1264
separator after the year, like
1267
>to_date('20000-1131', 'YYYY-MMDD')</TT
1271
>to_date('20000Nov31', 'YYYYMonDD')</TT
1277
> In conversions from string to <TT
1287
> (century) field is ignored
1308
> then the year is computed
1317
> An ISO week date (as distinct from a Gregorian date) can be
1325
> in one of two ways:
1331
> Year, week, and weekday: for example <TT
1333
>to_date('2006-42-4',
1339
>. If you omit the weekday it
1340
is assumed to be 1 (Monday).
1345
> Year and day of year: for example <TT
1347
>to_date('2006-291',
1359
> Attempting to construct a date using a mixture of ISO week and
1360
Gregorian date fields is nonsensical, and will cause an error. In the
1361
context of an ISO year, the concept of a <SPAN
1368
> has no meaning. In the context of a Gregorian year, the
1369
ISO week has no meaning. Users should avoid mixing Gregorian and
1370
ISO date specifications.
1375
> In a conversion from string to <TT
1382
>) or microsecond (<TT
1386
values are used as the
1387
seconds digits after the decimal point. For example
1390
>to_timestamp('12:3', 'SS:MS')</TT
1391
> is not 3 milliseconds,
1392
but 300, because the conversion counts it as 12 + 0.3 seconds.
1393
This means for the format <TT
1407
same number of milliseconds. To get three milliseconds, one must use
1411
>, which the conversion counts as
1412
12 + 0.003 = 12.003 seconds.
1419
>to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')</TT
1421
is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
1422
1230 microseconds = 2.021230 seconds.
1429
>to_char(..., 'ID')</CODE
1430
>'s day of the week numbering
1433
>extract(isodow from ...)</CODE
1437
>to_char(..., 'D')</CODE
1441
>extract(dow from ...)</CODE
1449
>to_char(interval)</CODE
1457
> as shown on a 12-hour clock, i.e. zero hours
1458
and 36 hours output as <TT
1465
outputs the full hour value, which can exceed 23 for intervals.
1473
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-NUMERIC-TABLE"
1476
template patterns available for formatting numeric values.
1481
NAME="FUNCTIONS-FORMATTING-NUMERIC-TABLE"
1485
>Table 9-23. Template Patterns for Numeric Formatting</B
1506
>value with the specified number of digits</TD
1515
>value with leading zeros</TD
1533
>group (thousand) separator</TD
1542
>negative value in angle brackets</TD
1551
>sign anchored to number (uses locale)</TD
1560
>currency symbol (uses locale)</TD
1569
>decimal point (uses locale)</TD
1578
>group separator (uses locale)</TD
1587
>minus sign in specified position (if number < 0)</TD
1596
>plus sign in specified position (if number > 0)</TD
1605
>plus/minus sign in specified position</TD
1614
>Roman numeral (input between 1 and 3999)</TD
1626
>ordinal number suffix</TD
1635
>shift specified number of digits (see notes)</TD
1644
>exponent for scientific notation</TD
1650
> Usage notes for numeric formatting:
1657
> A sign formatted using <TT
1667
> is not anchored to
1668
the number; for example,
1671
>to_char(-12, 'MI9999')</TT
1674
>'- 12'</TT
1678
>to_char(-12, 'S9999')</TT
1681
>' -12'</TT
1683
The Oracle implementation does not allow the use of
1706
> results in a value with the same number of
1707
digits as there are <TT
1711
not available it outputs a space.
1719
> does not convert values less than zero
1720
and does not convert fractional numbers.
1748
multiplies the input values by
1763
> is the number of digits following
1771
> does not support the use of
1775
> combined with a decimal point
1787
> (scientific notation) cannot be used in
1788
combination with any of the other formatting patterns or
1789
modifiers other than digit and decimal point patterns, and must be at the end of the format string
1793
> is a valid pattern).
1800
> Certain modifiers can be applied to any template pattern to alter its
1801
behavior. For example, <TT
1814
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-NUMERICMOD-TABLE"
1817
modifier patterns for numeric formatting.
1822
NAME="FUNCTIONS-FORMATTING-NUMERICMOD-TABLE"
1826
>Table 9-24. Template Pattern Modifiers for Numeric Formatting</B
1831
><COL><COL><COL><THEAD
1849
>fill mode (suppress padding blanks and trailing zeroes)</TD
1863
>upper case ordinal number suffix</TD
1877
>lower case ordinal number suffix</TD
1889
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE"
1892
examples of the use of the <CODE
1900
NAME="FUNCTIONS-FORMATTING-EXAMPLES-TABLE"
1925
>to_char(current_timestamp, 'Day, DD HH12:MI:SS')</TT
1930
>'Tuesday , 06 05:39:18'</TT
1937
>to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')</TT
1942
>'Tuesday, 6 05:39:18'</TT
1949
>to_char(-0.1, '99.99')</TT
1954
>' -.10'</TT
1961
>to_char(-0.1, 'FM9.99')</TT
1973
>to_char(0.1, '0.9')</TT
1985
>to_char(12, '9990999.9')</TT
1990
>' 0012.0'</TT
1997
>to_char(12, 'FM9990999.9')</TT
2009
>to_char(485, '999')</TT
2021
>to_char(-485, '999')</TT
2033
>to_char(485, '9 9 9')</TT
2038
>' 4 8 5'</TT
2045
>to_char(1485, '9,999')</TT
2057
>to_char(1485, '9G999')</TT
2062
>' 1 485'</TT
2069
>to_char(148.5, '999.999')</TT
2074
>' 148.500'</TT
2081
>to_char(148.5, 'FM999.999')</TT
2093
>to_char(148.5, 'FM999.990')</TT
2105
>to_char(148.5, '999D999')</TT
2110
>' 148,500'</TT
2117
>to_char(3148.5, '9G999D999')</TT
2122
>' 3 148,500'</TT
2129
>to_char(-485, '999S')</TT
2141
>to_char(-485, '999MI')</TT
2153
>to_char(485, '999MI')</TT
2165
>to_char(485, 'FM999MI')</TT
2177
>to_char(485, 'PL999')</TT
2189
>to_char(485, 'SG999')</TT
2201
>to_char(-485, 'SG999')</TT
2213
>to_char(-485, '9SG99')</TT
2225
>to_char(-485, '999PR')</TT
2237
>to_char(485, 'L999')</TT
2249
>to_char(485, 'RN')</TT
2254
>' CDLXXXV'</TT
2261
>to_char(485, 'FMRN')</TT
2273
>to_char(5.2, 'FMRN')</TT
2285
>to_char(482, '999th')</TT
2297
>to_char(485, '"Good number:"999')</TT
2302
>'Good number: 485'</TT
2309
>to_char(485.8, '"Pre:"999" Post:" .999')</TT
2314
>'Pre: 485 Post: .800'</TT
2321
>to_char(12, '99V999')</TT
2333
>to_char(12.4, '99V999')</TT
2345
>to_char(12.45, '99V9')</TT
2357
>to_char(0.0004859, '9.99EEEE')</TT
2374
SUMMARY="Footer navigation table"
2385
HREF="functions-matching.html"
2403
HREF="functions-datetime.html"
2413
>Pattern Matching</TD
2419
HREF="functions.html"
2427
>Date/Time Functions and Operators</TD
b'\\ No newline at end of file'