The TIMESTAMP WITH LOCAL TIME ZONE data type is a variant of TIMESTAMP and is sensitive to time zone information. This topic describes the syntax, parameters, and considerations of the TIMESTAMP WITH LOCAL TIME ZONE data type.
Syntax
TIMESTAMP [(scale)] WITH LOCAL TIME ZONE
Parameters
The threshold and precision of TIMESTAMP [(scale)] depend on the value of the scale parameter. The value range is [0,9]. The maximum value 9 and minimum value 0 respectively specify that the value is accurate to nanoseconds and seconds. The default value 6 specifies that the value is accurate to microseconds.
Considerations
The time zone information in the TIMESTAMP [(scale)] WITH LOCAL TIME ZONE data type is the time zone in which the current session occurs. The value range is [0001-01-01 00:00:00.000000000,9999-12-31 23:59:59.999999999]. Compared with TIMESTAMP [(scale)] WITH TIME ZONE, this data type does not require you to specify the time zone information. OceanBase Database directly stores the timestamp in the default time zone +00:00. This time zone does not change. When you retrieve data, OceanBase Database returns the data in your local session time zone. This time zone can change. This data type is useful for displaying date information in the time zone of the client system in a two-tier application. The default format of the TIMESTAMP WITH LOCAL TIME ZONE data type is determined by the NLS_TIMESTAMP_FORMAT parameter. Execute the following SQL statement to view the date format:
SELECT @@NLS_TIMESTAMP_FORMAT FROM DUAL;
The returned result is as follows:
DD-MON-RR HH.MI.SSXFF AM
You can use a conversion function to define a data format. You can use the TO_CHAR (datetime,fmt) function to specify the data output format. This conversion function converts strings to the format specified by the fmt argument. If the fmt argument is not specified, TO_CHAR returns data in the default format of the data type. For more information about the input format of the TIMESTAMP WITH LOCAL TIME ZONE data type, see Timestamp literals.
The value of the TIMESTAMP WITH LOCAL TIME ZONE data type is not specified based on any special literal. The time zone information of the local session is returned based on the SESSIONTIMEZONE parameter. The value of SESSIONTIMEZONE is specified based on the value of the customized parameter TIME_ZONE, as shown in the following example:
obclient> CREATE TABLE LocalTZ ( ltzcol TIMESTAMP WITH LOCAL TIME ZONE);
Query OK, 0 rows affected
obclient> INSERT INTO LocalTZ VALUES (TIMESTAMP '2020-02-25 11:10:08.123');
Query OK, 1 row affected
obclient> ALTER SESSION SET TIME_ZONE='+08:00';
Query OK, 0 rows affected
obclient> SELECT SESSIONTIMEZONE, ltzcol FROM LocalTZ;
+-----------------+------------------------------+
| SESSIONTIMEZONE | LTZCOL |
+-----------------+------------------------------+
| +08:00 | 25-FEB-20 11.10.08.123000 AM |
+-----------------+------------------------------+
1 row in set
You can modify the value of SESSIONTIMEZONE by modifying the value of the custom parameter TIME_ZONE, as shown in the following example:
obclient> ALTER SESSION SET TIME_ZONE='+00:00';
Query OK, 0 rows affected
obclient> SELECT SESSIONTIMEZONE, ltzcol FROM LocalTZ;
+-----------------+------------------------------+
| SESSIONTIMEZONE | LTZCOL |
+-----------------+------------------------------+
| +00:00 | 25-FEB-20 11.10.08.123000 AM |
+-----------------+------------------------------+
1 row in set