Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-formatting.html410 linesDownload Raw Back to html
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 &lt; 0)</td></tr><tr><td><code class="literal">PL</code></td><td>plus sign in specified position (if number &gt; 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">'&lt;485&gt;'</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>
codekingpro/portable-devtools · Team Ai