diff options
Diffstat (limited to 'doc/src/sgml/func/func-formatting.sgml')
| -rw-r--r-- | doc/src/sgml/func/func-formatting.sgml | 1193 |
1 files changed, 1193 insertions, 0 deletions
diff --git a/doc/src/sgml/func/func-formatting.sgml b/doc/src/sgml/func/func-formatting.sgml new file mode 100644 index 00000000000..806302b2f7b --- /dev/null +++ b/doc/src/sgml/func/func-formatting.sgml @@ -0,0 +1,1193 @@ + <sect1 id="functions-formatting"> + <title>Data Type Formatting Functions</title> + + <indexterm> + <primary>formatting</primary> + </indexterm> + + <para> + The <productname>PostgreSQL</productname> 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. + <xref linkend="functions-formatting-table"/> 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. + </para> + + <table id="functions-formatting-table"> + <title>Formatting Functions</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="func_table_entry"><para role="func_signature"> + Function + </para> + <para> + Description + </para> + <para> + Example(s) + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>to_char</primary> + </indexterm> + <function>to_char</function> ( <type>timestamp</type>, <type>text</type> ) + <returnvalue>text</returnvalue> + </para> + <para role="func_signature"> + <function>to_char</function> ( <type>timestamp with time zone</type>, <type>text</type> ) + <returnvalue>text</returnvalue> + </para> + <para> + Converts time stamp to string according to the given format. + </para> + <para> + <literal>to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')</literal> + <returnvalue>05:31:12</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <function>to_char</function> ( <type>interval</type>, <type>text</type> ) + <returnvalue>text</returnvalue> + </para> + <para> + Converts interval to string according to the given format. + </para> + <para> + <literal>to_char(interval '15h 2m 12s', 'HH24:MI:SS')</literal> + <returnvalue>15:02:12</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <function>to_char</function> ( <replaceable>numeric_type</replaceable>, <type>text</type> ) + <returnvalue>text</returnvalue> + </para> + <para> + Converts number to string according to the given format; available + for <type>integer</type>, <type>bigint</type>, <type>numeric</type>, + <type>real</type>, <type>double precision</type>. + </para> + <para> + <literal>to_char(125, '999')</literal> + <returnvalue>125</returnvalue> + </para> + <para> + <literal>to_char(125.8::real, '999D9')</literal> + <returnvalue>125.8</returnvalue> + </para> + <para> + <literal>to_char(-125.8, '999D99S')</literal> + <returnvalue>125.80-</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>to_date</primary> + </indexterm> + <function>to_date</function> ( <type>text</type>, <type>text</type> ) + <returnvalue>date</returnvalue> + </para> + <para> + Converts string to date according to the given format. + </para> + <para> + <literal>to_date('05 Dec 2000', 'DD Mon YYYY')</literal> + <returnvalue>2000-12-05</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>to_number</primary> + </indexterm> + <function>to_number</function> ( <type>text</type>, <type>text</type> ) + <returnvalue>numeric</returnvalue> + </para> + <para> + Converts string to numeric according to the given format. + </para> + <para> + <literal>to_number('12,454.8-', '99G999D9S')</literal> + <returnvalue>-12454.8</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>to_timestamp</primary> + </indexterm> + <function>to_timestamp</function> ( <type>text</type>, <type>text</type> ) + <returnvalue>timestamp with time zone</returnvalue> + </para> + <para> + Converts string to time stamp according to the given format. + (See also <function>to_timestamp(double precision)</function> in + <xref linkend="functions-datetime-table"/>.) + </para> + <para> + <literal>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</literal> + <returnvalue>2000-12-05 00:00:00-05</returnvalue> + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + <tip> + <para> + <function>to_timestamp</function> and <function>to_date</function> + exist to handle input formats that cannot be converted by + simple casting. For most standard date/time formats, simply casting the + source string to the required data type works, and is much easier. + Similarly, <function>to_number</function> is unnecessary for standard numeric + representations. + </para> + </tip> + + <para> + In a <function>to_char</function> output template string, there are certain + patterns that are recognized and replaced with appropriately-formatted + data based on the given value. Any text that is not a template pattern is + simply copied verbatim. Similarly, in an input template string (for the + other functions), template patterns identify the values to be supplied by + the input data string. If there are characters in the template string + that are not template patterns, the corresponding characters in the input + data string are simply skipped over (whether or not they are equal to the + template string characters). + </para> + + <para> + <xref linkend="functions-formatting-datetime-table"/> shows the + template patterns available for formatting date and time values. + </para> + + <table id="functions-formatting-datetime-table"> + <title>Template Patterns for Date/Time Formatting</title> + <tgroup cols="2"> + <thead> + <row> + <entry>Pattern</entry> + <entry>Description</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>HH</literal></entry> + <entry>hour of day (01–12)</entry> + </row> + <row> + <entry><literal>HH12</literal></entry> + <entry>hour of day (01–12)</entry> + </row> + <row> + <entry><literal>HH24</literal></entry> + <entry>hour of day (00–23)</entry> + </row> + <row> + <entry><literal>MI</literal></entry> + <entry>minute (00–59)</entry> + </row> + <row> + <entry><literal>SS</literal></entry> + <entry>second (00–59)</entry> + </row> + <row> + <entry><literal>MS</literal></entry> + <entry>millisecond (000–999)</entry> + </row> + <row> + <entry><literal>US</literal></entry> + <entry>microsecond (000000–999999)</entry> + </row> + <row> + <entry><literal>FF1</literal></entry> + <entry>tenth of second (0–9)</entry> + </row> + <row> + <entry><literal>FF2</literal></entry> + <entry>hundredth of second (00–99)</entry> + </row> + <row> + <entry><literal>FF3</literal></entry> + <entry>millisecond (000–999)</entry> + </row> + <row> + <entry><literal>FF4</literal></entry> + <entry>tenth of a millisecond (0000–9999)</entry> + </row> + <row> + <entry><literal>FF5</literal></entry> + <entry>hundredth of a millisecond (00000–99999)</entry> + </row> + <row> + <entry><literal>FF6</literal></entry> + <entry>microsecond (000000–999999)</entry> + </row> + <row> + <entry><literal>SSSS</literal>, <literal>SSSSS</literal></entry> + <entry>seconds past midnight (0–86399)</entry> + </row> + <row> + <entry><literal>AM</literal>, <literal>am</literal>, + <literal>PM</literal> or <literal>pm</literal></entry> + <entry>meridiem indicator (without periods)</entry> + </row> + <row> + <entry><literal>A.M.</literal>, <literal>a.m.</literal>, + <literal>P.M.</literal> or <literal>p.m.</literal></entry> + <entry>meridiem indicator (with periods)</entry> + </row> + <row> + <entry><literal>Y,YYY</literal></entry> + <entry>year (4 or more digits) with comma</entry> + </row> + <row> + <entry><literal>YYYY</literal></entry> + <entry>year (4 or more digits)</entry> + </row> + <row> + <entry><literal>YYY</literal></entry> + <entry>last 3 digits of year</entry> + </row> + <row> + <entry><literal>YY</literal></entry> + <entry>last 2 digits of year</entry> + </row> + <row> + <entry><literal>Y</literal></entry> + <entry>last digit of year</entry> + </row> + <row> + <entry><literal>IYYY</literal></entry> + <entry>ISO 8601 week-numbering year (4 or more digits)</entry> + </row> + <row> + <entry><literal>IYY</literal></entry> + <entry>last 3 digits of ISO 8601 week-numbering year</entry> + </row> + <row> + <entry><literal>IY</literal></entry> + <entry>last 2 digits of ISO 8601 week-numbering year</entry> + </row> + <row> + <entry><literal>I</literal></entry> + <entry>last digit of ISO 8601 week-numbering year</entry> + </row> + <row> + <entry><literal>BC</literal>, <literal>bc</literal>, + <literal>AD</literal> or <literal>ad</literal></entry> + <entry>era indicator (without periods)</entry> + </row> + <row> + <entry><literal>B.C.</literal>, <literal>b.c.</literal>, + <literal>A.D.</literal> or <literal>a.d.</literal></entry> + <entry>era indicator (with periods)</entry> + </row> + <row> + <entry><literal>MONTH</literal></entry> + <entry>full upper case month name (blank-padded to 9 chars)</entry> + </row> + <row> + <entry><literal>Month</literal></entry> + <entry>full capitalized month name (blank-padded to 9 chars)</entry> + </row> + <row> + <entry><literal>month</literal></entry> + <entry>full lower case month name (blank-padded to 9 chars)</entry> + </row> + <row> + <entry><literal>MON</literal></entry> + <entry>abbreviated upper case month name (3 chars in English, localized lengths vary)</entry> + </row> + <row> + <entry><literal>Mon</literal></entry> + <entry>abbreviated capitalized month name (3 chars in English, localized lengths vary)</entry> + </row> + <row> + <entry><literal>mon</literal></entry> + <entry>abbreviated lower case month name (3 chars in English, localized lengths vary)</entry> + </row> + <row> + <entry><literal>MM</literal></entry> + <entry>month number (01–12)</entry> + </row> + <row> + <entry><literal>DAY</literal></entry> + <entry>full upper case day name (blank-padded to 9 chars)</entry> + </row> + <row> + <entry><literal>Day</literal></entry> + <entry>full capitalized day name (blank-padded to 9 chars)</entry> + </row> + <row> + <entry><literal>day</literal></entry> + <entry>full lower case day name (blank-padded to 9 chars)</entry> + </row> + <row> + <entry><literal>DY</literal></entry> + <entry>abbreviated upper case day name (3 chars in English, localized lengths vary)</entry> + </row> + <row> + <entry><literal>Dy</literal></entry> + <entry>abbreviated capitalized day name (3 chars in English, localized lengths vary)</entry> + </row> + <row> + <entry><literal>dy</literal></entry> + <entry>abbreviated lower case day name (3 chars in English, localized lengths vary)</entry> + </row> + <row> + <entry><literal>DDD</literal></entry> + <entry>day of year (001–366)</entry> + </row> + <row> + <entry><literal>IDDD</literal></entry> + <entry>day of ISO 8601 week-numbering year (001–371; day 1 of the year is Monday of the first ISO week)</entry> + </row> + <row> + <entry><literal>DD</literal></entry> + <entry>day of month (01–31)</entry> + </row> + <row> + <entry><literal>D</literal></entry> + <entry>day of the week, Sunday (<literal>1</literal>) to Saturday (<literal>7</literal>)</entry> + </row> + <row> + <entry><literal>ID</literal></entry> + <entry>ISO 8601 day of the week, Monday (<literal>1</literal>) to Sunday (<literal>7</literal>)</entry> + </row> + <row> + <entry><literal>W</literal></entry> + <entry>week of month (1–5) (the first week starts on the first day of the month)</entry> + </row> + <row> + <entry><literal>WW</literal></entry> + <entry>week number of year (1–53) (the first week starts on the first day of the year)</entry> + </row> + <row> + <entry><literal>IW</literal></entry> + <entry>week number of ISO 8601 week-numbering year (01–53; the first Thursday of the year is in week 1)</entry> + </row> + <row> + <entry><literal>CC</literal></entry> + <entry>century (2 digits) (the twenty-first century starts on 2001-01-01)</entry> + </row> + <row> + <entry><literal>J</literal></entry> + <entry>Julian Date (integer days since November 24, 4714 BC at local + midnight; see <xref linkend="datetime-julian-dates"/>)</entry> + </row> + <row> + <entry><literal>Q</literal></entry> + <entry>quarter</entry> + </row> + <row> + <entry><literal>RM</literal></entry> + <entry>month in upper case Roman numerals (I–XII; I=January)</entry> + </row> + <row> + <entry><literal>rm</literal></entry> + <entry>month in lower case Roman numerals (i–xii; i=January)</entry> + </row> + <row> + <entry><literal>TZ</literal></entry> + <entry>upper case time-zone abbreviation</entry> + </row> + <row> + <entry><literal>tz</literal></entry> + <entry>lower case time-zone abbreviation</entry> + </row> + <row> + <entry><literal>TZH</literal></entry> + <entry>time-zone hours</entry> + </row> + <row> + <entry><literal>TZM</literal></entry> + <entry>time-zone minutes</entry> + </row> + <row> + <entry><literal>OF</literal></entry> + <entry>time-zone offset from UTC (<replaceable>HH</replaceable> + or <replaceable>HH</replaceable><literal>:</literal><replaceable>MM</replaceable>)</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + Modifiers can be applied to any template pattern to alter its + behavior. For example, <literal>FMMonth</literal> + is the <literal>Month</literal> pattern with the + <literal>FM</literal> modifier. + <xref linkend="functions-formatting-datetimemod-table"/> shows the + modifier patterns for date/time formatting. + </para> + + <table id="functions-formatting-datetimemod-table"> + <title>Template Pattern Modifiers for Date/Time Formatting</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Modifier</entry> + <entry>Description</entry> + <entry>Example</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>FM</literal> prefix</entry> + <entry>fill mode (suppress leading zeroes and padding blanks)</entry> + <entry><literal>FMMonth</literal></entry> + </row> + <row> + <entry><literal>TH</literal> suffix</entry> + <entry>upper case ordinal number suffix</entry> + <entry><literal>DDTH</literal>, e.g., <literal>12TH</literal></entry> + </row> + <row> + <entry><literal>th</literal> suffix</entry> + <entry>lower case ordinal number suffix</entry> + <entry><literal>DDth</literal>, e.g., <literal>12th</literal></entry> + </row> + <row> + <entry><literal>FX</literal> prefix</entry> + <entry>fixed format global option (see usage notes)</entry> + <entry><literal>FX Month DD Day</literal></entry> + </row> + <row> + <entry><literal>TM</literal> prefix</entry> + <entry>translation mode (use localized day and month names based on + <xref linkend="guc-lc-time"/>)</entry> + <entry><literal>TMMonth</literal></entry> + </row> + <row> + <entry><literal>SP</literal> suffix</entry> + <entry>spell mode (not implemented)</entry> + <entry><literal>DDSP</literal></entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + Usage notes for date/time formatting: + + <itemizedlist> + <listitem> + <para> + <literal>FM</literal> suppresses leading zeroes and trailing blanks + that would otherwise be added to make the output of a pattern be + fixed-width. In <productname>PostgreSQL</productname>, + <literal>FM</literal> modifies only the next specification, while in + Oracle <literal>FM</literal> affects all subsequent + specifications, and repeated <literal>FM</literal> modifiers + toggle fill mode on and off. + </para> + </listitem> + + <listitem> + <para> + <literal>TM</literal> suppresses trailing blanks whether or + not <literal>FM</literal> is specified. + </para> + </listitem> + + <listitem> + <para> + <function>to_timestamp</function> and <function>to_date</function> + ignore letter case in the input; so for + example <literal>MON</literal>, <literal>Mon</literal>, + and <literal>mon</literal> all accept the same strings. When using + the <literal>TM</literal> modifier, case-folding is done according to + the rules of the function's input collation (see + <xref linkend="collation"/>). + </para> + </listitem> + + <listitem> + <para> + <function>to_timestamp</function> and <function>to_date</function> + skip multiple blank spaces at the beginning of the input string and + around date and time values unless the <literal>FX</literal> option is used. For example, + <literal>to_timestamp(' 2000 JUN', 'YYYY MON')</literal> and + <literal>to_timestamp('2000 - JUN', 'YYYY-MON')</literal> work, but + <literal>to_timestamp('2000 JUN', 'FXYYYY MON')</literal> returns an error + because <function>to_timestamp</function> expects only a single space. + <literal>FX</literal> must be specified as the first item in + the template. + </para> + </listitem> + + <listitem> + <para> + A separator (a space or non-letter/non-digit character) in the template string of + <function>to_timestamp</function> and <function>to_date</function> + matches any single separator in the input string or is skipped, + unless the <literal>FX</literal> option is used. + For example, <literal>to_timestamp('2000JUN', 'YYYY///MON')</literal> and + <literal>to_timestamp('2000/JUN', 'YYYY MON')</literal> work, but + <literal>to_timestamp('2000//JUN', 'YYYY/MON')</literal> + returns an error because the number of separators in the input string + exceeds the number of separators in the template. + </para> + <para> + If <literal>FX</literal> is specified, a separator in the template string + matches exactly one character in the input string. But note that the + input string character is not required to be the same as the separator from the template string. + For example, <literal>to_timestamp('2000/JUN', 'FXYYYY MON')</literal> + works, but <literal>to_timestamp('2000/JUN', 'FXYYYY MON')</literal> + returns an error because the second space in the template string consumes + the letter <literal>J</literal> from the input string. + </para> + </listitem> + + <listitem> + <para> + A <literal>TZH</literal> template pattern can match a signed number. + Without the <literal>FX</literal> option, minus signs may be ambiguous, + and could be interpreted as a separator. + This ambiguity is resolved as follows: If the number of separators before + <literal>TZH</literal> in the template string is less than the number of + separators before the minus sign in the input string, the minus sign + is interpreted as part of <literal>TZH</literal>. + Otherwise, the minus sign is considered to be a separator between values. + For example, <literal>to_timestamp('2000 -10', 'YYYY TZH')</literal> matches + <literal>-10</literal> to <literal>TZH</literal>, but + <literal>to_timestamp('2000 -10', 'YYYY TZH')</literal> + matches <literal>10</literal> to <literal>TZH</literal>. + </para> + </listitem> + + <listitem> + <para> + Ordinary text is allowed in <function>to_char</function> + 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 template patterns. For example, in + <literal>'"Hello Year "YYYY'</literal>, the <literal>YYYY</literal> + will be replaced by the year data, but the single <literal>Y</literal> in <literal>Year</literal> + will not be. + In <function>to_date</function>, <function>to_number</function>, + and <function>to_timestamp</function>, literal text and double-quoted + strings result in skipping the number of characters contained in the + string; for example <literal>"XX"</literal> skips two input characters + (whether or not they are <literal>XX</literal>). + </para> + <tip> + <para> + Prior to <productname>PostgreSQL</productname> 12, it was possible to + skip arbitrary text in the input string using non-letter or non-digit + characters. For example, + <literal>to_timestamp('2000y6m1d', 'yyyy-MM-DD')</literal> used to + work. Now you can only use letter characters for this purpose. For example, + <literal>to_timestamp('2000y6m1d', 'yyyytMMtDDt')</literal> and + <literal>to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"')</literal> + skip <literal>y</literal>, <literal>m</literal>, and + <literal>d</literal>. + </para> + </tip> + </listitem> + + <listitem> + <para> + If you want to have a double quote in the output you must + precede it with a backslash, for example <literal>'\"YYYY + Month\"'</literal>. <!-- "" font-lock sanity :-) --> + Backslashes are not otherwise special outside of double-quoted + strings. Within a double-quoted string, a backslash causes the + next character to be taken literally, whatever it is (but this + has no special effect unless the next character is a double quote + or another backslash). + </para> + </listitem> + + <listitem> + <para> + In <function>to_timestamp</function> and <function>to_date</function>, + if the year format specification is less than four digits, e.g., + <literal>YYY</literal>, and the supplied year is less than four digits, + the year will be adjusted to be nearest to the year 2020, e.g., + <literal>95</literal> becomes 1995. + </para> + </listitem> + + <listitem> + <para> + In <function>to_timestamp</function> and <function>to_date</function>, + negative years are treated as signifying BC. If you write both a + negative year and an explicit <literal>BC</literal> field, you get AD + again. An input of year zero is treated as 1 BC. + </para> + </listitem> + + <listitem> + <para> + In <function>to_timestamp</function> and <function>to_date</function>, + the <literal>YYYY</literal> conversion has a restriction when + processing years with more than 4 digits. You must + use some non-digit character or template after <literal>YYYY</literal>, + otherwise the year is always interpreted as 4 digits. For example + (with the year 20000): + <literal>to_date('200001130', 'YYYYMMDD')</literal> will be + interpreted as a 4-digit year; instead use a non-digit + separator after the year, like + <literal>to_date('20000-1130', 'YYYY-MMDD')</literal> or + <literal>to_date('20000Nov30', 'YYYYMonDD')</literal>. + </para> + </listitem> + + <listitem> + <para> + In <function>to_timestamp</function> and <function>to_date</function>, + the <literal>CC</literal> (century) field is accepted but ignored + if there is a <literal>YYY</literal>, <literal>YYYY</literal> or + <literal>Y,YYY</literal> field. If <literal>CC</literal> is used with + <literal>YY</literal> or <literal>Y</literal> then the result is + computed as that year in the specified century. If the century is + specified but the year is not, the first year of the century + is assumed. + </para> + </listitem> + + <listitem> + <para> + In <function>to_timestamp</function> and <function>to_date</function>, + weekday names or numbers (<literal>DAY</literal>, <literal>D</literal>, + and related field types) are accepted but are ignored for purposes of + computing the result. The same is true for quarter + (<literal>Q</literal>) fields. + </para> + </listitem> + + <listitem> + <para> + In <function>to_timestamp</function> and <function>to_date</function>, + an ISO 8601 week-numbering date (as distinct from a Gregorian date) + can be specified in one of two ways: + <itemizedlist> + <listitem> + <para> + Year, week number, and weekday: for + example <literal>to_date('2006-42-4', 'IYYY-IW-ID')</literal> + returns the date <literal>2006-10-19</literal>. + If you omit the weekday it is assumed to be 1 (Monday). + </para> + </listitem> + <listitem> + <para> + Year and day of year: for example <literal>to_date('2006-291', + 'IYYY-IDDD')</literal> also returns <literal>2006-10-19</literal>. + </para> + </listitem> + </itemizedlist> + </para> + <para> + Attempting to enter a date using a mixture of ISO 8601 week-numbering + fields and Gregorian date fields is nonsensical, and will cause an + error. In the context of an ISO 8601 week-numbering year, the + concept of a <quote>month</quote> or <quote>day of month</quote> has no + meaning. In the context of a Gregorian year, the ISO week has no + meaning. + </para> + <caution> + <para> + While <function>to_date</function> will reject a mixture of + Gregorian and ISO week-numbering date + fields, <function>to_char</function> will not, since output format + specifications like <literal>YYYY-MM-DD (IYYY-IDDD)</literal> can be + useful. But avoid writing something like <literal>IYYY-MM-DD</literal>; + that would yield surprising results near the start of the year. + (See <xref linkend="functions-datetime-extract"/> for more + information.) + </para> + </caution> + </listitem> + + <listitem> + <para> + In <function>to_timestamp</function>, millisecond + (<literal>MS</literal>) or microsecond (<literal>US</literal>) + fields are used as the + seconds digits after the decimal point. For example + <literal>to_timestamp('12.3', 'SS.MS')</literal> is not 3 milliseconds, + but 300, because the conversion treats it as 12 + 0.3 seconds. + So, for the format <literal>SS.MS</literal>, the input values + <literal>12.3</literal>, <literal>12.30</literal>, + and <literal>12.300</literal> specify the + same number of milliseconds. To get three milliseconds, one must write + <literal>12.003</literal>, which the conversion treats as + 12 + 0.003 = 12.003 seconds. + </para> + + <para> + Here is a more + complex example: + <literal>to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US')</literal> + is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds + + 1230 microseconds = 2.021230 seconds. + </para> + </listitem> + + <listitem> + <para> + <function>to_char(..., 'ID')</function>'s day of the week numbering + matches the <function>extract(isodow from ...)</function> function, but + <function>to_char(..., 'D')</function>'s does not match + <function>extract(dow from ...)</function>'s day numbering. + </para> + </listitem> + + <listitem> + <para> + <function>to_char(interval)</function> formats <literal>HH</literal> and + <literal>HH12</literal> as shown on a 12-hour clock, for example zero hours + and 36 hours both output as <literal>12</literal>, while <literal>HH24</literal> + outputs the full hour value, which can exceed 23 in + an <type>interval</type> value. + </para> + </listitem> + + </itemizedlist> + </para> + + <para> + <xref linkend="functions-formatting-numeric-table"/> shows the + template patterns available for formatting numeric values. + </para> + + <table id="functions-formatting-numeric-table"> + <title>Template Patterns for Numeric Formatting</title> + <tgroup cols="2"> + <thead> + <row> + <entry>Pattern</entry> + <entry>Description</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>9</literal></entry> + <entry>digit position (can be dropped if insignificant)</entry> + </row> + <row> + <entry><literal>0</literal></entry> + <entry>digit position (will not be dropped, even if insignificant)</entry> + </row> + <row> + <entry><literal>.</literal> (period)</entry> + <entry>decimal point</entry> + </row> + <row> + <entry><literal>,</literal> (comma)</entry> + <entry>group (thousands) separator</entry> + </row> + <row> + <entry><literal>PR</literal></entry> + <entry>negative value in angle brackets</entry> + </row> + <row> + <entry><literal>S</literal></entry> + <entry>sign anchored to number (uses locale)</entry> + </row> + <row> + <entry><literal>L</literal></entry> + <entry>currency symbol (uses locale)</entry> + </row> + <row> + <entry><literal>D</literal></entry> + <entry>decimal point (uses locale)</entry> + </row> + <row> + <entry><literal>G</literal></entry> + <entry>group separator (uses locale)</entry> + </row> + <row> + <entry><literal>MI</literal></entry> + <entry>minus sign in specified position (if number < 0)</entry> + </row> + <row> + <entry><literal>PL</literal></entry> + <entry>plus sign in specified position (if number > 0)</entry> + </row> + <row> + <entry><literal>SG</literal></entry> + <entry>plus/minus sign in specified position</entry> + </row> + <row> + <entry><literal>RN</literal> or <literal>rn</literal></entry> + <entry>Roman numeral (values between 1 and 3999)</entry> + </row> + <row> + <entry><literal>TH</literal> or <literal>th</literal></entry> + <entry>ordinal number suffix</entry> + </row> + <row> + <entry><literal>V</literal></entry> + <entry>shift specified number of digits (see notes)</entry> + </row> + <row> + <entry><literal>EEEE</literal></entry> + <entry>exponent for scientific notation</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + Usage notes for numeric formatting: + + <itemizedlist> + <listitem> + <para> + <literal>0</literal> specifies a digit position that will always be printed, + even if it contains a leading/trailing zero. <literal>9</literal> also + specifies a digit position, but if it is a leading zero then it will + be replaced by a space, while if it is a trailing zero and fill mode + is specified then it will be deleted. (For <function>to_number()</function>, + these two pattern characters are equivalent.) + </para> + </listitem> + + <listitem> + <para> + If the format provides fewer fractional digits than the number being + formatted, <function>to_char()</function> will round the number to + the specified number of fractional digits. + </para> + </listitem> + + <listitem> + <para> + The pattern characters <literal>S</literal>, <literal>L</literal>, <literal>D</literal>, + and <literal>G</literal> represent the sign, currency symbol, decimal point, + and thousands separator characters defined by the current locale + (see <xref linkend="guc-lc-monetary"/> + and <xref linkend="guc-lc-numeric"/>). The pattern characters period + and comma represent those exact characters, with the meanings of + decimal point and thousands separator, regardless of locale. + </para> + </listitem> + + <listitem> + <para> + If no explicit provision is made for a sign + in <function>to_char()</function>'s pattern, one column will be reserved for + the sign, and it will be anchored to (appear just left of) the + number. If <literal>S</literal> appears just left of some <literal>9</literal>'s, + it will likewise be anchored to the number. + </para> + </listitem> + + <listitem> + <para> + A sign formatted using <literal>SG</literal>, <literal>PL</literal>, or + <literal>MI</literal> is not anchored to + the number; for example, + <literal>to_char(-12, 'MI9999')</literal> produces <literal>'- 12'</literal> + but <literal>to_char(-12, 'S9999')</literal> produces <literal>' -12'</literal>. + (The Oracle implementation does not allow the use of + <literal>MI</literal> before <literal>9</literal>, but rather + requires that <literal>9</literal> precede + <literal>MI</literal>.) + </para> + </listitem> + + <listitem> + <para> + <literal>TH</literal> does not convert values less than zero + and does not convert fractional numbers. + </para> + </listitem> + + <listitem> + <para> + <literal>PL</literal>, <literal>SG</literal>, and + <literal>TH</literal> are <productname>PostgreSQL</productname> + extensions. + </para> + </listitem> + + <listitem> + <para> + In <function>to_number</function>, if non-data template patterns such + as <literal>L</literal> or <literal>TH</literal> are used, the + corresponding number of input characters are skipped, whether or not + they match the template pattern, unless they are data characters + (that is, digits, sign, decimal point, or comma). For + example, <literal>TH</literal> would skip two non-data characters. + </para> + </listitem> + + <listitem> + <para> + <literal>V</literal> with <function>to_char</function> + multiplies the input values by + <literal>10^<replaceable>n</replaceable></literal>, where + <replaceable>n</replaceable> is the number of digits following + <literal>V</literal>. <literal>V</literal> with + <function>to_number</function> divides in a similar manner. + The <literal>V</literal> can be thought of as marking the position + of an implicit decimal point in the input or output string. + <function>to_char</function> and <function>to_number</function> + do not support the use of + <literal>V</literal> combined with a decimal point + (e.g., <literal>99.9V99</literal> is not allowed). + </para> + </listitem> + + <listitem> + <para> + <literal>EEEE</literal> (scientific notation) cannot be used in + combination with any of the other formatting patterns or + modifiers other than digit and decimal point patterns, and must be at the end of the format string + (e.g., <literal>9.99EEEE</literal> is a valid pattern). + </para> + </listitem> + + <listitem> + <para> + In <function>to_number()</function>, the <literal>RN</literal> + pattern converts Roman numerals (in standard form) to numbers. + Input is case-insensitive, so <literal>RN</literal> + and <literal>rn</literal> are equivalent. <literal>RN</literal> + cannot be used in combination with any other formatting patterns or + modifiers except <literal>FM</literal>, which is applicable only + in <function>to_char()</function> and is ignored + in <function>to_number()</function>. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + Certain modifiers can be applied to any template pattern to alter its + behavior. For example, <literal>FM99.99</literal> + is the <literal>99.99</literal> pattern with the + <literal>FM</literal> modifier. + <xref linkend="functions-formatting-numericmod-table"/> shows the + modifier patterns for numeric formatting. + </para> + + <table id="functions-formatting-numericmod-table"> + <title>Template Pattern Modifiers for Numeric Formatting</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Modifier</entry> + <entry>Description</entry> + <entry>Example</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>FM</literal> prefix</entry> + <entry>fill mode (suppress trailing zeroes and padding blanks)</entry> + <entry><literal>FM99.99</literal></entry> + </row> + <row> + <entry><literal>TH</literal> suffix</entry> + <entry>upper case ordinal number suffix</entry> + <entry><literal>999TH</literal></entry> + </row> + <row> + <entry><literal>th</literal> suffix</entry> + <entry>lower case ordinal number suffix</entry> + <entry><literal>999th</literal></entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + <xref linkend="functions-formatting-examples-table"/> shows some + examples of the use of the <function>to_char</function> function. + </para> + + <table id="functions-formatting-examples-table"> + <title><function>to_char</function> Examples</title> + <tgroup cols="2"> + <thead> + <row> + <entry>Expression</entry> + <entry>Result</entry> + </row> + </thead> + <tbody> + <row> + <entry><literal>to_char(current_timestamp, 'Day, DD HH12:MI:SS')</literal></entry> + <entry><literal>'Tuesday , 06 05:39:18'</literal></entry> + </row> + <row> + <entry><literal>to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')</literal></entry> + <entry><literal>'Tuesday, 6 05:39:18'</literal></entry> + </row> + <row> + <entry><literal>to_char(current_timestamp AT TIME ZONE + 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')</literal></entry> + <entry><literal>'2022-12-06T05:39:18Z'</literal>, + <acronym>ISO</acronym> 8601 extended format</entry> + </row> + <row> + <entry><literal>to_char(-0.1, '99.99')</literal></entry> + <entry><literal>' -.10'</literal></entry> + </row> + <row> + <entry><literal>to_char(-0.1, 'FM9.99')</literal></entry> + <entry><literal>'-.1'</literal></entry> + </row> + <row> + <entry><literal>to_char(-0.1, 'FM90.99')</literal></entry> + <entry><literal>'-0.1'</literal></entry> + </row> + <row> + <entry><literal>to_char(0.1, '0.9')</literal></entry> + <entry><literal>' 0.1'</literal></entry> + </row> + <row> + <entry><literal>to_char(12, '9990999.9')</literal></entry> + <entry><literal>' 0012.0'</literal></entry> + </row> + <row> + <entry><literal>to_char(12, 'FM9990999.9')</literal></entry> + <entry><literal>'0012.'</literal></entry> + </row> + <row> + <entry><literal>to_char(485, '999')</literal></entry> + <entry><literal>' 485'</literal></entry> + </row> + <row> + <entry><literal>to_char(-485, '999')</literal></entry> + <entry><literal>'-485'</literal></entry> + </row> + <row> + <entry><literal>to_char(485, '9 9 9')</literal></entry> + <entry><literal>' 4 8 5'</literal></entry> + </row> + <row> + <entry><literal>to_char(1485, '9,999')</literal></entry> + <entry><literal>' 1,485'</literal></entry> + </row> + <row> + <entry><literal>to_char(1485, '9G999')</literal></entry> + <entry><literal>' 1 485'</literal></entry> + </row> + <row> + <entry><literal>to_char(148.5, '999.999')</literal></entry> + <entry><literal>' 148.500'</literal></entry> + </row> + <row> + <entry><literal>to_char(148.5, 'FM999.999')</literal></entry> + <entry><literal>'148.5'</literal></entry> + </row> + <row> + <entry><literal>to_char(148.5, 'FM999.990')</literal></entry> + <entry><literal>'148.500'</literal></entry> + </row> + <row> + <entry><literal>to_char(148.5, '999D999')</literal></entry> + <entry><literal>' 148,500'</literal></entry> + </row> + <row> + <entry><literal>to_char(3148.5, '9G999D999')</literal></entry> + <entry><literal>' 3 148,500'</literal></entry> + </row> + <row> + <entry><literal>to_char(-485, '999S')</literal></entry> + <entry><literal>'485-'</literal></entry> + </row> + <row> + <entry><literal>to_char(-485, '999MI')</literal></entry> + <entry><literal>'485-'</literal></entry> + </row> + <row> + <entry><literal>to_char(485, '999MI')</literal></entry> + <entry><literal>'485 '</literal></entry> + </row> + <row> + <entry><literal>to_char(485, 'FM999MI')</literal></entry> + <entry><literal>'485'</literal></entry> + </row> + <row> + <entry><literal>to_char(485, 'PL999')</literal></entry> + <entry><literal>'+485'</literal></entry> + </row> + <row> + <entry><literal>to_char(485, 'SG999')</literal></entry> + <entry><literal>'+485'</literal></entry> + </row> + <row> + <entry><literal>to_char(-485, 'SG999')</literal></entry> + <entry><literal>'-485'</literal></entry> + </row> + <row> + <entry><literal>to_char(-485, '9SG99')</literal></entry> + <entry><literal>'4-85'</literal></entry> + </row> + <row> + <entry><literal>to_char(-485, '999PR')</literal></entry> + <entry><literal>'<485>'</literal></entry> + </row> + <row> + <entry><literal>to_char(485, 'L999')</literal></entry> + <entry><literal>'DM 485'</literal></entry> + </row> + <row> + <entry><literal>to_char(485, 'RN')</literal></entry> + <entry><literal>' CDLXXXV'</literal></entry> + </row> + <row> + <entry><literal>to_char(485, 'FMRN')</literal></entry> + <entry><literal>'CDLXXXV'</literal></entry> + </row> + <row> + <entry><literal>to_char(5.2, 'FMRN')</literal></entry> + <entry><literal>'V'</literal></entry> + </row> + <row> + <entry><literal>to_char(482, '999th')</literal></entry> + <entry><literal>' 482nd'</literal></entry> + </row> + <row> + <entry><literal>to_char(485, '"Good number:"999')</literal></entry> + <entry><literal>'Good number: 485'</literal></entry> + </row> + <row> + <entry><literal>to_char(485.8, '"Pre:"999" Post:" .999')</literal></entry> + <entry><literal>'Pre: 485 Post: .800'</literal></entry> + </row> + <row> + <entry><literal>to_char(12, '99V999')</literal></entry> + <entry><literal>' 12000'</literal></entry> + </row> + <row> + <entry><literal>to_char(12.4, '99V999')</literal></entry> + <entry><literal>' 12400'</literal></entry> + </row> + <row> + <entry><literal>to_char(12.45, '99V9')</literal></entry> + <entry><literal>' 125'</literal></entry> + </row> + <row> + <entry><literal>to_char(0.0004859, '9.99EEEE')</literal></entry> + <entry><literal>' 4.86e-04'</literal></entry> + </row> + </tbody> + </tgroup> + </table> + + </sect1> |