nls_timestamp(_tz)_format - PolarDB - Alibaba Cloud Documentation Center

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