1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Data Type Formatting Functions</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 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
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
51 HREF="functions-matching.html"
67 >Chapter 9. Functions and Operators</TD
81 HREF="functions-datetime.html"
95 NAME="FUNCTIONS-FORMATTING"
96 >9.7. Data Type Formatting Functions</A
108 > formatting functions
109 provide a powerful set of tools for converting various data types
110 (date/time, integer, floating point, numeric) to formatted strings
111 and for converting from formatted strings to specific data types.
113 HREF="functions-formatting.html#FUNCTIONS-FORMATTING-TABLE"
116 These functions all follow a common calling convention: the first
117 argument is the value to be formatted and the second argument is a
118 template that defines the output or input format.
123 NAME="FUNCTIONS-FORMATTING-TABLE"
127 >Table 9-20. Formatting Functions</B
132 ><COL><COL><COL><COL><THEAD
166 >convert time stamp to string</TD
170 >to_char(current_timestamp, 'HH12:MI:SS')</TT
194 >convert interval to string</TD
198 >to_char(interval '15h 2m 12s', 'HH24:MI:SS')</TT
222 >convert integer to string</TD
226 >to_char(125, '999')</TT
238 >double precision</TT
251 >convert real/double precision to string</TD
255 >to_char(125.8::real, '999D9')</TT
279 >convert numeric to string</TD
283 >to_char(-125.8, '999D99S')</TT
307 >convert string to date</TD
311 >to_date('05 Dec 2000', 'DD Mon YYYY')</TT
335 >convert string to time stamp</TD
339 >to_timestamp('05 Dec 2000', 'DD Mon YYYY')</TT
363 >convert string to numeric</TD
367 >to_number('12,454.8-', '99G999D9S')</TT
387 is deprecated and should not be used in newly-written code. It will be removed in the next version.
390 > In an output template string (for <CODE
393 >), there are certain patterns that are
394 recognized and replaced with appropriately-formatted data from the value
395 to be formatted. Any text that is not a template pattern is simply
396 copied verbatim. Similarly, in an input template string (for anything but <CODE
399 >), template patterns
400 identify the parts of the input data string to be looked at and the
401 values to be found there.
405 HREF="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE"
408 template patterns available for formatting date and time values.
413 NAME="FUNCTIONS-FORMATTING-DATETIME-TABLE"
417 >Table 9-21. Template Patterns for Date/Time Formatting</B
438 >hour of day (01-12)</TD
447 >hour of day (01-12)</TD
456 >hour of day (00-23)</TD
483 >millisecond (000-999)</TD
492 >microsecond (000000-999999)</TD
501 >seconds past midnight (0-86399)</TD
520 >meridian indicator (upper case)</TD
539 >meridian indicator (lower case)</TD
548 >year (4 and more digits) with comma</TD
557 >year (4 and more digits)</TD
566 >last 3 digits of year</TD
575 >last 2 digits of year</TD
584 >last digit of year</TD
603 >era indicator (upper case)</TD
622 >era indicator (lower case)</TD
631 >full upper-case month name (blank-padded to 9 chars)</TD
640 >full mixed-case month name (blank-padded to 9 chars)</TD
649 >full lower-case month name (blank-padded to 9 chars)</TD
658 >abbreviated upper-case month name (3 chars)</TD
667 >abbreviated mixed-case month name (3 chars)</TD
676 >abbreviated lower-case month name (3 chars)</TD
685 >month number (01-12)</TD
694 >full upper-case day name (blank-padded to 9 chars)</TD
703 >full mixed-case day name (blank-padded to 9 chars)</TD
712 >full lower-case day name (blank-padded to 9 chars)</TD
721 >abbreviated upper-case day name (3 chars)</TD
730 >abbreviated mixed-case day name (3 chars)</TD
739 >abbreviated lower-case day name (3 chars)</TD
748 >day of year (001-366)</TD
757 >day of month (01-31)</TD
766 >day of week (1-7; Sunday is 1)</TD
775 >week of month (1-5) (The first week starts on the first day of the month.)</TD
784 >week number of year (1-53) (The first week starts on the first day of the year.)</TD
793 >ISO week number of year (The first Thursday of the new year is in week 1.)</TD
802 >century (2 digits)</TD
811 >Julian Day (days since January 1, 4712 BC)</TD
829 >month in Roman numerals (I-XII; I=January) (upper case)</TD
838 >month in Roman numerals (i-xii; i=January) (lower case)</TD
847 >time-zone name (upper case)</TD
856 >time-zone name (lower case)</TD
862 > Certain modifiers may be applied to any template pattern to alter its
863 behavior. For example, <TT
876 HREF="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE"
879 modifier patterns for date/time formatting.
884 NAME="FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE"
888 >Table 9-22. Template Pattern Modifiers for Date/Time Formatting</B
893 ><COL><COL><COL><THEAD
911 >fill mode (suppress padding blanks and zeroes)</TD
925 >upper-case ordinal number suffix</TD
939 >lower-case ordinal number suffix</TD
953 >fixed format global option (see usage notes)</TD
957 >FX Month DD Day</TT
967 >spell mode (not yet implemented)</TD
978 > Usage notes for the date/time formatting:
988 > suppresses leading zeroes and trailing blanks
989 that would otherwise be added to make the output of a pattern be
1002 skip multiple blank spaces in the input string if the <TT
1009 > must be specified as the first item
1010 in the template. For example
1013 >to_timestamp('2000 JUN', 'YYYY MON')</TT
1017 >to_timestamp('2000 JUN', 'FXYYYY MON')</TT
1022 > expects one space only.
1027 > Ordinary text is allowed in <CODE
1031 templates and will be output literally. You can put a substring
1032 in double quotes to force it to be interpreted as literal text
1033 even if it contains pattern key words. For example, in
1036 >'"Hello Year "YYYY'</TT
1041 will be replaced by the year data, but the single <TT
1053 > If you want to have a double quote in the output you must
1054 precede it with a backslash, for example <TT
1059 (Two backslashes are necessary because the backslash already
1060 has a special meaning in a string constant.)
1068 > conversion from string to <TT
1075 > has a restriction if you use a year with more than 4 digits. You must
1076 use some non-digit character or template after <TT
1080 otherwise the year is always interpreted as 4 digits. For example
1081 (with the year 20000):
1084 >to_date('200001131', 'YYYYMMDD')</TT
1086 interpreted as a 4-digit year; instead use a non-digit
1087 separator after the year, like
1090 >to_date('20000-1131', 'YYYY-MMDD')</TT
1094 >to_date('20000Nov31', 'YYYYMonDD')</TT
1103 >) and microsecond (<TT
1107 values in a conversion from string to <TT
1110 > are used as part of the
1111 seconds after the decimal point. For example
1114 >to_timestamp('12:3', 'SS:MS')</TT
1115 > is not 3 milliseconds,
1116 but 300, because the conversion counts it as 12 + 0.3 seconds.
1117 This means for the format <TT
1131 same number of milliseconds. To get three milliseconds, one must use
1135 >, which the conversion counts as
1136 12 + 0.003 = 12.003 seconds.
1143 >to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')</TT
1145 is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
1146 1230 microseconds = 2.021230 seconds.
1154 HREF="functions-formatting.html#FUNCTIONS-FORMATTING-NUMERIC-TABLE"
1157 template patterns available for formatting numeric values.
1162 NAME="FUNCTIONS-FORMATTING-NUMERIC-TABLE"
1166 >Table 9-23. Template Patterns for Numeric Formatting</B
1187 >value with the specified number of digits</TD
1196 >value with leading zeros</TD
1214 >group (thousand) separator</TD
1223 >negative value in angle brackets</TD
1232 >sign anchored to number (uses locale)</TD
1241 >currency symbol (uses locale)</TD
1250 >decimal point (uses locale)</TD
1259 >group separator (uses locale)</TD
1268 >minus sign in specified position (if number < 0)</TD
1277 >plus sign in specified position (if number > 0)</TD
1286 >plus/minus sign in specified position</TD
1295 >roman numeral (input between 1 and 3999)</TD
1307 >ordinal number suffix</TD
1316 >shift specified number of digits (see notes)</TD
1325 >scientific notation (not implemented yet)</TD
1331 > Usage notes for the numeric formatting:
1338 > A sign formatted using <TT
1348 > is not anchored to
1349 the number; for example,
1352 >to_char(-12, 'S9999')</TT
1355 >' -12'</TT
1359 >to_char(-12, 'MI9999')</TT
1362 >'- 12'</TT
1364 The Oracle implementation does not allow the use of
1387 > results in a value with the same number of
1388 digits as there are <TT
1392 not available it outputs a space.
1400 > does not convert values less than zero
1401 and does not convert fractional numbers.
1429 multiplies the input values by
1440 > is the number of digits following
1448 > does not support the use of
1452 > combined with a decimal point.
1464 HREF="functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE"
1467 examples of the use of the <CODE
1475 NAME="FUNCTIONS-FORMATTING-EXAMPLES-TABLE"
1500 >to_char(current_timestamp, 'Day, DD HH12:MI:SS')</TT
1505 >'Tuesday , 06 05:39:18'</TT
1512 >to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')</TT
1517 >'Tuesday, 6 05:39:18'</TT
1524 >to_char(-0.1, '99.99')</TT
1529 >' -.10'</TT
1536 >to_char(-0.1, 'FM9.99')</TT
1548 >to_char(0.1, '0.9')</TT
1560 >to_char(12, '9990999.9')</TT
1565 >' 0012.0'</TT
1572 >to_char(12, 'FM9990999.9')</TT
1584 >to_char(485, '999')</TT
1596 >to_char(-485, '999')</TT
1608 >to_char(485, '9 9 9')</TT
1613 >' 4 8 5'</TT
1620 >to_char(1485, '9,999')</TT
1632 >to_char(1485, '9G999')</TT
1637 >' 1 485'</TT
1644 >to_char(148.5, '999.999')</TT
1649 >' 148.500'</TT
1656 >to_char(148.5, 'FM999.999')</TT
1668 >to_char(148.5, 'FM999.990')</TT
1680 >to_char(148.5, '999D999')</TT
1685 >' 148,500'</TT
1692 >to_char(3148.5, '9G999D999')</TT
1697 >' 3 148,500'</TT
1704 >to_char(-485, '999S')</TT
1716 >to_char(-485, '999MI')</TT
1728 >to_char(485, '999MI')</TT
1740 >to_char(485, 'FM999MI')</TT
1752 >to_char(485, 'PL999')</TT
1764 >to_char(485, 'SG999')</TT
1776 >to_char(-485, 'SG999')</TT
1788 >to_char(-485, '9SG99')</TT
1800 >to_char(-485, '999PR')</TT
1812 >to_char(485, 'L999')</TT
1824 >to_char(485, 'RN')</TT
1829 >' CDLXXXV'</TT
1836 >to_char(485, 'FMRN')</TT
1848 >to_char(5.2, 'FMRN')</TT
1860 >to_char(482, '999th')</TT
1872 >to_char(485, '"Good number:"999')</TT
1877 >'Good number: 485'</TT
1884 >to_char(485.8, '"Pre:"999" Post:" .999')</TT
1889 >'Pre: 485 Post: .800'</TT
1896 >to_char(12, '99V999')</TT
1908 >to_char(12.4, '99V999')</TT
1920 >to_char(12.45, '99V9')</TT
1937 SUMMARY="Footer navigation table"
1948 HREF="functions-matching.html"
1966 HREF="functions-datetime.html"
1976 >Pattern Matching</TD
1982 HREF="functions.html"
1990 >Date/Time Functions and Operators</TD