Formatting functions

• 1: Template patterns for date/time formatting
• 1.1: Template pattern modifiers for date/time formatting
• 2: Template patterns for numeric formatting
• 3: TO_BITSTRING
• 4: TO_CHAR
• 5: TO_DATE
• 6: TO_HEX
• 7: TO_NUMBER
• 8: TO_TIMESTAMP
• 9: TO_TIMESTAMP_TZ

1 - Template patterns for date/time formatting

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 is not a template pattern is copied verbatim. Similarly, in an input template string (for anything other than TO_CHAR), template patterns identify the parts of the input data string to look at and the values to find there.

2020-03-25 05:04:22.372 Init Session:0x7f8fcefec700-a000000013dcd4 [Txn] <INFO> Begin Txn: a000000013dcd4 'read role info'

Certain modifiers can be applied to any template pattern to alter its behavior, as described in Template pattern modifiers for date/time formatting.

Examples

Use TO_TIMESTAMP to convert an expression using the pattern 'YYY MON':

=> SELECT TO_TIMESTAMP('2017 JUN', 'YYYY MON');
    TO_TIMESTAMP
---------------------
 2017-06-01 00:00:00
(1 row)

Use TO_DATE to convert an expression using the pattern 'YYY-MMDD':

=> SELECT TO_DATE('2017-1231', 'YYYY-MMDD');
  TO_DATE
------------
 2017-12-31
(1 row)

1.1 - Template pattern modifiers for date/time formatting

Certain modifiers can be applied to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier.

2 - Template patterns for numeric formatting

Usage

• A sign formatted using SG, PL, or MI is not anchored to the number. For example:

  => SELECT to_char(-12, 'S9999'), to_char(-12, 'MI9999');
   to_char | to_char
  ---------+---------
     -12   | -  12
  (1 row)
  

  • TO_CHAR(-12, 'S9999') produces ' -12'

  • TO_CHAR(-12, 'MI9999') produces '- 12'

• 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 10^n, where n is the number of digits following V. TO_CHAR does not support the use of V combined with a decimal point—for example: 99.9V99.

3 - TO_BITSTRING

Returns a VARCHAR that represents the given VARBINARY value in bitstring format. This function is the inverse of BITSTRING_TO_BINARY.

Behavior type

Syntax

TO_BITSTRING ( expression )

Arguments

expression

The VARCHAR string to process.

Examples

=> SELECT TO_BITSTRING('ab'::BINARY(2));
   to_bitstring
------------------
 0110000101100010
(1 row)

=> SELECT TO_BITSTRING(HEX_TO_BINARY('0x10'));
 to_bitstring
--------------
00010000
(1 row)

=> SELECT TO_BITSTRING(HEX_TO_BINARY('0xF0'));
 to_bitstring
--------------
11110000
(1 row)

See also

4 - TO_CHAR

Converts date/time and numeric values into text strings.

Behavior type

Syntax

TO_CHAR ( expression [, pattern ] )

Parameters

expression

Specifies the value to convert, one of the following data types:

• DOUBLE PRECISION

• INTEGER

• INTERVAL

• TIME/TIMETZ

• TIMESTAMP/TIMESTAMPTZ

The following restrictions apply:

• TO_CHAR does not support binary data types BINARY and VARBINARY

• TO_CHAR does not support the use of V combined with a decimal point—for example, 99.9V99

pattern

A CHAR or VARCHAR that specifies an output pattern string. See:

• Template patterns for date/time formatting
• Template patterns for numeric formatting

Notes

• Vertica pads TO_CHAR output with a leading space, so positive and negative values have the same length. To suppress padding, use the FM prefix.

• TO_CHAR accepts TIME and TIMETZ data types as inputs if you explicitly cast TIME to TIMESTAMP and TIMETZ to TIMESTAMPTZ.

  => SELECT TO_CHAR(TIME '14:34:06.4','HH12:MI am'), TO_CHAR(TIMETZ '14:34:06.4+6','HH12:MI am');
   TO_CHAR  | TO_CHAR
  ----------+----------
   02:34 pm | 04:34 am
  (1 row)
  

• You can extract the timezone hour from TIMETZ:

  => SELECT EXTRACT(timezone_hour FROM TIMETZ '10:30+13:30');
   date_part
  -----------
          13
  (1 row)
  

• Ordinary text is allowed in TO_CHAR templates and is output literally. You can put a substring in double quotes to force it to be interpreted as literal text even if it contains pattern key words. In the following example, YYYY is replaced by the year data, but the Y in Year is not:

  => SELECT to_char(CURRENT_TIMESTAMP, '"Hello Year " YYYY');
       to_char
  ------------------
   Hello Year  2021
  (1 row)
  

• TO_CHAR uses different day-of-the-week numbering (see the D template pattern) than EXTRACT.

