Timezone of SQL Server and Oracle

1. In our projects, we may encounter this situation. The database server and the client are in different time zones. Therefore, we need to consider the synchronization of time when designing our system, that is, different clients in different time zones can obtain the same time from the server. If your system must support both SQL Server and Oracle, you need to find a unified processing method, because SQL Server and Oracle have different solutions to this problem. This article describes how to use the. NET solution.
   The following describes the processing methods available in Oracle:

1. -------------------- ORACLE Server ----------------------------------------------------------------------------------
When creating table, specify the field as timestamp with time zone.
-------------------- Client restart --------------------------------------------------------------------------------------------
Retrieve data from the server: obtain the value of the field (datetime type), and the database provider will automatically process it.
Save data to the server: directly pass the value of the client datetime object
2. -------------------- ORACLE Server ----------------------------------------------------------------------------------
When creating table, specify the field as timestamp with local time zone.
-------------------- Client restart --------------------------------------------------------------------------------------------
Get data from the server: Get the value of the field (datetime type), and use tolocaltime of datetime to convert it to the time in the local time zone
Save data to the server: directly pass the value of the client datetime object
3. -------------------- ORACLE Server ----------------------------------------------------------------------------------
When creating table, specify the field as date and save it as UTC time on the server.
-------------------- Client restart --------------------------------------------------------------------------------------------
Get data from the server: Get the value of the field (datetime type), and use tolocaltime of datetime to convert it to the time in the local time zone
Save data to the server: Convert the client datetime object to UTC time and save it to the server
Storage Process and other server data storage: Get the UTC time and save it to the field
3. -------------------- ORACLE Server ----------------------------------------------------------------------------------
When creating table, specify the field as date/timstamp, and save it as the time in the server time zone on the server.
-------------------- Client restart --------------------------------------------------------------------------------------------
Note: When the client starts, it needs to obtain the time zone information of the server and client, which is used for subsequent time conversion.
Get data from the server: Get the value of the field (datetime type), and use tolocaltime of datetime to convert it to the time in the local time zone
Save data to the server: Convert the client datetime object to the UTC time, convert the UTC time to the server time, and save it to the server
Storage Process and other server data storage: Get the server time and save it to the field
Because SQL server does not have Oracle's processing mechanism for time zone, it can only adopt the above 3rd or 4th methods. Therefore, if your system supports both Oracle and SQL Server, we recommend that you use 3rd processing methods, because the client does not need to obtain time zone information from the server in the case of 4th processing methods.

Other problems:
1. the time zone used by the Oracle server exists in the database instance. It is specified during installation. The default GMT + is used.
2. the SQL Server server uses the OS time zone information.