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 18.0 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 functions
4 provide a powerful set of tools for converting various data types
5 (date/time, integer, floating point, numeric) to formatted strings
6 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 first
9 argument is the value to be formatted and the second argument is a
10 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">
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>
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>
29 Converts time stamp to string according to the given format.
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>
39 Converts interval to string according to the given format.
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>
49 Converts number to string according to the given format; available
50 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>.
54 <code class="literal">to_char(125, '999')</code>
55 → <code class="returnvalue">125</code>
58 <code class="literal">to_char(125.8::real, '999D9')</code>
59 → <code class="returnvalue">125.8</code>
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>
70 Converts string to date according to the given format.
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>
81 Converts string to numeric according to the given format.
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>
92 Converts string to time stamp according to the given format.
93 (See also <code class="function">to_timestamp(double precision)</code> in
94 <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-TABLE" title="Table 9.33. Date/Time Functions">Table 9.33</a>.)
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 by
102 simple casting. For most standard date/time formats, simply casting the
103 source string to the required data type works, and is much easier.
104 Similarly, <code class="function">to_number</code> is unnecessary for standard numeric
107 In a <code class="function">to_char</code> output template string, there are certain
108 patterns that are recognized and replaced with appropriately-formatted
109 data based on the given value. Any text that is not a template pattern is
110 simply copied verbatim. Similarly, in an input template string (for the
111 other functions), template patterns identify the values to be supplied by
112 the input data string. If there are characters in the template string
113 that are not template patterns, the corresponding characters in the input
114 data string are simply skipped over (whether or not they are equal to the
115 template string characters).
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 the
118 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 local
124 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 abbreviation</td></tr><tr><td><code class="literal">tz</code></td><td>lower case time-zone abbreviation</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 UTC (<em class="replaceable"><code>HH</code></em>
125 or <em class="replaceable"><code>HH</code></em><code class="literal">:</code><em class="replaceable"><code>MM</code></em>)</td></tr></tbody></table></div></div><br class="table-break" /><p>
126 Modifiers can be applied to any template pattern to alter its
127 behavior. For example, <code class="literal">FMMonth</code>
128 is the <code class="literal">Month</code> pattern with the
129 <code class="literal">FM</code> modifier.
130 <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 the
131 modifier patterns for date/time formatting.
132 </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 on
133 <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>
134 Usage notes for date/time formatting:
136 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
137 <code class="literal">FM</code> suppresses leading zeroes and trailing blanks
138 that would otherwise be added to make the output of a pattern be
139 fixed-width. In <span class="productname">PostgreSQL</span>,
140 <code class="literal">FM</code> modifies only the next specification, while in
141 Oracle <code class="literal">FM</code> affects all subsequent
142 specifications, and repeated <code class="literal">FM</code> modifiers
143 toggle fill mode on and off.
144 </p></li><li class="listitem"><p>
145 <code class="literal">TM</code> suppresses trailing blanks whether or
146 not <code class="literal">FM</code> is specified.
147 </p></li><li class="listitem"><p>
148 <code class="function">to_timestamp</code> and <code class="function">to_date</code>
149 ignore letter case in the input; so for
150 example <code class="literal">MON</code>, <code class="literal">Mon</code>,
151 and <code class="literal">mon</code> all accept the same strings. When using
152 the <code class="literal">TM</code> modifier, case-folding is done according to
153 the rules of the function's input collation (see
154 <a class="xref" href="collation.html" title="23.2. Collation Support">Section 23.2</a>).
155 </p></li><li class="listitem"><p>
156 <code class="function">to_timestamp</code> and <code class="function">to_date</code>
157 skip multiple blank spaces at the beginning of the input string and
158 around date and time values unless the <code class="literal">FX</code> option is used. For example,
159 <code class="literal">to_timestamp(' 2000 JUN', 'YYYY MON')</code> and
160 <code class="literal">to_timestamp('2000 - JUN', 'YYYY-MON')</code> work, but
161 <code class="literal">to_timestamp('2000 JUN', 'FXYYYY MON')</code> returns an error
162 because <code class="function">to_timestamp</code> expects only a single space.
163 <code class="literal">FX</code> must be specified as the first item in
165 </p></li><li class="listitem"><p>
166 A separator (a space or non-letter/non-digit character) in the template string of
167 <code class="function">to_timestamp</code> and <code class="function">to_date</code>
168 matches any single separator in the input string or is skipped,
169 unless the <code class="literal">FX</code> option is used.
170 For example, <code class="literal">to_timestamp('2000JUN', 'YYYY///MON')</code> and
171 <code class="literal">to_timestamp('2000/JUN', 'YYYY MON')</code> work, but
172 <code class="literal">to_timestamp('2000//JUN', 'YYYY/MON')</code>
173 returns an error because the number of separators in the input string
174 exceeds the number of separators in the template.
176 If <code class="literal">FX</code> is specified, a separator in the template string
177 matches exactly one character in the input string. But note that the
178 input string character is not required to be the same as the separator from the template string.
179 For example, <code class="literal">to_timestamp('2000/JUN', 'FXYYYY MON')</code>
180 works, but <code class="literal">to_timestamp('2000/JUN', 'FXYYYY MON')</code>
181 returns an error because the second space in the template string consumes
182 the letter <code class="literal">J</code> from the input string.
183 </p></li><li class="listitem"><p>
184 A <code class="literal">TZH</code> template pattern can match a signed number.
185 Without the <code class="literal">FX</code> option, minus signs may be ambiguous,
186 and could be interpreted as a separator.
187 This ambiguity is resolved as follows: If the number of separators before
188 <code class="literal">TZH</code> in the template string is less than the number of
189 separators before the minus sign in the input string, the minus sign
190 is interpreted as part of <code class="literal">TZH</code>.
191 Otherwise, the minus sign is considered to be a separator between values.
192 For example, <code class="literal">to_timestamp('2000 -10', 'YYYY TZH')</code> matches
193 <code class="literal">-10</code> to <code class="literal">TZH</code>, but
194 <code class="literal">to_timestamp('2000 -10', 'YYYY TZH')</code>
195 matches <code class="literal">10</code> to <code class="literal">TZH</code>.
196 </p></li><li class="listitem"><p>
197 Ordinary text is allowed in <code class="function">to_char</code>
198 templates and will be output literally. You can put a substring
199 in double quotes to force it to be interpreted as literal text
200 even if it contains template patterns. For example, in
201 <code class="literal">'"Hello Year "YYYY'</code>, the <code class="literal">YYYY</code>
202 will be replaced by the year data, but the single <code class="literal">Y</code> in <code class="literal">Year</code>
204 In <code class="function">to_date</code>, <code class="function">to_number</code>,
205 and <code class="function">to_timestamp</code>, literal text and double-quoted
206 strings result in skipping the number of characters contained in the
207 string; for example <code class="literal">"XX"</code> skips two input characters
208 (whether or not they are <code class="literal">XX</code>).
209 </p><div class="tip"><h3 class="title">Tip</h3><p>
210 Prior to <span class="productname">PostgreSQL</span> 12, it was possible to
211 skip arbitrary text in the input string using non-letter or non-digit
212 characters. For example,
213 <code class="literal">to_timestamp('2000y6m1d', 'yyyy-MM-DD')</code> used to
214 work. Now you can only use letter characters for this purpose. For example,
215 <code class="literal">to_timestamp('2000y6m1d', 'yyyytMMtDDt')</code> and
216 <code class="literal">to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"')</code>
217 skip <code class="literal">y</code>, <code class="literal">m</code>, and
218 <code class="literal">d</code>.
219 </p></div></li><li class="listitem"><p>
220 If you want to have a double quote in the output you must
221 precede it with a backslash, for example <code class="literal">'\"YYYY
223 Backslashes are not otherwise special outside of double-quoted
224 strings. Within a double-quoted string, a backslash causes the
225 next character to be taken literally, whatever it is (but this
226 has no special effect unless the next character is a double quote
227 or another backslash).
228 </p></li><li class="listitem"><p>
229 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,
230 if the year format specification is less than four digits, e.g.,
231 <code class="literal">YYY</code>, and the supplied year is less than four digits,
232 the year will be adjusted to be nearest to the year 2020, e.g.,
233 <code class="literal">95</code> becomes 1995.
234 </p></li><li class="listitem"><p>
235 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,
236 negative years are treated as signifying BC. If you write both a
237 negative year and an explicit <code class="literal">BC</code> field, you get AD
238 again. An input of year zero is treated as 1 BC.
239 </p></li><li class="listitem"><p>
240 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,
241 the <code class="literal">YYYY</code> conversion has a restriction when
242 processing years with more than 4 digits. You must
243 use some non-digit character or template after <code class="literal">YYYY</code>,
244 otherwise the year is always interpreted as 4 digits. For example
245 (with the year 20000):
246 <code class="literal">to_date('200001130', 'YYYYMMDD')</code> will be
247 interpreted as a 4-digit year; instead use a non-digit
248 separator after the year, like
249 <code class="literal">to_date('20000-1130', 'YYYY-MMDD')</code> or
250 <code class="literal">to_date('20000Nov30', 'YYYYMonDD')</code>.
251 </p></li><li class="listitem"><p>
252 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,
253 the <code class="literal">CC</code> (century) field is accepted but ignored
254 if there is a <code class="literal">YYY</code>, <code class="literal">YYYY</code> or
255 <code class="literal">Y,YYY</code> field. If <code class="literal">CC</code> is used with
256 <code class="literal">YY</code> or <code class="literal">Y</code> then the result is
257 computed as that year in the specified century. If the century is
258 specified but the year is not, the first year of the century
260 </p></li><li class="listitem"><p>
261 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,
262 weekday names or numbers (<code class="literal">DAY</code>, <code class="literal">D</code>,
263 and related field types) are accepted but are ignored for purposes of
264 computing the result. The same is true for quarter
265 (<code class="literal">Q</code>) fields.
266 </p></li><li class="listitem"><p>
267 In <code class="function">to_timestamp</code> and <code class="function">to_date</code>,
268 an ISO 8601 week-numbering date (as distinct from a Gregorian date)
269 can be specified in one of two ways:
270 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
271 Year, week number, and weekday: for
272 example <code class="literal">to_date('2006-42-4', 'IYYY-IW-ID')</code>
273 returns the date <code class="literal">2006-10-19</code>.
274 If you omit the weekday it is assumed to be 1 (Monday).
275 </p></li><li class="listitem"><p>
276 Year and day of year: for example <code class="literal">to_date('2006-291',
277 'IYYY-IDDD')</code> also returns <code class="literal">2006-10-19</code>.
278 </p></li></ul></div><p>
280 Attempting to enter a date using a mixture of ISO 8601 week-numbering
281 fields and Gregorian date fields is nonsensical, and will cause an
282 error. In the context of an ISO 8601 week-numbering year, the
283 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 no
284 meaning. In the context of a Gregorian year, the ISO week has no
286 </p><div class="caution"><h3 class="title">Caution</h3><p>
287 While <code class="function">to_date</code> will reject a mixture of
288 Gregorian and ISO week-numbering date
289 fields, <code class="function">to_char</code> will not, since output format
290 specifications like <code class="literal">YYYY-MM-DD (IYYY-IDDD)</code> can be
291 useful. But avoid writing something like <code class="literal">IYYY-MM-DD</code>;
292 that would yield surprising results near the start of the year.
293 (See <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" title="9.9.1. EXTRACT, date_part">Section 9.9.1</a> for more
295 </p></div></li><li class="listitem"><p>
296 In <code class="function">to_timestamp</code>, millisecond
297 (<code class="literal">MS</code>) or microsecond (<code class="literal">US</code>)
298 fields are used as the
299 seconds digits after the decimal point. For example
300 <code class="literal">to_timestamp('12.3', 'SS.MS')</code> is not 3 milliseconds,
301 but 300, because the conversion treats it as 12 + 0.3 seconds.
302 So, for the format <code class="literal">SS.MS</code>, the input values
303 <code class="literal">12.3</code>, <code class="literal">12.30</code>,
304 and <code class="literal">12.300</code> specify the
305 same number of milliseconds. To get three milliseconds, one must write
306 <code class="literal">12.003</code>, which the conversion treats as
307 12 + 0.003 = 12.003 seconds.
311 <code class="literal">to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US')</code>
312 is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
313 1230 microseconds = 2.021230 seconds.
314 </p></li><li class="listitem"><p>
315 <code class="function">to_char(..., 'ID')</code>'s day of the week numbering
316 matches the <code class="function">extract(isodow from ...)</code> function, but
317 <code class="function">to_char(..., 'D')</code>'s does not match
318 <code class="function">extract(dow from ...)</code>'s day numbering.
319 </p></li><li class="listitem"><p>
320 <code class="function">to_char(interval)</code> formats <code class="literal">HH</code> and
321 <code class="literal">HH12</code> as shown on a 12-hour clock, for example zero hours
322 and 36 hours both output as <code class="literal">12</code>, while <code class="literal">HH24</code>
323 outputs the full hour value, which can exceed 23 in
324 an <code class="type">interval</code> value.
325 </p></li></ul></div><p>
327 <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 the
328 template patterns available for formatting numeric values.
329 </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> or <code class="literal">rn</code></td><td>Roman numeral (values 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>
330 Usage notes for numeric formatting:
332 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
333 <code class="literal">0</code> specifies a digit position that will always be printed,
334 even if it contains a leading/trailing zero. <code class="literal">9</code> also
335 specifies a digit position, but if it is a leading zero then it will
336 be replaced by a space, while if it is a trailing zero and fill mode
337 is specified then it will be deleted. (For <code class="function">to_number()</code>,
338 these two pattern characters are equivalent.)
339 </p></li><li class="listitem"><p>
340 If the format provides fewer fractional digits than the number being
341 formatted, <code class="function">to_char()</code> will round the number to
342 the specified number of fractional digits.
343 </p></li><li class="listitem"><p>
344 The pattern characters <code class="literal">S</code>, <code class="literal">L</code>, <code class="literal">D</code>,
345 and <code class="literal">G</code> represent the sign, currency symbol, decimal point,
346 and thousands separator characters defined by the current locale
347 (see <a class="xref" href="runtime-config-client.html#GUC-LC-MONETARY">lc_monetary</a>
348 and <a class="xref" href="runtime-config-client.html#GUC-LC-NUMERIC">lc_numeric</a>). The pattern characters period
349 and comma represent those exact characters, with the meanings of
350 decimal point and thousands separator, regardless of locale.
351 </p></li><li class="listitem"><p>
352 If no explicit provision is made for a sign
353 in <code class="function">to_char()</code>'s pattern, one column will be reserved for
354 the sign, and it will be anchored to (appear just left of) the
355 number. If <code class="literal">S</code> appears just left of some <code class="literal">9</code>'s,
356 it will likewise be anchored to the number.
357 </p></li><li class="listitem"><p>
358 A sign formatted using <code class="literal">SG</code>, <code class="literal">PL</code>, or
359 <code class="literal">MI</code> is not anchored to
360 the number; for example,
361 <code class="literal">to_char(-12, 'MI9999')</code> produces <code class="literal">'- 12'</code>
362 but <code class="literal">to_char(-12, 'S9999')</code> produces <code class="literal">' -12'</code>.
363 (The Oracle implementation does not allow the use of
364 <code class="literal">MI</code> before <code class="literal">9</code>, but rather
365 requires that <code class="literal">9</code> precede
366 <code class="literal">MI</code>.)
367 </p></li><li class="listitem"><p>
368 <code class="literal">TH</code> does not convert values less than zero
369 and does not convert fractional numbers.
370 </p></li><li class="listitem"><p>
371 <code class="literal">PL</code>, <code class="literal">SG</code>, and
372 <code class="literal">TH</code> are <span class="productname">PostgreSQL</span>
374 </p></li><li class="listitem"><p>
375 In <code class="function">to_number</code>, if non-data template patterns such
376 as <code class="literal">L</code> or <code class="literal">TH</code> are used, the
377 corresponding number of input characters are skipped, whether or not
378 they match the template pattern, unless they are data characters
379 (that is, digits, sign, decimal point, or comma). For
380 example, <code class="literal">TH</code> would skip two non-data characters.
381 </p></li><li class="listitem"><p>
382 <code class="literal">V</code> with <code class="function">to_char</code>
383 multiplies the input values by
384 <code class="literal">10^<em class="replaceable"><code>n</code></em></code>, where
385 <em class="replaceable"><code>n</code></em> is the number of digits following
386 <code class="literal">V</code>. <code class="literal">V</code> with
387 <code class="function">to_number</code> divides in a similar manner.
388 The <code class="literal">V</code> can be thought of as marking the position
389 of an implicit decimal point in the input or output string.
390 <code class="function">to_char</code> and <code class="function">to_number</code>
391 do not support the use of
392 <code class="literal">V</code> combined with a decimal point
393 (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 in
396 combination with any of the other formatting patterns or
397 modifiers other than digit and decimal point patterns, and must be at the end of the format string
398 (e.g., <code class="literal">9.99EEEE</code> is a valid pattern).
399 </p></li><li class="listitem"><p>
400 In <code class="function">to_number()</code>, the <code class="literal">RN</code>
401 pattern converts Roman numerals (in standard form) to numbers.
402 Input is case-insensitive, so <code class="literal">RN</code>
403 and <code class="literal">rn</code> are equivalent. <code class="literal">RN</code>
404 cannot be used in combination with any other formatting patterns or
405 modifiers except <code class="literal">FM</code>, which is applicable only
406 in <code class="function">to_char()</code> and is ignored
407 in <code class="function">to_number()</code>.
408 </p></li></ul></div><p>
410 Certain modifiers can be applied to any template pattern to alter its
411 behavior. For example, <code class="literal">FM99.99</code>
412 is the <code class="literal">99.99</code> pattern with the
413 <code class="literal">FM</code> modifier.
414 <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 the
415 modifier patterns for numeric formatting.
416 </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>
417 <a class="xref" href="functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE" title="Table 9.31. to_char Examples">Table 9.31</a> shows some
418 examples of the use of the <code class="function">to_char</code> function.
419 </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(current_timestamp AT TIME ZONE
420 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')</code></td><td><code class="literal">'2022-12-06T05:39:18Z'</code>,
421 <acronym class="acronym">ISO</acronym> 8601 extended format</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 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.9. Date/Time Functions and Operators</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob