codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.8. Data Type Formatting Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-matching.html" title="9.7. Pattern Matching" /><link rel="next" href="functions-datetime.html" title="9.9. Date/Time Functions and Operators" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.8. Data Type Formatting Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-matching.html" title="9.7. Pattern Matching">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-datetime.html" title="9.9. Date/Time Functions and Operators">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-FORMATTING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.8. Data Type Formatting Functions <a href="#FUNCTIONS-FORMATTING" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.14.2" class="indexterm"></a><p>3 The <span class="productname">PostgreSQL</span> formatting functions4 provide a powerful set of tools for converting various data types5 (date/time, integer, floating point, numeric) to formatted strings6 and for converting from formatted strings to specific data types.7 <a class="xref" href="functions-formatting.html#FUNCTIONS-FORMATTING-TABLE" title="Table 9.26. Formatting Functions">Table 9.26</a> lists them.8 These functions all follow a common calling convention: the first9 argument is the value to be formatted and the second argument is a10 template that defines the output or input format.11 </p><div class="table" id="FUNCTIONS-FORMATTING-TABLE"><p class="title"><strong>Table 9.26. Formatting Functions</strong></p><div class="table-contents"><table class="table" summary="Formatting Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">12 Function13 </p>14 <p>15 Description16 </p>17 <p>18 Example(s)19 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">20 <a id="id-1.5.8.14.4.2.2.1.1.1.1" class="indexterm"></a>21 <code class="function">to_char</code> ( <code class="type">timestamp</code>, <code class="type">text</code> )22 → <code class="returnvalue">text</code>23 </p>24 <p class="func_signature">25 <code class="function">to_char</code> ( <code class="type">timestamp with time zone</code>, <code class="type">text</code> )26 → <code class="returnvalue">text</code>27 </p>28 <p>29 Converts time stamp to string according to the given format.30 </p>31 <p>32 <code class="literal">to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')</code>33 → <code class="returnvalue">05:31:12</code>34 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">35 <code class="function">to_char</code> ( <code class="type">interval</code>, <code class="type">text</code> )36 → <code class="returnvalue">text</code>37 </p>38 <p>39 Converts interval to string according to the given format.40 </p>41 <p>42 <code class="literal">to_char(interval '15h 2m 12s', 'HH24:MI:SS')</code>43 → <code class="returnvalue">15:02:12</code>44 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">45 <code class="function">to_char</code> ( <em class="replaceable"><code>numeric_type</code></em>, <code class="type">text</code> )46 → <code class="returnvalue">text</code>47 </p>48 <p>49 Converts number to string according to the given format; available50 for <code class="type">integer</code>, <code class="type">bigint</code>, <code class="type">numeric</code>,51 <code class="type">real</code>, <code class="type">double precision</code>.52 </p>53 <p>54 <code class="literal">to_char(125, '999')</code>55 → <code class="returnvalue">125</code>56 </p>57 <p>58 <code class="literal">to_char(125.8::real, '999D9')</code>59 → <code class="returnvalue">125.8</code>60 </p>61 <p>62 <code class="literal">to_char(-125.8, '999D99S')</code>63 → <code class="returnvalue">125.80-</code>64 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">65 <a id="id-1.5.8.14.4.2.2.4.1.1.1" class="indexterm"></a>66 <code class="function">to_date</code> ( <code class="type">text</code>, <code class="type">text</code> )67 → <code class="returnvalue">date</code>68 </p>69 <p>70 Converts string to date according to the given format.71 </p>72 <p>73 <code class="literal">to_date('05 Dec 2000', 'DD Mon YYYY')</code>74 → <code class="returnvalue">2000-12-05</code>75 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">76 <a id="id-1.5.8.14.4.2.2.5.1.1.1" class="indexterm"></a>77 <code class="function">to_number</code> ( <code class="type">text</code>, <code class="type">text</code> )78 → <code class="returnvalue">numeric</code>79 </p>80 <p>81 Converts string to numeric according to the given format.82 </p>83 <p>84 <code class="literal">to_number('12,454.8-', '99G999D9S')</code>85 → <code class="returnvalue">-12454.8</code>86 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">87 <a id="id-1.5.8.14.4.2.2.6.1.1.1" class="indexterm"></a>88 <code class="function">to_timestamp</code> ( <code class="type">text</code>, <code class="type">text</code> )89 → <code class="returnvalue">timestamp with time zone</code>90 </p>91 <p>92 Converts string to time stamp according to the given format.93 (See also <code class="function">to_timestamp(double precision)</code> in94 <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-TABLE" title="Table 9.33. Date/Time Functions">Table 9.33</a>.)95 </p>96 <p>97 <code class="literal">to_timestamp('05 Dec 2000', 'DD Mon YYYY')</code>98 → <code class="returnvalue">2000-12-05 00:00:00-05</code>99 </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="tip"><h3 class="title">Tip</h3><p>100 <code class="function">to_timestamp</code> and <code class="function">to_date</code>101 exist to handle input formats that cannot be converted by102 simple casting. For most standard date/time formats, simply casting the103 source string to the required data type works, and is much easier.104 Similarly, <code class="function">to_number</code> is unnecessary for standard numeric105 representations.106 </p></div><p>107 In a <code class="function">to_char</code> output template string, there are certain108 patterns that are recognized and replaced with appropriately-formatted109 data based on the given value. Any text that is not a template pattern is110 simply copied verbatim. Similarly, in an input template string (for the111 other functions), template patterns identify the values to be supplied by112 the input data string. If there are characters in the template string113 that are not template patterns, the corresponding characters in the input114 data string are simply skipped over (whether or not they are equal to the115 template string characters).116 </p><p>117 <a class="xref" href="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE" title="Table 9.27. Template Patterns for Date/Time Formatting">Table 9.27</a> shows the118 template patterns available for formatting date and time values.119 </p><div class="table" id="FUNCTIONS-FORMATTING-DATETIME-TABLE"><p class="title"><strong>Table 9.27. Template Patterns for Date/Time Formatting</strong></p><div class="table-contents"><table class="table" summary="Template Patterns for Date/Time Formatting" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Pattern</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">HH</code></td><td>hour of day (01–12)</td></tr><tr><td><code class="literal">HH12</code></td><td>hour of day (01–12)</td></tr><tr><td><code class="literal">HH24</code></td><td>hour of day (00–23)</td></tr><tr><td><code class="literal">MI</code></td><td>minute (00–59)</td></tr><tr><td><code class="literal">SS</code></td><td>second (00–59)</td></tr><tr><td><code class="literal">MS</code></td><td>millisecond (000–999)</td></tr><tr><td><code class="literal">US</code></td><td>microsecond (000000–999999)</td></tr><tr><td><code class="literal">FF1</code></td><td>tenth of second (0–9)</td></tr><tr><td><code class="literal">FF2</code></td><td>hundredth of second (00–99)</td></tr><tr><td><code class="literal">FF3</code></td><td>millisecond (000–999)</td></tr><tr><td><code class="literal">FF4</code></td><td>tenth of a millisecond (0000–9999)</td></tr><tr><td><code class="literal">FF5</code></td><td>hundredth of a millisecond (00000–99999)</td></tr><tr><td><code class="literal">FF6</code></td><td>microsecond (000000–999999)</td></tr><tr><td><code class="literal">SSSS</code>, <code class="literal">SSSSS</code></td><td>seconds past midnight (0–86399)</td></tr><tr><td><code class="literal">AM</code>, <code class="literal">am</code>,120 <code class="literal">PM</code> or <code class="literal">pm</code></td><td>meridiem indicator (without periods)</td></tr><tr><td><code class="literal">A.M.</code>, <code class="literal">a.m.</code>,121 <code class="literal">P.M.</code> or <code class="literal">p.m.</code></td><td>meridiem indicator (with periods)</td></tr><tr><td><code class="literal">Y,YYY</code></td><td>year (4 or more digits) with comma</td></tr><tr><td><code class="literal">YYYY</code></td><td>year (4 or more digits)</td></tr><tr><td><code class="literal">YYY</code></td><td>last 3 digits of year</td></tr><tr><td><code class="literal">YY</code></td><td>last 2 digits of year</td></tr><tr><td><code class="literal">Y</code></td><td>last digit of year</td></tr><tr><td><code class="literal">IYYY</code></td><td>ISO 8601 week-numbering year (4 or more digits)</td></tr><tr><td><code class="literal">IYY</code></td><td>last 3 digits of ISO 8601 week-numbering year</td></tr><tr><td><code class="literal">IY</code></td><td>last 2 digits of ISO 8601 week-numbering year</td></tr><tr><td><code class="literal">I</code></td><td>last digit of ISO 8601 week-numbering year</td></tr><tr><td><code class="literal">BC</code>, <code class="literal">bc</code>,122 <code class="literal">AD</code> or <code class="literal">ad</code></td><td>era indicator (without periods)</td></tr><tr><td><code class="literal">B.C.</code>, <code class="literal">b.c.</code>,123 <code class="literal">A.D.</code> or <code class="literal">a.d.</code></td><td>era indicator (with periods)</td></tr><tr><td><code class="literal">MONTH</code></td><td>full upper case month name (blank-padded to 9 chars)</td></tr><tr><td><code class="literal">Month</code></td><td>full capitalized month name (blank-padded to 9 chars)</td></tr><tr><td><code class="literal">month</code></td><td>full lower case month name (blank-padded to 9 chars)</td></tr><tr><td><code class="literal">MON</code></td><td>abbreviated upper case month name (3 chars in English, localized lengths vary)</td></tr><tr><td><code class="literal">Mon</code></td><td>abbreviated capitalized month name (3 chars in English, localized lengths vary)</td></tr><tr><td><code class="literal">mon</code></td><td>abbreviated lower case month name (3 chars in English, localized lengths vary)</td></tr><tr><td><code class="literal">MM</code></td><td>month number (01–12)</td></tr><tr><td><code class="literal">DAY</code></td><td>full upper case day name (blank-padded to 9 chars)</td></tr><tr><td><code class="literal">Day</code></td><td>full capitalized day name (blank-padded to 9 chars)</td></tr><tr><td><code class="literal">day</code></td><td>full lower case day name (blank-padded to 9 chars)</td></tr><tr><td><code class="literal">DY</code></td><td>abbreviated upper case day name (3 chars in English, localized lengths vary)</td></tr><tr><td><code class="literal">Dy</code></td><td>abbreviated capitalized day name (3 chars in English, localized lengths vary)</td></tr><tr><td><code class="literal">dy</code></td><td>abbreviated lower case day name (3 chars in English, localized lengths vary)</td></tr><tr><td><code class="literal">DDD</code></td><td>day of year (001–366)</td></tr><tr><td><code class="literal">IDDD</code></td><td>day of ISO 8601 week-numbering year (001–371; day 1 of the year is Monday of the first ISO week)</td></tr><tr><td><code class="literal">DD</code></td><td>day of month (01–31)</td></tr><tr><td><code class="literal">D</code></td><td>day of the week, Sunday (<code class="literal">1</code>) to Saturday (<code class="literal">7</code>)</td></tr><tr><td><code class="literal">ID</code></td><td>ISO 8601 day of the week, Monday (<code class="literal">1</code>) to Sunday (<code class="literal">7</code>)</td></tr><tr><td><code class="literal">W</code></td><td>week of month (1–5) (the first week starts on the first day of the month)</td></tr><tr><td><code class="literal">WW</code></td><td>week number of year (1–53) (the first week starts on the first day of the year)</td></tr><tr><td><code class="literal">IW</code></td><td>week number of ISO 8601 week-numbering year (01–53; the first Thursday of the year is in week 1)</td></tr><tr><td><code class="literal">CC</code></td><td>century (2 digits) (the twenty-first century starts on 2001-01-01)</td></tr><tr><td><code class="literal">J</code></td><td>Julian Date (integer days since November 24, 4714 BC at local124 midnight; see <a class="xref" href="datetime-julian-dates.html" title="B.7. Julian Dates">Section B.7</a>)</td></tr><tr><td><code class="literal">Q</code></td><td>quarter</td></tr><tr><td><code class="literal">RM</code></td><td>month in upper case Roman numerals (I–XII; I=January)</td></tr><tr><td><code class="literal">rm</code></td><td>month in lower case Roman numerals (i–xii; i=January)</td></tr><tr><td><code class="literal">TZ</code></td><td>upper case time-zone abbreviation125 (only supported in <code class="function">to_char</code>)</td></tr><tr><td><code class="literal">tz</code></td><td>lower case time-zone abbreviation126 (only supported in <code class="function">to_char</code>)</td></tr><tr><td><code class="literal">TZH</code></td><td>time-zone hours</td></tr><tr><td><code class="literal">TZM</code></td><td>time-zone minutes</td></tr><tr><td><code class="literal">OF</code></td><td>time-zone offset from UTC127 (only supported in <code class="function">to_char</code>)</td></tr></tbody></table></div></div><br class="table-break" /><p>128 Modifiers can be applied to any template pattern to alter its129 behavior. For example, <code class="literal">FMMonth</code>130 is the <code class="literal">Month</code> pattern with the131 <code class="literal">FM</code> modifier.132 <a class="xref" href="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE" title="Table 9.28. Template Pattern Modifiers for Date/Time Formatting">Table 9.28</a> shows the133 modifier patterns for date/time formatting.134 </p><div class="table" id="FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE"><p class="title"><strong>Table 9.28. Template Pattern Modifiers for Date/Time Formatting</strong></p><div class="table-contents"><table class="table" summary="Template Pattern Modifiers for Date/Time Formatting" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Modifier</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td><code class="literal">FM</code> prefix</td><td>fill mode (suppress leading zeroes and padding blanks)</td><td><code class="literal">FMMonth</code></td></tr><tr><td><code class="literal">TH</code> suffix</td><td>upper case ordinal number suffix</td><td><code class="literal">DDTH</code>, e.g., <code class="literal">12TH</code></td></tr><tr><td><code class="literal">th</code> suffix</td><td>lower case ordinal number suffix</td><td><code class="literal">DDth</code>, e.g., <code class="literal">12th</code></td></tr><tr><td><code class="literal">FX</code> prefix</td><td>fixed format global option (see usage notes)</td><td><code class="literal">FX Month DD Day</code></td></tr><tr><td><code class="literal">TM</code> prefix</td><td>translation mode (use localized day and month names based on135 <a class="xref" href="runtime-config-client.html#GUC-LC-TIME">lc_time</a>)</td><td><code class="literal">TMMonth</code></td></tr><tr><td><code class="literal">SP</code> suffix</td><td>spell mode (not implemented)</td><td><code class="literal">DDSP</code></td></tr></tbody></table></div></div><br class="table-break" /><p>136 Usage notes for date/time formatting:137 138 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>139 <code class="literal">FM</code> suppresses leading zeroes and trailing blanks140 that would otherwise be added to make the output of a pattern be141 fixed-width. In <span class="productname">PostgreSQL</span>,142 <code class="literal">FM</code> modifies only the next specification, while in143 Oracle <code class="literal">FM</code> affects all subsequent144 specifications, and repeated <code class="literal">FM</code> modifiers145 toggle fill mode on and off.146 </p></li><li class="listitem"><p>147 <code class="literal">TM</code> suppresses trailing blanks whether or148 not <code class="literal">FM</code> is specified.149 </p></li><li class="listitem"><p>150 <code class="function">to_timestamp</code> and <code class="function">to_date</code>151 ignore letter case in the input; so for152 example <code class="literal">MON</code>, <code class="literal">Mon</code>,153 and <code class="literal">mon</code> all accept the same strings. When using154 the <code class="literal">TM</code> modifier, case-folding is done according to155 the rules of the function's input collation (see156 <a class="xref" href="collation.html" title="24.2. Collation Support">Section 24.2</a>).157 </p></li><li class="listitem"><p>158 <code class="function">to_timestamp</code> and <code class="function">to_date</code>159 skip multiple blank spaces at the beginning of the input string and160 around date and time values unless the <code class="literal">FX</code> option is used. For example,161 <code class="literal">to_timestamp(' 2000 JUN', 'YYYY MON')</code> and162 <code class="literal">to_timestamp('2000 - JUN', 'YYYY-MON')</code> work, but163 <code class="literal">to_timestamp('2000 JUN', 'FXYYYY MON')</code> returns an error164 because <code class="function">to_timestamp</code> expects only a single space.165 <code class="literal">FX</code> must be specified as the first item in166 the template.167 </p></li><li class="listitem"><p>168 A separator (a space or non-letter/non-digit character) in the template string of169 <code class="function">to_timestamp</code> and <code class="function">to_date</code>170 matches any single separator in the input string or is skipped,171 unless the <code class="literal">FX</code> option is used.172 For example, <code class="literal">to_timestamp('2000JUN', 'YYYY///MON')</code> and173 <code class="literal">to_timestamp('2000/JUN', 'YYYY MON')</code> work, but174 <code class="literal">to_timestamp('2000//JUN', 'YYYY/MON')</code>175 returns an error because the number of separators in the input string176 exceeds the number of separators in the template.177 </p><p>178 If <code class="literal">FX</code> is specified, a separator in the template string179 matches exactly one character in the input string. But note that the180 input string character is not required to be the same as the separator from the template string.181 For example, <code class="literal">to_timestamp('2000/JUN', 'FXYYYY MON')</code>182 works, but <code class="literal">to_timestamp('2000/JUN', 'FXYYYY MON')</code>183 returns an error because the second space in the template string consumes184 the letter <code class="literal">J</code> from the input string.185 </p></li><li class="listitem"><p>186 A <code class="literal">TZH</code> template pattern can match a signed number.187 Without the <code class="literal">FX</code> option, minus signs may be ambiguous,188 and could be interpreted as a separator.189 This ambiguity is resolved as follows: If the number of separators before190 <code class="literal">TZH</code> in the template string is less than the number of191 separators before the minus sign in the input string, the minus sign192 is interpreted as part of <code class="literal">TZH</code>.193 Otherwise, the minus sign is considered to be a separator between values.194 For example, <code class="literal">to_timestamp('2000 -10', 'YYYY TZH')</code> matches195 <code class="literal">-10</code> to <code class="literal">TZH</code>, but196 <code class="literal">to_timestamp('2000 -10', 'YYYY TZH')</code>197 matches <code class="literal">10</code> to <code class="literal">TZH</code>.198 </p></li><li class="listitem"><p>199 Ordinary text is allowed in <code class="function">to_char</code>200 templates and will be output literally. You can put a substring201 in double quotes to force it to be interpreted as literal text202 even if it contains template patterns. For example, in203 <code class="literal">'"Hello Year "YYYY'</code>, the <code class="literal">YYYY</code>204 will be replaced by the year data, but the single <code class="literal">Y</code> in <code class="literal">Year</code>205 will not be.206 In <code class="function">to_date</code>, <code class="function">to_number</code>,207 and <code class="function">to_timestamp</code>, literal text and double-quoted208 strings result in skipping the number of characters contained in the209 string; for example <code class="literal">"XX"</code> skips two input characters210 (whether or not they are <code class="literal">XX</code>).211 </p><div class="tip"><h3 class="title">Tip</h3><p>212 Prior to <span class="productname">PostgreSQL</span> 12, it was possible to213 skip arbitrary text in the input string using non-letter or non-digit214 characters. For example,215 <code class="literal">to_timestamp('2000y6m1d', 'yyyy-MM-DD')</code> used to216 work. Now you can only use letter characters for this purpose. For example,217 <code class="literal">to_timestamp('2000y6m1d', 'yyyytMMtDDt')</code> and218 <code class="literal">to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"')</code>219 skip <code class="literal">y</code>, <code class="literal">m</code>, and220 <code class="literal">d</code>.221 </p></div></li><li class="listitem"><p>222 If you want to have a double quote in the output you must223 precede it with a backslash, for example <code class="literal">'\"YYYY224 Month\"'</code>. 225 Backslashes are not otherwise special outside of double-quoted226 strings. Within a double-quoted string, a backslash causes the227 next character to be taken literally, whatever it is (but this228 has no special effect unless the next character is a double quote229 or another backslash).230 </p></li><li class="listitem"><p>231 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,232 if the year format specification is less than four digits, e.g.,233 <code class="literal">YYY</code>, and the supplied year is less than four digits,234 the year will be adjusted to be nearest to the year 2020, e.g.,235 <code class="literal">95</code> becomes 1995.236 </p></li><li class="listitem"><p>237 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,238 negative years are treated as signifying BC. If you write both a239 negative year and an explicit <code class="literal">BC</code> field, you get AD240 again. An input of year zero is treated as 1 BC.241 </p></li><li class="listitem"><p>242 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,243 the <code class="literal">YYYY</code> conversion has a restriction when244 processing years with more than 4 digits. You must245 use some non-digit character or template after <code class="literal">YYYY</code>,246 otherwise the year is always interpreted as 4 digits. For example247 (with the year 20000):248 <code class="literal">to_date('200001130', 'YYYYMMDD')</code> will be249 interpreted as a 4-digit year; instead use a non-digit250 separator after the year, like251 <code class="literal">to_date('20000-1130', 'YYYY-MMDD')</code> or252 <code class="literal">to_date('20000Nov30', 'YYYYMonDD')</code>.253 </p></li><li class="listitem"><p>254 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,255 the <code class="literal">CC</code> (century) field is accepted but ignored256 if there is a <code class="literal">YYY</code>, <code class="literal">YYYY</code> or257 <code class="literal">Y,YYY</code> field. If <code class="literal">CC</code> is used with258 <code class="literal">YY</code> or <code class="literal">Y</code> then the result is259 computed as that year in the specified century. If the century is260 specified but the year is not, the first year of the century261 is assumed.262 </p></li><li class="listitem"><p>263 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,264 weekday names or numbers (<code class="literal">DAY</code>, <code class="literal">D</code>,265 and related field types) are accepted but are ignored for purposes of266 computing the result. The same is true for quarter267 (<code class="literal">Q</code>) fields.268 </p></li><li class="listitem"><p>269 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,270 an ISO 8601 week-numbering date (as distinct from a Gregorian date)271 can be specified in one of two ways:272 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>273 Year, week number, and weekday: for274 example <code class="literal">to_date('2006-42-4', 'IYYY-IW-ID')</code>275 returns the date <code class="literal">2006-10-19</code>.276 If you omit the weekday it is assumed to be 1 (Monday).277 </p></li><li class="listitem"><p>278 Year and day of year: for example <code class="literal">to_date('2006-291',279 'IYYY-IDDD')</code> also returns <code class="literal">2006-10-19</code>.280 </p></li></ul></div><p>281 </p><p>282 Attempting to enter a date using a mixture of ISO 8601 week-numbering283 fields and Gregorian date fields is nonsensical, and will cause an284 error. In the context of an ISO 8601 week-numbering year, the285 concept of a <span class="quote">“<span class="quote">month</span>”</span> or <span class="quote">“<span class="quote">day of month</span>”</span> has no286 meaning. In the context of a Gregorian year, the ISO week has no287 meaning.288 </p><div class="caution"><h3 class="title">Caution</h3><p>289 While <code class="function">to_date</code> will reject a mixture of290 Gregorian and ISO week-numbering date291 fields, <code class="function">to_char</code> will not, since output format292 specifications like <code class="literal">YYYY-MM-DD (IYYY-IDDD)</code> can be293 useful. But avoid writing something like <code class="literal">IYYY-MM-DD</code>;294 that would yield surprising results near the start of the year.295 (See <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" title="9.9.1. EXTRACT, date_part">Section 9.9.1</a> for more296 information.)297 </p></div></li><li class="listitem"><p>298 In <code class="function">to_timestamp</code>, millisecond299 (<code class="literal">MS</code>) or microsecond (<code class="literal">US</code>)300 fields are used as the301 seconds digits after the decimal point. For example302 <code class="literal">to_timestamp('12.3', 'SS.MS')</code> is not 3 milliseconds,303 but 300, because the conversion treats it as 12 + 0.3 seconds.304 So, for the format <code class="literal">SS.MS</code>, the input values305 <code class="literal">12.3</code>, <code class="literal">12.30</code>,306 and <code class="literal">12.300</code> specify the307 same number of milliseconds. To get three milliseconds, one must write308 <code class="literal">12.003</code>, which the conversion treats as309 12 + 0.003 = 12.003 seconds.310 </p><p>311 Here is a more312 complex example:313 <code class="literal">to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US')</code>314 is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +315 1230 microseconds = 2.021230 seconds.316 </p></li><li class="listitem"><p>317 <code class="function">to_char(..., 'ID')</code>'s day of the week numbering318 matches the <code class="function">extract(isodow from ...)</code> function, but319 <code class="function">to_char(..., 'D')</code>'s does not match320 <code class="function">extract(dow from ...)</code>'s day numbering.321 </p></li><li class="listitem"><p>322 <code class="function">to_char(interval)</code> formats <code class="literal">HH</code> and323 <code class="literal">HH12</code> as shown on a 12-hour clock, for example zero hours324 and 36 hours both output as <code class="literal">12</code>, while <code class="literal">HH24</code>325 outputs the full hour value, which can exceed 23 in326 an <code class="type">interval</code> value.327 </p></li></ul></div><p>328 </p><p>329 <a class="xref" href="functions-formatting.html#FUNCTIONS-FORMATTING-NUMERIC-TABLE" title="Table 9.29. Template Patterns for Numeric Formatting">Table 9.29</a> shows the330 template patterns available for formatting numeric values.331 </p><div class="table" id="FUNCTIONS-FORMATTING-NUMERIC-TABLE"><p class="title"><strong>Table 9.29. Template Patterns for Numeric Formatting</strong></p><div class="table-contents"><table class="table" summary="Template Patterns for Numeric Formatting" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Pattern</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">9</code></td><td>digit position (can be dropped if insignificant)</td></tr><tr><td><code class="literal">0</code></td><td>digit position (will not be dropped, even if insignificant)</td></tr><tr><td><code class="literal">.</code> (period)</td><td>decimal point</td></tr><tr><td><code class="literal">,</code> (comma)</td><td>group (thousands) separator</td></tr><tr><td><code class="literal">PR</code></td><td>negative value in angle brackets</td></tr><tr><td><code class="literal">S</code></td><td>sign anchored to number (uses locale)</td></tr><tr><td><code class="literal">L</code></td><td>currency symbol (uses locale)</td></tr><tr><td><code class="literal">D</code></td><td>decimal point (uses locale)</td></tr><tr><td><code class="literal">G</code></td><td>group separator (uses locale)</td></tr><tr><td><code class="literal">MI</code></td><td>minus sign in specified position (if number < 0)</td></tr><tr><td><code class="literal">PL</code></td><td>plus sign in specified position (if number > 0)</td></tr><tr><td><code class="literal">SG</code></td><td>plus/minus sign in specified position</td></tr><tr><td><code class="literal">RN</code></td><td>Roman numeral (input between 1 and 3999)</td></tr><tr><td><code class="literal">TH</code> or <code class="literal">th</code></td><td>ordinal number suffix</td></tr><tr><td><code class="literal">V</code></td><td>shift specified number of digits (see notes)</td></tr><tr><td><code class="literal">EEEE</code></td><td>exponent for scientific notation</td></tr></tbody></table></div></div><br class="table-break" /><p>332 Usage notes for numeric formatting:333 334 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>335 <code class="literal">0</code> specifies a digit position that will always be printed,336 even if it contains a leading/trailing zero. <code class="literal">9</code> also337 specifies a digit position, but if it is a leading zero then it will338 be replaced by a space, while if it is a trailing zero and fill mode339 is specified then it will be deleted. (For <code class="function">to_number()</code>,340 these two pattern characters are equivalent.)341 </p></li><li class="listitem"><p>342 If the format provides fewer fractional digits than the number being343 formatted, <code class="function">to_char()</code> will round the number to344 the specified number of fractional digits.345 </p></li><li class="listitem"><p>346 The pattern characters <code class="literal">S</code>, <code class="literal">L</code>, <code class="literal">D</code>,347 and <code class="literal">G</code> represent the sign, currency symbol, decimal point,348 and thousands separator characters defined by the current locale349 (see <a class="xref" href="runtime-config-client.html#GUC-LC-MONETARY">lc_monetary</a>350 and <a class="xref" href="runtime-config-client.html#GUC-LC-NUMERIC">lc_numeric</a>). The pattern characters period351 and comma represent those exact characters, with the meanings of352 decimal point and thousands separator, regardless of locale.353 </p></li><li class="listitem"><p>354 If no explicit provision is made for a sign355 in <code class="function">to_char()</code>'s pattern, one column will be reserved for356 the sign, and it will be anchored to (appear just left of) the357 number. If <code class="literal">S</code> appears just left of some <code class="literal">9</code>'s,358 it will likewise be anchored to the number.359 </p></li><li class="listitem"><p>360 A sign formatted using <code class="literal">SG</code>, <code class="literal">PL</code>, or361 <code class="literal">MI</code> is not anchored to362 the number; for example,363 <code class="literal">to_char(-12, 'MI9999')</code> produces <code class="literal">'- 12'</code>364 but <code class="literal">to_char(-12, 'S9999')</code> produces <code class="literal">' -12'</code>.365 (The Oracle implementation does not allow the use of366 <code class="literal">MI</code> before <code class="literal">9</code>, but rather367 requires that <code class="literal">9</code> precede368 <code class="literal">MI</code>.)369 </p></li><li class="listitem"><p>370 <code class="literal">TH</code> does not convert values less than zero371 and does not convert fractional numbers.372 </p></li><li class="listitem"><p>373 <code class="literal">PL</code>, <code class="literal">SG</code>, and374 <code class="literal">TH</code> are <span class="productname">PostgreSQL</span>375 extensions.376 </p></li><li class="listitem"><p>377 In <code class="function">to_number</code>, if non-data template patterns such378 as <code class="literal">L</code> or <code class="literal">TH</code> are used, the379 corresponding number of input characters are skipped, whether or not380 they match the template pattern, unless they are data characters381 (that is, digits, sign, decimal point, or comma). For382 example, <code class="literal">TH</code> would skip two non-data characters.383 </p></li><li class="listitem"><p>384 <code class="literal">V</code> with <code class="function">to_char</code>385 multiplies the input values by386 <code class="literal">10^<em class="replaceable"><code>n</code></em></code>, where387 <em class="replaceable"><code>n</code></em> is the number of digits following388 <code class="literal">V</code>. <code class="literal">V</code> with389 <code class="function">to_number</code> divides in a similar manner.390 <code class="function">to_char</code> and <code class="function">to_number</code>391 do not support the use of392 <code class="literal">V</code> combined with a decimal point393 (e.g., <code class="literal">99.9V99</code> is not allowed).394 </p></li><li class="listitem"><p>395 <code class="literal">EEEE</code> (scientific notation) cannot be used in396 combination with any of the other formatting patterns or397 modifiers other than digit and decimal point patterns, and must be at the end of the format string398 (e.g., <code class="literal">9.99EEEE</code> is a valid pattern).399 </p></li></ul></div><p>400 </p><p>401 Certain modifiers can be applied to any template pattern to alter its402 behavior. For example, <code class="literal">FM99.99</code>403 is the <code class="literal">99.99</code> pattern with the404 <code class="literal">FM</code> modifier.405 <a class="xref" href="functions-formatting.html#FUNCTIONS-FORMATTING-NUMERICMOD-TABLE" title="Table 9.30. Template Pattern Modifiers for Numeric Formatting">Table 9.30</a> shows the406 modifier patterns for numeric formatting.407 </p><div class="table" id="FUNCTIONS-FORMATTING-NUMERICMOD-TABLE"><p class="title"><strong>Table 9.30. Template Pattern Modifiers for Numeric Formatting</strong></p><div class="table-contents"><table class="table" summary="Template Pattern Modifiers for Numeric Formatting" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Modifier</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td><code class="literal">FM</code> prefix</td><td>fill mode (suppress trailing zeroes and padding blanks)</td><td><code class="literal">FM99.99</code></td></tr><tr><td><code class="literal">TH</code> suffix</td><td>upper case ordinal number suffix</td><td><code class="literal">999TH</code></td></tr><tr><td><code class="literal">th</code> suffix</td><td>lower case ordinal number suffix</td><td><code class="literal">999th</code></td></tr></tbody></table></div></div><br class="table-break" /><p>408 <a class="xref" href="functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE" title="Table 9.31. to_char Examples">Table 9.31</a> shows some409 examples of the use of the <code class="function">to_char</code> function.410 </p><div class="table" id="FUNCTIONS-FORMATTING-EXAMPLES-TABLE"><p class="title"><strong>Table 9.31. <code class="function">to_char</code> Examples</strong></p><div class="table-contents"><table class="table" summary="to_char Examples" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Expression</th><th>Result</th></tr></thead><tbody><tr><td><code class="literal">to_char(current_timestamp, 'Day, DD HH12:MI:SS')</code></td><td><code class="literal">'Tuesday , 06 05:39:18'</code></td></tr><tr><td><code class="literal">to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')</code></td><td><code class="literal">'Tuesday, 6 05:39:18'</code></td></tr><tr><td><code class="literal">to_char(-0.1, '99.99')</code></td><td><code class="literal">' -.10'</code></td></tr><tr><td><code class="literal">to_char(-0.1, 'FM9.99')</code></td><td><code class="literal">'-.1'</code></td></tr><tr><td><code class="literal">to_char(-0.1, 'FM90.99')</code></td><td><code class="literal">'-0.1'</code></td></tr><tr><td><code class="literal">to_char(0.1, '0.9')</code></td><td><code class="literal">' 0.1'</code></td></tr><tr><td><code class="literal">to_char(12, '9990999.9')</code></td><td><code class="literal">' 0012.0'</code></td></tr><tr><td><code class="literal">to_char(12, 'FM9990999.9')</code></td><td><code class="literal">'0012.'</code></td></tr><tr><td><code class="literal">to_char(485, '999')</code></td><td><code class="literal">' 485'</code></td></tr><tr><td><code class="literal">to_char(-485, '999')</code></td><td><code class="literal">'-485'</code></td></tr><tr><td><code class="literal">to_char(485, '9 9 9')</code></td><td><code class="literal">' 4 8 5'</code></td></tr><tr><td><code class="literal">to_char(1485, '9,999')</code></td><td><code class="literal">' 1,485'</code></td></tr><tr><td><code class="literal">to_char(1485, '9G999')</code></td><td><code class="literal">' 1 485'</code></td></tr><tr><td><code class="literal">to_char(148.5, '999.999')</code></td><td><code class="literal">' 148.500'</code></td></tr><tr><td><code class="literal">to_char(148.5, 'FM999.999')</code></td><td><code class="literal">'148.5'</code></td></tr><tr><td><code class="literal">to_char(148.5, 'FM999.990')</code></td><td><code class="literal">'148.500'</code></td></tr><tr><td><code class="literal">to_char(148.5, '999D999')</code></td><td><code class="literal">' 148,500'</code></td></tr><tr><td><code class="literal">to_char(3148.5, '9G999D999')</code></td><td><code class="literal">' 3 148,500'</code></td></tr><tr><td><code class="literal">to_char(-485, '999S')</code></td><td><code class="literal">'485-'</code></td></tr><tr><td><code class="literal">to_char(-485, '999MI')</code></td><td><code class="literal">'485-'</code></td></tr><tr><td><code class="literal">to_char(485, '999MI')</code></td><td><code class="literal">'485 '</code></td></tr><tr><td><code class="literal">to_char(485, 'FM999MI')</code></td><td><code class="literal">'485'</code></td></tr><tr><td><code class="literal">to_char(485, 'PL999')</code></td><td><code class="literal">'+485'</code></td></tr><tr><td><code class="literal">to_char(485, 'SG999')</code></td><td><code class="literal">'+485'</code></td></tr><tr><td><code class="literal">to_char(-485, 'SG999')</code></td><td><code class="literal">'-485'</code></td></tr><tr><td><code class="literal">to_char(-485, '9SG99')</code></td><td><code class="literal">'4-85'</code></td></tr><tr><td><code class="literal">to_char(-485, '999PR')</code></td><td><code class="literal">'<485>'</code></td></tr><tr><td><code class="literal">to_char(485, 'L999')</code></td><td><code class="literal">'DM 485'</code></td></tr><tr><td><code class="literal">to_char(485, 'RN')</code></td><td><code class="literal">' CDLXXXV'</code></td></tr><tr><td><code class="literal">to_char(485, 'FMRN')</code></td><td><code class="literal">'CDLXXXV'</code></td></tr><tr><td><code class="literal">to_char(5.2, 'FMRN')</code></td><td><code class="literal">'V'</code></td></tr><tr><td><code class="literal">to_char(482, '999th')</code></td><td><code class="literal">' 482nd'</code></td></tr><tr><td><code class="literal">to_char(485, '"Good number:"999')</code></td><td><code class="literal">'Good number: 485'</code></td></tr><tr><td><code class="literal">to_char(485.8, '"Pre:"999" Post:" .999')</code></td><td><code class="literal">'Pre: 485 Post: .800'</code></td></tr><tr><td><code class="literal">to_char(12, '99V999')</code></td><td><code class="literal">' 12000'</code></td></tr><tr><td><code class="literal">to_char(12.4, '99V999')</code></td><td><code class="literal">' 12400'</code></td></tr><tr><td><code class="literal">to_char(12.45, '99V9')</code></td><td><code class="literal">' 125'</code></td></tr><tr><td><code class="literal">to_char(0.0004859, '9.99EEEE')</code></td><td><code class="literal">' 4.86e-04'</code></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-matching.html" title="9.7. Pattern Matching">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-datetime.html" title="9.9. Date/Time Functions and Operators">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.7. Pattern Matching </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.9. Date/Time Functions and Operators</td></tr></table></div></body></html>