Data Type Formatting Functions v13

The Advanced Server formatting functions described in the following table 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, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ

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.

If you do not specify a date, month, or year when calling TO_TIMESTAMP, TO_TIMESTAMP_TZ or TO_DATE, then by default the output format considers the first date of a current month or current year respectively. In the following example, date, month, and year is not specified in the input string; TO_TIMESTAMP, TO_TIMESTAMP_TZ, and TO_DATE returns a default value of the first date of a current month and current year.

edb=# select to_timestamp('12', 'HH');

      to_timestamp
---------------------------
 01-MAY-20 12:00:00 +05:30
(1 row)

edb=# select to_timestamp_tz('12', 'HH');

      to_timestamp_tz
---------------------------
 01-MAY-20 12:00:00 +05:30
(1 row)

edb=# select to_date('12', 'HH');

      to_date
--------------------
 01-MAY-20 12:00:00
(1 row)

edb=# select to_char(sysdate, 'MISP');

 to_char 
---------
 sixteen
(1 row)

edb=# select to_char(cast('30-JAN-20 05:37:14 PM +05:30' as timestamp with time zone), 'HH24SP') from dual;

 to_char 
---------
 twelve
(1 row)

edb=# select to_char(cast('30-JAN-20 05:37:14 PM +05:30' as timestamp with time zone), 'MM-DD-YYYY HH24:MM:SS TZ') from dual;

         to_char         
-------------------------
 01-30-2020 17:01:14 IST
(1 row)

The following table shows the template patterns available for formatting date values using the TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ functions.

Date and Time Modifiers

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.

Usage notes for date/time formatting:

• FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern fixed-width.

• TO_TIMESTAMP, TO_TIMESTAMP_TZ, 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:

  → WrapCopy

  TO_TIMESTAMP('2000 - JUN', 'YYYY-MON') is correct, but
  TO_TIMESTAMP('2000    JUN', 'FXYYYY MON') and
  TO_TIMESTAMP_TZ('2000    JUN', 'FXYYYY MON') returns an error
  because TO_TIMESTAMP and TO_TIMESTAMP_TZ expects one space
  only.

• Ordinary text is allowed in TO_CHAR templates and will be output literally.

• In conversions from string to timestamp, timestamptz, 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.

The following table shows some examples of the use of the TO_CHAR and TO_DATE functions:

The following table shows some examples of the use of the TO_TIMESTAMP_TZ function:

IMMUTABLE TO_CHAR(TIMESTAMP, format) Function

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, see the PostgreSQL Core documentation at:

https://www.postgresql.org/docs/current/static/xfunc-volatility.html

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.

CREATE TABLE ts_tbl (ts_col TIMESTAMP);

The following shows the successful creation of an index with the IMMUTABLE form of the TO_CHAR function.

edb=# CREATE INDEX ts_idx ON ts_tbl (TO_CHAR(ts_col,'YYYY-MM-DD HH24:MI:SS'));
CREATE INDEX
edb=# \dS ts_idx
                                   Index "public.ts_idx"
 Column  |       Type        |                         Definition
---------+-------------------+-----------------------------------------------------------
 to_char | character varying | to_char(ts_col, 'YYYY-MM-DD HH24:MI:SS'::character varying)
btree, for table "public.ts_tbl"

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.

edb=# CREATE INDEX ts_idx_2 ON ts_tbl (TO_CHAR(ts_col, 'DD-MON-YYYY'));
ERROR:  functions in index expression must be marked IMMUTABLE

TO_NUMBER

The following table lists the template patterns available for formatting numeric values:

Note that 9 results in a value with the same number of digits as there are 9s. If a digit is not available, the server ignores the corresponding 9s. The S pattern does not support + and $ pattern does not support decimal points in the expression.

The following table shows some examples of the use of the TO_NUMBER function:

Numeric Modifiers

The following table shows the modifier pattern for numeric formatting: