Skip to content

Commit d584bf4

Browse files
tglsfdctglsfdc
committed
Improve description of to_char templates.
1 parent 7f02049 commit d584bf4

File tree

1 file changed

+73
-55
lines changed

1 file changed

+73
-55
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 73 additions & 55 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.54 2001/02/21 23:15:24 tgl Exp $ -->
1+
<!-- $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.55 2001/03/15 01:07:51 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -1387,9 +1387,9 @@
13871387
provide a powerful set of tools for converting various data types
13881388
(date/time, integer, floating point, numeric) to formatted strings
13891389
and for converting from formatted strings to specific data types.
1390-
These functions all follow a common calling convention: The first
1390+
These functions all follow a common calling convention: the first
13911391
argument is the value to be formatted and the second argument is a
1392-
template that defines the output format.
1392+
template that defines the output or input format.
13931393
</para>
13941394

13951395
<para>
@@ -1437,7 +1437,7 @@
14371437
</row>
14381438
<row>
14391439
<entry>to_timestamp(text, text)</entry>
1440-
<entry>date</entry>
1440+
<entry>timestamp</entry>
14411441
<entry>convert string to timestamp</entry>
14421442
<entry>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</entry>
14431443
</row>
@@ -1452,13 +1452,22 @@
14521452
</table>
14531453
</para>
14541454

1455+
<para>
1456+
In an output template string, there are certain patterns that are
1457+
recognized and replaced with appropriately-formatted data from the value
1458+
to be formatted. Any text that is not a template pattern is simply
1459+
copied verbatim. Similarly, in an input template string template patterns
1460+
identify the parts of the input data string to be looked at and the
1461+
values to be found there.
1462+
</para>
1463+
14551464
<para>
14561465
<table tocentry="1">
1457-
<title>Templates for date/time conversions</title>
1466+
<title>Template patterns for date/time conversions</title>
14581467
<tgroup cols="2">
14591468
<thead>
14601469
<row>
1461-
<entry>Template</entry>
1470+
<entry>Pattern</entry>
14621471
<entry>Description</entry>
14631472
</row>
14641473
</thead>
@@ -1525,19 +1534,19 @@
15251534
</row>
15261535
<row>
15271536
<entry>MONTH</entry>
1528-
<entry>full upper case month name (9 chars)</entry>
1537+
<entry>full upper case month name (blank-padded to 9 chars)</entry>
15291538
</row>
15301539
<row>
15311540
<entry>Month</entry>
1532-
<entry>full mixed case month name (9 chars)</entry>
1541+
<entry>full mixed case month name (blank-padded to 9 chars)</entry>
15331542
</row>
15341543
<row>
15351544
<entry>month</entry>
1536-
<entry>full lower case month name (9 chars)</entry>
1545+
<entry>full lower case month name (blank-padded to 9 chars)</entry>
15371546
</row>
15381547
<row>
15391548
<entry>MON</entry>
1540-
<entry>upper case abbreviated month name (3 chars)</entry>
1549+
<entry>abbreviated upper case month name (3 chars)</entry>
15411550
</row>
15421551
<row>
15431552
<entry>Mon</entry>
@@ -1549,19 +1558,19 @@
15491558
</row>
15501559
<row>
15511560
<entry>MM</entry>
1552-
<entry>month (01-12)</entry>
1561+
<entry>month number (01-12)</entry>
15531562
</row>
15541563
<row>
15551564
<entry>DAY</entry>
1556-
<entry>full upper case day name (9 chars)</entry>
1565+
<entry>full upper case day name (blank-padded to 9 chars)</entry>
15571566
</row>
15581567
<row>
15591568
<entry>Day</entry>
1560-
<entry>full mixed case day name (9 chars)</entry>
1569+
<entry>full mixed case day name (blank-padded to 9 chars)</entry>
15611570
</row>
15621571
<row>
15631572
<entry>day</entry>
1564-
<entry>full lower case day name (9 chars)</entry>
1573+
<entry>full lower case day name (blank-padded to 9 chars)</entry>
15651574
</row>
15661575
<row>
15671576
<entry>DY</entry>
@@ -1621,57 +1630,58 @@
16211630
</row>
16221631
<row>
16231632
<entry>TZ</entry>
1624-
<entry>timezone string - upper case</entry>
1633+
<entry>timezone name - upper case</entry>
16251634
</row>
16261635
<row>
16271636
<entry>tz</entry>
1628-
<entry>timezone string - lower case</entry>
1637+
<entry>timezone name - lower case</entry>
16291638
</row>
16301639
</tbody>
16311640
</tgroup>
16321641
</table>
16331642
</para>
16341643

