TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that is sensitive to time zone information. This topic describes the syntax, parameters, and usage of the TIMESTAMP WITH LOCAL TIME ZONE data type.
Syntax
TIMESTAMP [(scale)] WITH LOCAL TIME ZONE
Parameters
The TIMESTAMP [(scale)] data type has a precision of scale seconds, where scale is an integer in the range [0,9]. The maximum value is 9 (nanoseconds), the minimum value is 0 (seconds), and the default value is 6 (microseconds).
Considerations
The TIMESTAMP [(scale)] WITH LOCAL TIME ZONE data type stores the time zone of the current session. The value range is 0001-01-01 00:00:00.000000000~ 9999-12-31 23:59:59.999999999. It differs from the TIMESTAMP [(scale)] WITH TIME ZONE data type in that the time zone is not specified by the user. Instead, OceanBase Database stores the default database time zone, which is +00:00 (and cannot be changed). When you retrieve data, OceanBase Database returns the local session time zone (which can be changed). This data type is commonly used to display date information in the time zone of the client system in a two-tier application. The default input and output format of the TIMESTAMP WITH LOCAL TIME ZONE data type is determined by the NLS_TIMESTAMP_FORMAT parameter. Run the following SQL statement to view the date and time format:
SELECT @@NLS_TIMESTAMP_FORMAT FROM DUAL;
The output is as follows:
DD-MON-RR HH.MI.SSXFF AM
If you need to customize the data format, you can use conversion functions. You can use the TO_CHAR (datetime,fmt) function to specify the output format of the data. This conversion function converts the string to the format defined by the fmt parameter. If the fmt parameter is not specified, TO_CHAR returns the data in the default format for the data type. For more information about the input format of the TIMESTAMP WITH LOCAL TIME ZONE data type, see Timestamp literals.
The TIMESTAMP WITH LOCAL TIME ZONE data type does not have a dedicated literal. Instead, the time zone is determined by the SESSIONTIMEZONE function, which returns the local session time zone. The value of SESSIONTIMEZONE is obtained from the custom parameter TIME_ZONE. Here is an 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 change the value of the custom parameter TIME_ZONE to modify SESSIONTIMEZONE. Here is an 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