nls_timestamp_tz_format

Description

The nls_timestamp_tz_format variable controls the format of timestamp tz values when they are converted to strings, and the format of strings when they are implicitly converted to timestamp tz values.

Privilege requirements

• Query variables

  • Global level

    The sys tenant and all user tenants can execute the SHOW VARIABLES statement or view the SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view (Oracle mode) to query the value of a global system variable.

  • Session level

    The sys tenant and all user tenants can execute the SHOW VARIABLES statement or view the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view (Oracle mode) to query the value of a session system variable.

• Modify variables

  • Set the variable at the global level

    • The sys tenant can directly modify the value of a global system variable.

    • An Oracle user tenant must have the ALTER SYSTEM privilege to modify the value of a global system variable.

  • Set the variable at the session level

    The sys tenant and all user tenants can directly modify the value of a session system variable in their own tenant.

Attributes

Considerations

This variable specifies the default time zone format for timestamp values when they are used with the TO_CHAR and TO_TIMESTAMP_TZ functions. The default value is YYYY-MM-DD HH24:MI:SS.FF TZR TZD.

Examples

• Set the variable at the session level

  obclient> SET nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
  

• Set the variable at the global level

  obclient> SET GLOBAL nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
  

References

• to_CHAR
• to_TIMESTAMP
• Date and time expressions
• Considerations for data conversion