What about database time zones – Oracle’s time zone processing

Time:2019-1-28

Original address

What happens when the JVM time zone is inconsistent with the database time zone? You may never have noticed this, but when you container Java programs, the problem arises because almost all of the Docker Image time zones are UTC. This article explores how Oracle and its JDBC drivers handle time zones and tries to give best practices.

Let’s summarize first.

  • DATEandTIMESTAMPTypes do not support time zone conversion.
  • If the application does not match Oracle’s time zone, then you should useTIMESTAMP WITH LOCAL TIME ZONE

    • For JDBC programs, just keep the JVM time zone consistent with the user time zone.
  • If the application and Oracle’s time zone are inconsistent and time zone information needs to be saved, then you should useTIMESTAMP WITH TIME ZONE
  • Formatting Date-Time String FunctionTO_CHAR

    • aboutTIMESTAMP WITH TIME ZONEFor example, useTO_CHARBe careful to let it output time zone information.TZH:TZM TZR TZDOtherwise the result will be truncated.
    • aboutTIMESTAMP WITH LOCAL TIME ZONEFor example, useTO_CHARThe result returned converts the time zone.
  • The function of the current date and time:

    • Do not use unless necessarySYSDATEandSYSTIMESTAMPThis returns the time of the operating system on which the database resides.
    • Use as much as possibleCURRENT_TIMESTAMPIt returnsTIMESTAMP WITH TIME ZONECan be used for safe comparison of time.

Time zone of date and time type

