TIMESTAMP WITH TIME ZONE data type

The TIMESTAMP WITH TIME ZONE data type is a variant of TIMESTAMP that includes a time zone offset or time zone region (TZR) name in its value. This topic describes the syntax, parameters, and considerations of the TIMESTAMP WITH TIME ZONE data type.

Syntax

TIMESTAMP [(scale)] WITH TIME ZONE

Parameters

The threshold and precision of TIMESTAMP [(scale)] WITH TIME ZONE depend on the value of the scale parameter. The value range is [0,9]. The minimum value 0 indicates that the timestamp is accurate to seconds, the maximum value 9 indicates that the timestamp is accurate to nanoseconds, and the default value 6 indicates that the timestamp is accurate to microseconds.

Considerations

TIMESTAMP [(scale)] WITH TIME ZONE is used to store time information such as year, month, day, hour, minute, and second. The threshold and precision are determined by the value of the scale parameter. Different from TIMESTAMP [(scale)], TIMESTAMP [(scale)] WITH TIME ZONE can store time zone information and it is generally used to store datetime information across geographic regions. The value range is [0001-01-01 00:00:00.000000000,9999-12-31 23:59:59.999999999].

The default format for the TIMESTAMP WITH TIME ZONE data type is determined by the NLS_TIMESTAMP_TZ_FORMAT parameter. Execute the following SQL statement to view the default format:

SELECT @@NLS_TIMESTAMP_TZ_FORMAT FROM DUAL;

The return result is as follows:

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

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

When you insert a time zone, you can use the time zone offset and TZR to specify the time zone.

• A time zone offset is the difference (in hour and minute) from the Greenwich Mean Time (GMT).

• The TZR and TZD indicate the abbreviated name of a country or city. TZD represents an abbreviated form of the time zone region with Daylight Saving Time (DST) information.

Examples

In the following example, the timestamp value is entered by using TO_TIMESTAMP_TZ(char,fmt).

• Insert a timestamp that has the 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 timestamp that has the TZR and TZD information

  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
  

DST

OceanBase Database supports DST and uses TZD to indicate the DST information. Take America/Los_Angeles as an example. The Pacific Daylight Time (PDT) starts from the second Sunday of March of a year and ends on the first Sunday of November in the same year. The rest time of the year is referred to as the Pacific Standard Time (PST). When the inserted value includes only the TZR, OceanBase Database determines whether the inserted TZR is in the DST range based on the inserted time information and includes the TZD in the returned result to specify that the current time is DST.

Here is an 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 return result 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