nls_timestamp(_tz)_format

nls_timestamp_format and nls_timestamp_tz_format are session-level configuration parameters in PolarDB for PostgreSQL (Compatible with Oracle) that control how timestamp and timestamptz values are formatted and parsed.

How it works

nls_timestamp_format affects the following:

The output format of the timestamp data type
The conversion behavior of pg_catalog.to_char(arg1 timestamp) when called without an explicit format argument
The conversion behavior of pg_catalog.to_timestamp(arg1 text) when called without an explicit format argument

nls_timestamp_tz_format affects the following:

The output format of the timestamptz data type
The conversion behavior of pg_catalog.to_char(arg1 timestamptz) when called without an explicit format argument
The conversion behavior of pg_catalog.to_timestamp_tz(arg1 text) when called without an explicit format argument

Note

TIMESTAMP and TIMESTAMP without time zone are the same data type. TIMESTAMPTZ and TIMESTAMPTZ with time zone are the same data type.

Parameter properties

Property	`nls_timestamp_format`	`nls_timestamp_tz_format`
Type	String	String
Default value	`''` (empty string)	`''` (empty string)
Scope	Session	Session
Set with	`SET nls_timestamp_format TO 'format'`	`SET nls_timestamp_tz_format TO 'format'`
Reset with	`RESET nls_timestamp_format`	`RESET nls_timestamp_tz_format`
On invalid format	Error	Error

Supported format tokens

Use the following tokens to compose a format string:

Token	Description	Example
`YYYY`	4-digit year	`2021`
`MM`	Month (01–12)	`11`
`DD`	Day of month (01–31)	`11`
`HH`	Hour of day, 12-hour (01–12)	`11`
`HH24`	Hour of day, 24-hour (00–23)	`11`
`MI`	Minute (00–59)	`11`
`SS`	Second (00–59)	`11`
`FF`	Fractional seconds	`.123456`
`TZH`	Time zone hours offset	`+08`
`TZM`	Time zone minutes offset	`00`

Usage

nls_timestamp_format

The following examples use YYYY/MM/DD HH24:MI:SS as the format string.

Set the format:

SET nls_timestamp_format TO 'YYYY/MM/DD HH24:MI:SS';

set

Convert a timestamp to a string with `to_char`:

SELECT to_char('2021-11-11 11:11:11'::timestamp);

      to_char
---------------------
 2021/11/11 11:11:11
(1 row)

Parse a string to a timestamp with `to_timestamp`:

SELECT to_timestamp('2021/11/11 11:11:11'::text);

    to_timestamp
---------------------
 2021/11/11 11:11:11
(1 row)

Display a `timestamp` value in the configured format:

SELECT '2021-11-11 11:11:11'::timestamp;

      timestamp
---------------------
 2021/11/11 11:11:11
(1 row)

Important

The input string passed to to_timestamp(arg1 text) must exactly match the format set by nls_timestamp_format. A mismatch causes an error or an unexpected result. For example, if nls_timestamp_format is set to YYYY/MM/DD HH:MI:SS.FF, passing '2000-03-28 08:00:00' (which uses - separators, not /) returns an error:

SET nls_timestamp_format = 'YYYY/MM/DD HH:MI:SS.FF';
SELECT to_timestamp('2000-03-28 08:00:00');

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

Reset to the default:

RESET nls_timestamp_format;