Oracle Datetime Datatypes have several types:

  • DATE, saveYYYY-MM-DD HH24:MI:SS
  • TIMESTAMP, ratioDATEMultiple fractional seconds are saved.FF)。
  • TIMESTAMP WITH TIME ZONETIMESTAMPMore time zone offsets (e.g. +08:00,TZH:TZMOr Time Zone Area Name (e.g. Asia/Shanghai,TZR(and daylight saving time markers)TZD)。
  • TIMESTAMP WITH LOCAL TIME ZONE. andTIMESTAMPSimilarly, the stored data is standardized as the time zone of the database, which is converted to the user time zone when the user acquires it (for JDBC, the JVM time zone).
docker run --name oracle-xe-timezone-test \
  -e ORACLE_ALLOW_REMOTE=true \
  -p 1521:1521 \
  -d wnameless/oracle-xe-11g:16.04

Then login to Oracle with system/oracle user and execute the following SQL table building:

create table test (
  date_field date,
  ts_field timestamp,
  ts_tz_field timestamp with time zone,
  ts_ltz_field timestamp with local time zone
);

To verify this conclusion, I wrote a program to experiment, which did three things:

  1. Use the Asia/Shanghai time zone to construct a date java.util.Date: 2018-09-1410:00:00 and insert it into the database.
  2. Use the Asia/Shanghai time zone to look up this value again and see the results.
  3. Using the Asia/Shanghai time zone, get the formatted string of this field (using the DATE_FORMAT() function).
  4. Repeat steps 2-3 using the European/Paris time zone.

Running the program yields the following results:

JVM Time Zone: China Standard Time
Retrieve java.util.Date from DATE column                              : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP column                         : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP WITH TIME ZONE column          : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP WITH LOCAL TIME ZONE column    : 2018-09-14 10:00:00.0
Retrieve formatted string from DATE column                            : 2018-09-14 10:00:00
Retrieve formatted string from TIMESTAMP column                       : 2018-09-14 10:00:00
Retrieve formatted string from TIMESTAMP WITH TIME ZONE column        : 2018-09-14 10:00:00 +08:00 ASIA/SHANGHAI CST
Retrieve formatted string from TIMESTAMP WITH LOCAL TIME ZONE column  : 2018-09-14 10:00:00
--------------------
JVM Time Zone: Central European Time
Retrieve java.util.Date from DATE column                              : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP column                         : 2018-09-14 10:00:00.0
Retrieve java.util.Date from TIMESTAMP WITH TIME ZONE column          : 2018-09-14 04:00:00.0
Retrieve java.util.Date from TIMESTAMP WITH LOCAL TIME ZONE column    : 2018-09-14 04:00:00.0
Retrieve formatted string from DATE column                            : 2018-09-14 10:00:00
Retrieve formatted string from TIMESTAMP column                       : 2018-09-14 10:00:00
Retrieve formatted string from TIMESTAMP WITH TIME ZONE column        : 2018-09-14 10:00:00 +08:00 ASIA/SHANGHAI CST
Retrieve formatted string from TIMESTAMP WITH LOCAL TIME ZONE column  : 2018-09-14 04:00:00

As you can see,DATEandTIMESTAMPTime zone conversion is not supported, in factDATEandTIMESTAMPTime zone information is discarded.

aboutTIMESTAMP WITH TIME ZONEFor example, useTO_CHARBe careful to let it output time zone information.TZH:TZM TZR TZDOtherwise the result will be truncated.

aboutTIMESTAMP WITH LOCAL TIME ZONEFor example, useTO_CHARThe result returned converts the time zone.

Current date-time correlation function

Oracle has several functions related to the current time:

  • CURRENT_DATEThe return isDATEtype
  • CURRENT_TIMESTAMPThe return isTIMESTAMP WITH TIME ZONEtype
  • LOCALTIMESTAMPThe return isTIMESTAMPtype
  • SYSDATEThe return isDATEtype
  • SYSTIMESTAMPThe return isTIMESTAMPtype

Write a program, the output is as follows:

=========TEST CURRENT DATE/TIME FUNCTIONS===========
JVM Time Zone: China Standard Time
Test CURRENT_DATE           : 2018-09-18 10:27:23.0
Test CURRENT_TIMESTAMP      : 2018-09-18 10:27:23.880378 Asia/Shanghai
Test LOCALTIMESTAMP         : 2018-09-18 10:27:23.926375
Test SYSDATE                : 2018-09-18 02:27:23.0
Test SYSTIMESTAMP           : 2018-09-18 02:27:23.929605 +0:00
--------------------
JVM Time Zone: Central European Time
Test CURRENT_DATE           : 2018-09-18 04:27:45.0
Test CURRENT_TIMESTAMP      : 2018-09-18 04:27:45.429024 Europe/Paris
Test LOCALTIMESTAMP         : 2018-09-18 04:27:45.482485
Test SYSDATE                : 2018-09-18 02:27:45.0
Test SYSTIMESTAMP           : 2018-09-18 02:27:45.48582 +0:00

It can be found that,CURRENT_DATECURRENT_TIMESTAMPLOCALTIMESTAMPThe results are converted according to the client time zone. andSYSDATEandSYSTIMESTAMPReturns the time in which the database is in the operating system’s time zone.

Operating in Oracle Client Time Zone

Query system time zone and session time zone
SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

- Setting session time zone
ALTER SESSION SET TIME_ZONE='Asia/Shanghai';

See Setting the Database Time Zone and Setting the Session Time Zone

Reference material

  • Oracle Datetime Datatypes
  • Oracle and current time-related functions
  • Oracle Datetime Comparisons
  • Setting the Database Time Zone
  • Setting the Session Time Zone
  • Oracle JDBC Connection Constant Field Values
  • W3C- Working with timezone

Related code

https://github.com/chanjarste…

Recommended Today

WPF: (2) thread example

Source project address: https://github.com/Microsoft/…The following is the conversion of the sample into a brief description, and gives the actual operation effect and key code: 1 MultiThreadingWebBrowser private void Browse(object sender, RoutedEventArgs e) { placeHolder.Source = new Uri(newLocation.Text); } private void NewWindowHandler(object sender, RoutedEventArgs e) { var newWindowThread = new Thread(ThreadStartingPoint); newWindowThread.SetApartmentState(ApartmentState.STA); newWindowThread.IsBackground = true; newWindowThread.Start(); […]