16351644
<para>
1636-
All templates allow the use of prefix and suffix modifiers. Modifiers are
1637-
always valid for use in templates. The prefix
1638-
<quote><literal>FX</literal></quote> is a global modifier only.
1645+
Certain modifiers may be applied to any template pattern to alter its
1646+
behavior. For example, <quote><literal>FMMonth</literal></quote>
1647+
is the <quote><literal>Month</literal></quote> pattern with the
1648+
<quote><literal>FM</literal></quote> prefix.
16391649
</para>
16401650

16411651
<para>
16421652
<table tocentry="1">
1643-
<title>Suffixes for templates for date/time to_char()</title>
1653+
<title>Template pattern modifiers for date/time conversions</title>
16441654
<tgroup cols="3">
16451655
<thead>
16461656
<row>
1647-
<entry>Suffix</entry>
1657+
<entry>Modifier</entry>
16481658
<entry>Description</entry>
16491659
<entry>Example</entry>
16501660
</row>
16511661
</thead>
16521662
<tbody>
16531663
<row>
1654-
<entry>FM</entry>
1655-
<entry>fill mode prefix</entry>
1664+
<entry><literal>FM</literal> prefix</entry>
1665+
<entry>fill mode (suppress padding blanks and zeroes)</entry>
16561666
<entry>FMMonth</entry>
16571667
</row>
16581668
<row>
1659-
<entry>TH</entry>
1660-
<entry>upper ordinal number suffix</entry>
1669+
<entry><literal>TH</literal> suffix</entry>
1670+
<entry>add upper-case ordinal number suffix</entry>
16611671
<entry>DDTH</entry>
16621672
</row>
16631673
<row>
1664-
<entry>th</entry>
1665-
<entry>lower ordinal number suffix</entry>
1666-
<entry>DDTH</entry>
1674+
<entry><literal>th</literal> suffix</entry>
1675+
<entry>add lower-case ordinal number suffix</entry>
1676+
<entry>DDth</entry>
16671677
</row>
16681678
<row>
1669-
<entry>FX</entry>
1679+
<entry><literal>FX</literal> prefix</entry>
16701680
<entry>FiXed format global option (see below)</entry>
16711681
<entry>FX Month DD Day</entry>
16721682
</row>
16731683
<row>
1674-
<entry>SP</entry>
1684+
<entry><literal>SP</literal> suffix</entry>
16751685
<entry>spell mode (not yet implemented)</entry>
16761686
<entry>DDSP</entry>
16771687
</row>
@@ -1684,15 +1694,23 @@
16841694
Usage notes:
16851695

16861696
<itemizedlist>
1697+
<listitem>
1698+
<para>
1699+
<literal>FM</literal> suppresses leading zeroes or trailing blanks
1700+
that would otherwise be added to make the output of a pattern be
1701+
fixed-width.
1702+
</para>
1703+
</listitem>
1704+
16871705
<listitem>
16881706
<para>
16891707
<function>to_timestamp</function> and <function>to_date</function>
1690-
skip multiple blank space in converted string if the <literal>FX</literal> option
1708+
skip multiple blank spaces in the input string if the <literal>FX</literal> option
16911709
is not used. <literal>FX</literal> must be specified as the first item
16921710
in the template; for example
16931711
<literal>to_timestamp('2000 JUN','YYYY MON')</literal> is right, but
1694-
<literal>to_timestamp('2000 JUN','FXYYYY MON')</literal> returns error,
1695-
because to_timestamp() expects one blank space only.
1712+
<literal>to_timestamp('2000 JUN','FXYYYY MON')</literal> returns an error,
1713+
because <function>to_timestamp</function> expects one blank space only.
16961714
</para>
16971715
</listitem>
16981716

