From d584bf4867affcd00998335a8eab24ae01c8be34 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 15 Mar 2001 01:07:51 +0000 Subject: [PATCH] Improve description of to_char templates. --- doc/src/sgml/func.sgml | 128 +++++++++++++++++++++++------------------ 1 file changed, 73 insertions(+), 55 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index de7ef9c032..071a457bcb 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -1387,9 +1387,9 @@ provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. - These functions all follow a common calling convention: The first + These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a - template that defines the output format. + template that defines the output or input format. @@ -1437,7 +1437,7 @@ to_timestamp(text, text) - date + timestamp convert string to timestamp to_timestamp('05 Dec 2000', 'DD Mon YYYY') @@ -1452,13 +1452,22 @@ + + In an output template string, there are certain patterns that are + recognized and replaced with appropriately-formatted data from the value + to be formatted. Any text that is not a template pattern is simply + copied verbatim. Similarly, in an input template string template patterns + identify the parts of the input data string to be looked at and the + values to be found there. + + - Templates for date/time conversions + Template patterns for date/time conversions - Template + Pattern Description @@ -1525,19 +1534,19 @@ MONTH - full upper case month name (9 chars) + full upper case month name (blank-padded to 9 chars) Month - full mixed case month name (9 chars) + full mixed case month name (blank-padded to 9 chars) month - full lower case month name (9 chars) + full lower case month name (blank-padded to 9 chars) MON - upper case abbreviated month name (3 chars) + abbreviated upper case month name (3 chars) Mon @@ -1549,19 +1558,19 @@ MM - month (01-12) + month number (01-12) DAY - full upper case day name (9 chars) + full upper case day name (blank-padded to 9 chars) Day - full mixed case day name (9 chars) + full mixed case day name (blank-padded to 9 chars) day - full lower case day name (9 chars) + full lower case day name (blank-padded to 9 chars) DY @@ -1621,11 +1630,11 @@ TZ - timezone string - upper case + timezone name - upper case tz - timezone string - lower case + timezone name - lower case @@ -1633,45 +1642,46 @@ - All templates allow the use of prefix and suffix modifiers. Modifiers are - always valid for use in templates. The prefix - FX is a global modifier only. + Certain modifiers may be applied to any template pattern to alter its + behavior. For example, FMMonth + is the Month pattern with the + FM prefix.
- Suffixes for templates for date/time to_char() + Template pattern modifiers for date/time conversions - Suffix + Modifier Description Example - FM - fill mode prefix + FM prefix + fill mode (suppress padding blanks and zeroes) FMMonth - TH - upper ordinal number suffix + TH suffix + add upper-case ordinal number suffix DDTH - th - lower ordinal number suffix - DDTH + th suffix + add lower-case ordinal number suffix + DDth - FX + FX prefix FiXed format global option (see below) FX Month DD Day - SP + SP suffix spell mode (not yet implemented) DDSP @@ -1684,15 +1694,23 @@ Usage notes: + + + FM suppresses leading zeroes or trailing blanks + that would otherwise be added to make the output of a pattern be + fixed-width. + + + to_timestamp and to_date - skip multiple blank space in converted string if the FX option + skip multiple blank spaces in the input string if the FX option is not used. FX must be specified as the first item in the template; for example to_timestamp('2000 JUN','YYYY MON') is right, but - to_timestamp('2000 JUN','FXYYYY MON') returns error, - because to_timestamp() expects one blank space only. + to_timestamp('2000 JUN','FXYYYY MON') returns an error, + because to_timestamp expects one blank space only. @@ -1709,19 +1727,20 @@ Ordinary text is allowed in to_char - templates but any string between double quotes is guaranteed - that it will not be interpreted as a template keyword and it is - also processed faster. (Example: '"Hello Year: - "YYYY'). + templates and will be output literally. You can put a substring + in double quotes to force it to be interpreted as literal text + even if it contains pattern keywords. For example, in + '"Hello Year: "YYYY', the YYYY + will be replaced by year data, but the single Y + will not be. - A double quote (") between - quotation marks is skipped and is not parsed. If you want to + If you want to have a double quote in the output you must precede it with a - double backslash, for example '\\"YYYY + backslash, for example '\\"YYYY Month\\"'. @@ -1745,11 +1764,11 @@
- Templates for to_char(<replaceable>numeric</replaceable>) + Template patterns for numeric conversions - Template + Pattern Description @@ -1843,25 +1862,24 @@ - PL, SG, and - TH are Postgres - extensions. + 9 specifies a value with the same number of + digits as there are 9s. If a digit is + not available use blank space. - 9 specifies a value with the same number of - digits as there are 9s. If a digit is - not available use blank space. + TH does not convert values less than zero + and does not convert decimal numbers. - TH does not convert values less than zero - and does not convert decimal numbers. TH is - a Postgres extension. + PL, SG, and + TH are Postgres + extensions. @@ -1892,12 +1910,12 @@ - to_char(now(),'Day, HH12:MI:SS') - 'Tuesday , 05:39:18' + to_char(now(),'Day, DD HH12:MI:SS') + 'Tuesday , 06 05:39:18' - to_char(now(),'FMDay, HH12:MI:SS') - 'Tuesday, 05:39:18' + to_char(now(),'FMDay, FMDD HH12:MI:SS') + 'Tuesday, 6 05:39:18' to_char(-0.1,'99.99') -- 2.40.0