Data type formatting functions v17

The EDB Postgres Advanced Server formatting functions 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 all functions follow a common calling convention: the first argument is the value to format and the second argument is a string template that defines the output or input format.

Overview of data type formatting functions

Available template patterns for date values

This 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

You can apply certain modifiers to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier. This table shows the modifier patterns for date/time formatting.

Usage notes for date/time formatting:

• FM suppresses leading zeroes and trailing blanks that are otherwise 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 you don't use the FX option. You must use FX 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 is 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.

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

This table shows some examples of the use of the TO_TIMESTAMP_TZ function:

Available template patterns for numeric values

This table lists the template patterns available for formatting numeric values:

The pattern 9 results in a value with the same number of digits as there are 9s. If a digit isn't available, the server ignores the corresponding 9s. The S pattern doesn't support +, and the $ pattern doesn't support decimal points in the expression.

This table shows some examples of the use of the TO_NUMBER function:

Numeric modifiers

This table shows the modifier pattern for numeric formatting:

Output formats for string and date functions

In an output template string (for TO_CHAR), certain patterns are recognized and replaced with appropriately formatted data from the value to format. Any text that isn't a template pattern is copied verbatim. Similarly, in an input template string (for anything but TO_CHAR), template patterns identify the parts of the input data string to look at and the values to find there.

If you don't 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. In the following example, date, month, and year isn't 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)

Altering the output format

Alter the output format of TO_DSINTERVAL(string) using the intervalstyle GUC setting. For example:

edb=# SET intervalstyle = 'sql_standard';
SET

edb=# select to_dsinterval('80 13:30:00') from dual;

to_dsinterval 
---------------
80 13:30:00

edb=# SET intervalstyle = 'postgres_verbose';
SET
edb=# select to_dsinterval('80 13:30:00') from dual;

to_dsinterval        
----------------------------
@ 80 days 13 hours 30 mins

Creating an index with the IMMUTABLE form of the TO_CHAR function

Certain cases of the TO_CHAR function results in usage of an IMMUTABLE form of the function. A function is IMMUTABLE if the function doesn't 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, and so on don't affect the results returned by the function.

For more information about function volatility categories VOLATILE, STABLE, and IMMUTABLE, see the function volatility doc.

A particular advantage of an IMMUTABLE function is that you can use it in the CREATE INDEX command to create an index based on that function.

For the TO_CHAR function to use the IMMUTABLE form, you must satisfy these conditions:

• 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 and locale. For example, you can use a format of 'YYYY-MM-DD HH24:MI:SS' for an IMMUTABLE form of the function since the result of the function is the date and time expressed solely in numeric form, regardless of locale settings. However, you can't use a format of 'DD-MON-YYYY' for an IMMUTABLE form of the function because the three-character abbreviation of the month can 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 this example, a table with a TIMESTAMP column is created:

CREATE TABLE ts_tbl (ts_col TIMESTAMP);

This example 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"

This results in an error because the format specified in the TO_CHAR function prevents the use of the IMMUTABLE form since the three-character month abbreviation, MON, can 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

Difference TO_CHAR, TO_NUMBER, and SUBSTR functions

Initialize the database using the INITDBOPTS variable to create clusters in a mode compatible with Oracle databases. The clusters created in PostgreSQL mode don't include compatibility features. To create a new cluster in PostgreSQL mode, use the "--no-redwood-compat" option, which offers a similar behavior to PostgreSQL.

A SELECT statement that contains one of the functions shown in the following table returns the indicated result in EDB Postgres Advanced Server and PostgreSQL. Listed are the following differences for TO_CHAR, TO_NUMBER, and SUBSTR functions.

TO_CHAR: Converts the timestamp to string according to a given format.

This table shows the examples for TO_CHAR(timestamp, text) function:

TO_NUMBER: Converts the string to numeric according to a given format.

This table shows the examples for the TO_NUMBER(text, text) function:

SUBSTR: Returns a substring of a character value.

This table shows the examples for SUBSTR(string, from, count), SUBSTR(string, from), SUBSTR(string, from, count)::bytea, and SUBSTR(string, from)::bytea function: