PolarDB for PostgreSQL(Compatible with Oracle) supports the nls_timestamp_format and nls_timestamp_tz_format functions.

Overview

  • The nls_timestamp_format function controls the conversion format of the pg_catalog.to_char(arg1 timestamp) and pg_catalog.to_timestamp(arg1 text) functions, and the output format of the timestamp data type.
  • The nls_timestamp_tz_format function controls the conversion format of the pg_catalog.to_char(arg1 timestamptz) and pg_catalog.to_timestamp_tz(arg1 text) functions, and the output format of the timestamptz data type.
Note
  • TIMESTAMP and TIMESTAMP without time zone are of the same data type.
  • TIMESTAMPTZ and TIMESTAMPTZ with time zone are of the same data type.

Usage

  • nls_timestamp_format

    The initial value of nls_timestamp_format is ''.

    You can execute the SET statement to set nls_timestamp_format to a supported TIMESTAMP data type. If the format is invalid, an error is reported.

    You can reset the value by executing the RESET nls_timestamp_format; statement.

    Examples of the YYYY/MM/DD HH24:MI:SS format:
    • Set nls_timestamp_format to YYYY/MM/DD HH24:MI:SS. 
      set nls_timestamp_format to 'YYYY/MM/DD HH24:MI:SS';
      Sample result: 
      set
    • Convert 2021-11-11 11:11:11 in the YYYY/MM/DD HH24:MI:SS format and then run the to_char function. 
      select to_char('2021-11-11 11:11:11'::timestamp);
      Sample result: 
             to_char
---------------------
 2021/11/11 11:11:11
(1 row)
    • Convert 2021-11-11 11:11:11 in the YYYY/MM/DD HH24:MI:SS format and then run the to_timestamp function. 
      select to_timestamp('2021/11/11 11:11:11'::text);
      Sample result: 
          to_timestamp
---------------------
 2021/11/11 11:11:11
(1 row)
    • Display the timestamp data type in the YYYY/MM/DD HH24:MI:SS format. 
      select '2021-11-11 11:11:11'::timestamp;
      Sample result: 
            timestamp
---------------------
 2021/11/11 11:11:11
(1 row)
    Note
    The format of arg1 in to_timestamp(arg1 TEXT) must exactly the same as the format specified by nls_timestamp_format. Otherwise, an error or an unexpected result is returned. 
    # Set nls_timestamp_format to YYYY/MM/DD HH:MI:SS.FF. 
set nls_timestamp_format = 'YYYY/MM/DD HH:MI:SS.FF';
# Sample result:
set
# Convert 2000-03-28 08:00:00 in the YYYY/MM/DD HH:MI:SS.FF format and then run the to_timestamp function. 
select to_timestamp('2000-03-28 08:00:00');
# Sample result:
ERROR:  date/time field value out of range: "2000-03-28 08:00:00"
CONTEXT:  PL/pgSQL function to_timestamp(text) line 7 at RETURN
  • nls_timestamp_tz_format

    The initial value of the nls_timestamp_tz_format function is ''.

    You can execute the SET statement to set nls_timestamp_tz_format to a supported TIMESTAMPTZ data type. If the format is invalid, an error is reported.

    You can reset the value by executing the reset nls_timestamp_tz_format; statement.

    Examples of the YYYY/MM/DD HH24:MI:DD TZH:TZM format:
    • Set nls_timestamp_format to YYYY/MM/DD HH24:MI:DD TZH:TZM. 
      set nls_timestamp_tz_format to 'YYYY/MM/DD HH24:MI:SS TZH:TZM';
      Sample result: 
      set
    • Convert 2021-11-11 11:11:11 +8 in the YYYY/MM/DD HH24:MI:DD TZH:TZM format and then run the to_char function. 
      select to_char('2021-11-11 11:11:11 +8'::timestamptz);
      Sample result: 
                to_char
----------------------------
 2021/11/11 03:11:11 +00:00
(1 row)
    • Convert 2021/11/11 11:11:11 +8 in the YYYY/MM/DD HH24:MI:DD TZH:TZM format and then run the to_timestamp function. 
      select to_timestamp_tz('2021/11/11 11:11:11 +8'::text);
      Sample result: 
            to_timestamp_tz
----------------------------
 2021/11/11 03:11:11 +00:00
(1 row)
    • Display the timestamptz data type in the YYYY/MM/DD HH24:MI:DD TZH:TZM format. 
      select '2021/11/11 11:11:11 +8'::timestamptz
      Sample result: 
              timestamptz
----------------------------
 2021/11/11 03:11:11 +00:00
(1 row)
    Note
    The format of arg1 in to_timestamp_tz(arg1 TEXT) must exactly the same as the format specified by nls_timestamp_format. Otherwise, an error or an unexpected result is returned. 
    # Set nls_timestamp_tz_format to YYYY/MM/DD HH:MI:SS.FF TZH:TZM. 
set nls_timestamp_tz_format = 'YYYY/MM/DD HH:MI:SS.FF TZH:TZM';
# Sample result:
set
# Convert 2000-03-28 08:00:00 +8 in the YYYY/MM/DD HH:MI:SS.FF TZH:TZM format and then execute the to_timestamp function. 
select to_timestamp('2000-03-28 08:00:00 +8');
# Sample result:
ERROR:  date/time field value out of range: "2000-03-28 08:00:00 +8"
CONTEXT:  PL/pgSQL function to_timestamp(text) line 7 at RETURN