@@ -1709,19 +1727,20 @@
17091727
<listitem>
17101728
<para>
17111729
Ordinary text is allowed in <function>to_char</function>
1712-
templates but any string between double quotes is guaranteed
1713-
that it will not be interpreted as a template keyword and it is
1714-
also processed faster. (Example: <literal>'"Hello Year:
1715-
"YYYY'</literal>).
1730+
templates and will be output literally. You can put a substring
1731+
in double quotes to force it to be interpreted as literal text
1732+
even if it contains pattern keywords. For example, in
1733+
<literal>'"Hello Year: "YYYY'</literal>, the <literal>YYYY</literal>
1734+
will be replaced by year data, but the single <literal>Y</literal>
1735+
will not be.
17161736
</para>
17171737
</listitem>
17181738

17191739
<listitem>
17201740
<para>
1721-
A double quote (<quote><literal>"</literal></quote>) between
1722-
quotation marks is skipped and is not parsed. If you want to
1741+
If you want to
17231742
have a double quote in the output you must precede it with a
1724-
double backslash, for example <literal>'\\"YYYY
1743+
backslash, for example <literal>'\\"YYYY
17251744
Month\\"'</literal>. <!-- " font-lock sanity :-) -->
17261745
</para>
17271746
</listitem>
@@ -1745,11 +1764,11 @@
17451764

17461765
<para>
17471766
<table tocentry="1">
1748-
<title>Templates for to_char(<replaceable>numeric</replaceable>)</title>
1767+
<title>Template patterns for numeric conversions</title>
17491768
<tgroup cols="2">
17501769
<thead>
17511770
<row>
1752-
<entry>Template</entry>
1771+
<entry>Pattern</entry>
17531772
<entry>Description</entry>
17541773
</row>
17551774
</thead>
@@ -1843,25 +1862,24 @@
18431862

18441863
<listitem>
18451864
<para>
1846-
<literal>PL</literal>, <literal>SG</literal>, and
1847-
<literal>TH</literal> are <productname>Postgres</productname>
1848-
extensions.
1865+
<literal>9</literal> specifies a value with the same number of
1866+
digits as there are <literal>9</literal>s. If a digit is
1867+
not available use blank space.
18491868
</para>
18501869
</listitem>
18511870

18521871
<listitem>
18531872
<para>
1854-
<literal>9</literal> specifies a value with the same number of
1855-
digits as there are <literal>9</literal>s. If a digit is
1856-
not available use blank space.
1873+
<literal>TH</literal> does not convert values less than zero
1874+
and does not convert decimal numbers.
18571875
</para>
18581876
</listitem>
18591877

18601878
<listitem>
18611879
<para>
1862-
<literal>TH</literal> does not convert values less than zero
1863-
and does not convert decimal numbers. <literal>TH</literal> is
1864-
a <productname>Postgres</productname> extension.
1880+
<literal>PL</literal>, <literal>SG</literal>, and
1881+
<literal>TH</literal> are <productname>Postgres</productname>
1882+
extensions.
18651883
</para>
18661884
</listitem>
18671885

@@ -1892,12 +1910,12 @@
18921910
</thead>
18931911
<tbody>
18941912
<row>
1895-
<entry>to_char(now(),'Day, HH12:MI:SS')</entry>
1896-
<entry><literal>'Tuesday , 05:39:18'</literal></entry>
1913+
<entry>to_char(now(),'Day, DD HH12:MI:SS')</entry>
1914+
<entry><literal>'Tuesday , 06 05:39:18'</literal></entry>
18971915
</row>
18981916
<row>
1899-
<entry>to_char(now(),'FMDay, HH12:MI:SS')</entry>
1900-
<entry><literal>'Tuesday, 05:39:18'</literal></entry>
1917+
<entry>to_char(now(),'FMDay, FMDD HH12:MI:SS')</entry>
1918+
<entry><literal>'Tuesday, 6 05:39:18'</literal></entry>
19011919
</row>
19021920
<row>
19031921
<entry>to_char(-0.1,'99.99')</entry>

0 commit comments

Comments
 (0)