3.5.7 Data Type Formatting Functions
The Advanced Server formatting functions (described in Table 3-3-22) 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 argument is the value to be formatted and the second argument is a string template that defines the output or input format.
TO_CHAR(DATE [, format ]) Convert a date/time to a string with output, format. If omitted default format is DD-MON-YY. TO_CHAR(TIMESTAMP [, format ]) Convert a timestamp to a string with output, format. If omitted default format is DD-MON-YY. TO_CHAR(INTEGER [, format ]) TO_CHAR(NUMBER [, format ]) TO_CHAR(DOUBLE PRECISION, format) TO_DATE(string [, format ]) Convert a date formatted string to a DATE data type TO_NUMBER(string [, format ]) Convert a number formatted string to a NUMBER data type TO_TIMESTAMP(string, format) Convert a timestamp formatted string to a TIMESTAMP data type In an output template string (for TO_CHAR), 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 (for anything but TO_CHAR), template patterns identify the parts of the input data string to be looked at and the values to be found there.The following table shows the template patterns available for formatting date values using the TO_CHAR and TO_DATE functions.Certain modifiers may be applied to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier. The following table shows the modifier patterns for date/time formatting.
FM prefix TH suffix th suffix FX prefix SP suffix
• FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern fixed-width.
• TO_TIMESTAMP and TO_DATE 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 correct, but TO_TIMESTAMP('2000 JUN', 'FXYYYY MON') returns an error, because TO_TIMESTAMP expects one space only.
• Ordinary text is allowed in TO_CHAR templates and will be output literally.
• In conversions from string to timestamp or date, the CC field is ignored if there is a YYY, YYYY or Y,YYY field. If CC is used with YY or Y then the year is computed as (CC-1)*100+YY.
. (period) , (comma) RN or rn
• 9 results in a value with the same number of digits as there are 9s. If a digit is not available it outputs a space.
• TH does not convert values less than zero and does not convert fractional numbers.V effectively multiplies the input values by 10n, where n is the number of digits following V. TO_CHAR does not support the use of V combined with a decimal point. (E.g., 99.9V99 is not allowed.)The following table shows some examples of the use of the TO_CHAR and TO_DATE functions.
There are certain cases of the TO_CHAR function that can result in usage of an IMMUTABLE form of the function. Basically, a function is IMMUTABLE if the function does not modify the database, and the function returns the same, consistent value dependent upon only its input parameters. That is, the settings of configuration parameters, the locale, the content of the database, etc. do not affect the results returned by the function.For more information about function volatility categories VOLATILE, STABLE, and IMMUTABLE, please see the PostgreSQL Core documentation at:A particular advantage of an IMMUTABLE function is that it can be used in the CREATE INDEX command to create an index based on that function.In order for the TO_CHAR function to use the IMMUTABLE form the following conditions must be satisfied:
• The first parameter of the TO_CHAR function must be of data type TIMESTAMP.
• The format specified in the second parameter of the TO_CHAR function must not affect the return value of the function based on factors such as language, locale, etc. For example a format of 'YYYY-MM-DD HH24:MI:SS' can be used for an IMMUTABLE form of the function since, regardless of locale settings, the result of the function is the date and time expressed solely in numeric form. However, a format of 'DD-MON-YYYY' cannot be used for an IMMUTABLE form of the function because the 3-character abbreviation of the month may return different results depending upon the locale setting.Format patterns that result in a non-immutable function include any variations of spelled out or abbreviated months (MONTH, MON), days (DAY, DY), median indicators (AM, PM), or era indicators (BC, AD).For the following example, a table with a TIMESTAMP column is created.The following shows the successful creation of an index with the IMMUTABLE form of the TO_CHAR function.The following results in an error because the format specified in the TO_CHAR function prevents the use of the IMMUTABLE form since the 3-character month abbreviation, MON, may result in different return values based on the locale setting.