TIMESTAMP WITH TIME ZONE data type

TIMESTAMP WITH TIME ZONE is a variant of the TIMESTAMP data type. It stores a value that includes the name of a time zone region or the offset from Coordinated Universal Time (UTC). This topic describes the syntax, parameters, and usage notes of the TIMESTAMP WITH TIME ZONE data type.

Syntax

TIMESTAMP [(scale)] WITH TIME ZONE

Parameters

The precision of the TIMESTAMP [(scale)] WITH TIME ZONE data type depends on the value of scale, which ranges from 0 to 9. The maximum value is 9 (nanoseconds, which means the seconds are precise to the ninth decimal place), and the minimum value is 0 (seconds, which means the seconds are precise to the zeroth decimal place). The default value is 6.

Considerations

The TIMESTAMP [(scale)] WITH TIME ZONE data type stores information about the year, month, day, hour, minute, and second. The precision of the data type depends on the value of scale. Unlike TIMESTAMP [(scale)], the TIMESTAMP [(scale)] WITH TIME ZONE data type can also store time zone information. You can use this data type to store date and time values that span multiple geographic regions. The value range of the TIMESTAMP [(scale)] WITH TIME ZONE data type is from 0001-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999.

The default input and output formats of the TIMESTAMP WITH TIME ZONE data type are determined by the NLS_TIMESTAMP_TZ_FORMAT parameter. Run the following SQL statement to view the default format:

SELECT @@NLS_TIMESTAMP_TZ_FORMAT FROM DUAL;

The output is as follows:

DD-MON-RR HH.MI.SSXFF AM TZR

You can use conversion functions to customize the format of the data. When you insert data, you can use the TO_TIMESTAMP_TZ(char,fmt) function to specify the input format of the data. When you query data, you can use the TO_CHAR(datetime,fmt) function to specify the output format of the data. These two conversion functions convert strings to the format specified by the fmt parameter. If the fmt parameter is not specified, the default format is used.

OceanBase Database supports the insertion of time zone information in the form of time zone offsets and time zone region names.

• Time zone offset: the difference from Coordinated Universal Time (UTC) in hours and minutes.

• Time zone region name (TZR) and time zone abbreviation (TZD): the time zone abbreviation of a country or city.

Examples

The following examples use the TO_TIMESTAMP_TZ(char,fmt) function to insert timestamp values.

• Insert a value with a time zone offset.

  obclient> SELECT TO_TIMESTAMP_TZ('2020-01-01 11:00:00 -05:00','YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
  +-------------------------------------------------------------------------+
  | TO_TIMESTAMP_TZ('2020-01-0111:00:00-05:00','YYYY-MM-DDHH:MI:SSTZH:TZM') |
  +-------------------------------------------------------------------------+
  | 01-JAN-20 11.00.00.000000000 AM AMERICA/LOS_ANGELES                                  |
  +-------------------------------------------------------------------------+
  1 row in set
  

• Insert a value with a time zone region name and a time zone abbreviation.

  obclient> SELECT TO_TIMESTAMP_TZ('2020-01-01 11:00:00 America/Los_Angeles PST','YYYY-MM-DD HH:MI:SS TZR TZD') FROM DUAL;
  +-------------------------------------------------------------------------+
  | TO_TIMESTAMP_TZ('2020-01-01 11:00:00 America/Los_Angeles PST','YYYY-MM-DDHH:MI:SS TZR TZD') |
  +-------------------------------------------------------------------------+
  |01-JUN-20 11.00.00.000000000 AM America/Los_Angeles PST                                  |
  +-------------------------------------------------------------------------+
  1 row in set
  

Daylight Saving Time

OceanBase Database supports Daylight Saving Time (DST) and uses time zone abbreviations to represent DST information. For example, in the America/Los_Angeles time zone, DST is observed from the second Sunday in March to the first Sunday in November, and the standard time is observed otherwise. If the value inserted into the database contains only the time zone region name, OceanBase Database determines whether the time zone is in DST based on the inserted time and includes the time zone abbreviation in the result to indicate that DST is in effect.

Example:

obclient> SELECT TO_TIMESTAMP_TZ('2020-02-01 11:00:00 America/Los_Angeles','YYYY-MM-DD HH:MI:SS TZR') FROM DUAL;
obclient> SELECT TO_TIMESTAMP_TZ('2020-06-01 11:00:00 America/Los_Angeles','YYYY-MM-DD HH:MI:SS TZR') FROM DUAL;

The output is as follows:

01-JUN-20 11.00.00.000000000 AM America/Los_Angeles PST
01-JUN-20 11.00.00.000000000 AM America/Los_Angeles PDT