• Given an INTERVAL type, TO_CHAR formats HH and HH12 as hours in a single day, while HH24 can output hours exceeding a single day—for example, >24.

• To include a double quote (") character in output, precede it with a double backslash (\\). This is necessary because the backslash already has a special meaning in a string constant. For example: '\\"YYYY Month\\"'

• When rounding, the last digit of the rounded representation is selected to be even if the number is exactly half way between the two.

Examples

`0012.0`

    `CDLXXXV`

See also

5 - TO_DATE

Converts a string value to a DATE type.

Behavior type

Syntax

TO_DATE ( expression , pattern )

Parameters

expression

Specifies the string value to convert, either CHAR or VARCHAR.

pattern

A CHAR or VARCHAR that specifies an output pattern string. See:

• Template patterns for date/time formatting
• Template patterns for numeric formatting

Input value considerations

TO_DATE requires a CHAR or VARCHAR expression. For other input types, use TO_CHAR to perform an explicit cast to a CHAR or VARCHAR before using this function.

Notes

• To use a double quote character in the output, precede it with a double backslash. This is necessary because the backslash already has a special meaning in a string constant. For example: '\\"YYYY Month\\"'

• 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:

  • TO_TIMESTAMP('2000 JUN', 'YYYY MON') is correct.

  • TO_TIMESTAMP('2000 JUN', 'FXYYYY MON') returns an error, because TO_TIMESTAMP expects one space only.

• The YYYY conversion from string to TIMESTAMP or DATE has a restriction if you use a year with more than four digits. You must use a non-digit character or template after YYYY, otherwise the year is always interpreted as four digits. For example, given the following arguments, TO_DATE interprets the five-digit year 20000 as a four-digit year:

  => SELECT TO_DATE('200001131','YYYYMMDD');
    TO_DATE
  ------------
   2000-01-13
  (1 row)
  

  Instead, use a non-digit separator after the year. For example:

  => SELECT TO_DATE('20000-1131', 'YYYY-MMDD');
     TO_DATE
  -------------
   20000-12-01
  (1 row)
  

• 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.

Examples

=> SELECT TO_DATE('13 Feb 2000', 'DD Mon YYYY');
  to_date
------------
 2000-02-13
(1 row)

See also

6 - TO_HEX

Returns a VARCHAR or VARBINARY representing the hexadecimal equivalent of a number. This function is the inverse of HEX_TO_BINARY.

Behavior type

Syntax

TO_HEX ( number )

Arguments

number

An INTEGER or VARBINARY value to convert to hexadecimal. If you supply a VARBINARY argument, the function's return value is not preceded by 0x.

Examples

=> SELECT TO_HEX(123456789);
 TO_HEX
---------
 75bcd15
(1 row)

For VARBINARY inputs, the returned value is not preceded by 0x. For example:

=> SELECT TO_HEX('ab'::binary(2));
 TO_HEX
--------
 6162
(1 row)

7 - TO_NUMBER

Converts a string value to DOUBLE PRECISION.

Behavior type

Syntax

TO_NUMBER ( expression, [ pattern ] )

Parameters

expression

Specifies the string value to convert, either CHAR or VARCHAR.

pattern

A string value, either CHAR or VARCHAR, that specifies an output pattern string using one of the supported Template patterns for numeric formatting. If you omit this parameter, TO_NUMBER returns a floating point.

Notes

To use a double quote character in the output, precede it with a double backslash. This is necessary because the backslash already has a special meaning in a string constant. For example: '\\"YYYY Month\\"'

Examples

=> SELECT TO_NUMBER('MCML', 'rn');
TO_NUMBER
-----------
1950
(1 row)

It the pattern parameter is omitted, the function returns a floating point. For example:

=> SELECT TO_NUMBER('-123.456e-01');
 TO_NUMBER
-----------
  -12.3456

8 - TO_TIMESTAMP

Converts a string value or a UNIX/POSIX epoch value to a TIMESTAMP type.

Behavior type

Syntax

TO_TIMESTAMP ( { expression, pattern } | unix-epoch )

Parameters

expression

Specifies the string value to convert, of type CHAR or VARCHAR.

pattern

A CHAR or VARCHAR that specifies an output pattern string. See:

• Template patterns for date/time formatting
• Template patterns for numeric formatting

unix-epoch

DOUBLE PRECISION value that specifies some number of seconds elapsed since midnight UTC of January 1, 1970, excluding leap seconds. INTEGER values are implicitly cast to DOUBLE PRECISION.

Notes

• Millisecond (MS) and microsecond (US) values in a conversion from string to TIMESTAMP are used as part of the seconds after the decimal point. For example TO_TIMESTAMP('12:3', 'SS:MS') is not 3 milliseconds, but 300, because the conversion counts it as 12 + 0.3 seconds. This means for the format SS:MS, the input values 12:3, 12:30, and 12:300 specify the same number of milliseconds. To get three milliseconds, use 12:003, which the conversion counts as 12 + 0.003 = 12.003 seconds.

  Here is a more complex example: TO_TIMESTAMP('15:12:02.020.001230', 'HH:MI:SS.MS.US') is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds + 1230 microseconds = 2.021230 seconds.

• To use a double quote character in the output, precede it with a double backslash. This is necessary because the backslash already has a special meaning in a string constant. For example: '\\"YYYY Month\\"'

• 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:

  • TO_TIMESTAMP('2000 JUN', 'YYYY MON') is correct.

  • TO_TIMESTAMP('2000 JUN', 'FXYYYY MON') returns an error, because TO_TIMESTAMP expects one space only.

• The YYYY conversion from string to TIMESTAMP or DATE has a restriction if you use a year with more than four digits. You must use a non-digit character or template after YYYY, otherwise the year is always interpreted as four digits. For example, given the following arguments, TO_DATE interprets the five-digit year 20000 as a four-digit year:

  => SELECT TO_DATE('200001131','YYYYMMDD');
    TO_DATE
  ------------
   2000-01-13
  (1 row)
  

  Instead, use a non-digit separator after the year. For example:

  => SELECT TO_DATE('20000-1131', 'YYYY-MMDD');
     TO_DATE
  -------------
   20000-12-01
  (1 row)
  

• 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.

Examples

=> SELECT TO_TIMESTAMP('13 Feb 2009', 'DD Mon YYYY');
    TO_TIMESTAMP
---------------------
 1200-02-13 00:00:00
(1 row)
=> SELECT TO_TIMESTAMP(200120400);
    TO_TIMESTAMP
---------------------
 1976-05-05 01:00:00
(1 row)

See also

9 - TO_TIMESTAMP_TZ

Converts a string value or a UNIX/POSIX epoch value to a TIMESTAMP WITH TIME ZONE type.

Behavior type

Immutable if single argument form, Stable otherwise.

Syntax

TO_TIMESTAMP_TZ ( { expression, pattern } | unix-epoch )

Parameters

expression

Specifies the string value to convert, of type CHAR or VARCHAR.

pattern

A CHAR or VARCHAR that specifies an output pattern string. See:

• Template patterns for date/time formatting
• Template patterns for numeric formatting

unix-epoch

A DOUBLE PRECISION value that specifies some number of seconds elapsed since midnight UTC of January 1, 1970, excluding leap seconds. INTEGER values are implicitly cast to DOUBLE PRECISION.

Notes

• Millisecond (MS) and microsecond (US) values in a conversion from string to TIMESTAMP are used as part of the seconds after the decimal point. For example TO_TIMESTAMP('12:3', 'SS:MS') is not 3 milliseconds, but 300, because the conversion counts it as 12 + 0.3 seconds. This means for the format SS:MS, the input values 12:3, 12:30, and 12:300 specify the same number of milliseconds. To get three milliseconds, use 12:003, which the conversion counts as 12 + 0.003 = 12.003 seconds.

  Here is a more complex example: TO_TIMESTAMP('15:12:02.020.001230', 'HH:MI:SS.MS.US') is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds + 1230 microseconds = 2.021230 seconds.

• To use a double quote character in the output, precede it with a double backslash. This is necessary because the backslash already has a special meaning in a string constant. For example: '\\"YYYY Month\\"'

• 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:

  • TO_TIMESTAMP('2000 JUN', 'YYYY MON') is correct.

  • TO_TIMESTAMP('2000 JUN', 'FXYYYY MON') returns an error, because TO_TIMESTAMP expects one space only.

• The YYYY conversion from string to TIMESTAMP or DATE has a restriction if you use a year with more than four digits. You must use a non-digit character or template after YYYY, otherwise the year is always interpreted as four digits. For example, given the following arguments, TO_DATE interprets the five-digit year 20000 as a four-digit year:

  => SELECT TO_DATE('200001131','YYYYMMDD');
    TO_DATE
  ------------
   2000-01-13
  (1 row)
  

  Instead, use a non-digit separator after the year. For example:

  => SELECT TO_DATE('20000-1131', 'YYYY-MMDD');
     TO_DATE
  -------------
   20000-12-01
  (1 row)
  

• 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.

Examples

=> SELECT TO_TIMESTAMP_TZ('13 Feb 2009', 'DD Mon YYY');
    TO_TIMESTAMP_TZ
------------------------
 1200-02-13 00:00:00-05
(1 row)

=> SELECT TO_TIMESTAMP_TZ(200120400);
    TO_TIMESTAMP_TZ
------------------------
 1976-05-05 01:00:00-04
(1 row)

See also