Oracle Cloud Time Zone Reflection

In the past, overall system complexity and performance were handled by the administrator. Date and time reflection was covered by the server, which ensured it was properly managed irrespective of the session definitions. As a result, DATE and TIMESTAMP values were obtained by the sysdate and systimestamp functions, respectively. With globalization, this is no longer the case. The server time zones cannot manage data because different regions and associated time zones must be taken into consideration. Therefore, session perspective reflection should be used.

Moreover, another problem can be if customers want to move their databases to a cloud environment. We often get questions related to the process of moving databases to the cloud. Time management suddenly becomes difficult because there is no proper reference to the time points. It is not enough for the server to use UTC as its reference; time zones must be properly set and evaluated. Dedicated...

Summarizing time zone management

Nowadays, data is no longer placed and processed locally. Instead, cloud technologies are used. Oracle provides many cloud locations allowing the provisioning of databases, infrastructure, and many other resources. The number of cloud locations is continuously rising, offering you a wide range of technologies and parameters. But the greatest advantage is, you do not need to hire system administrators. You do not need to manage and optimize the structure. You do not need to maintain hardware. The cloud vendors supervise all the infrastructure, management, patching, and global availability. All data is encrypted, secured, and duplicated, so robustness and reliability can be ensured. Thanks to that, more and more systems are migrated to the cloud to minimize costs and maximize performance. In terms of time management, different temporal aspects must be highlighted. Namely, there is the region where the cloud data center is located. There is also the UTC...

Exploring SQL translation profiles

A SQL translation profile can serve as the first solution for dynamic SQL transformation. Although it is primarily intended to transform non-Oracle SQL syntax, it can also be used internally to transform date function calls and representations. The DBMS_SQL_TRANSLATION package and its methods power the management of the SQL translation profile. Simply, the translation profile is created and given a name (in our case, it is called DATE_PROF), and then the query transformation registration is carried out. Multiple statements can be registered for the transformation by using the REGISTER_SQL_TRANSLATION procedure of the DBMS_SQL_TRANSLATION package.

The REGISTER_SQL_TRANSLATION procedure takes three parameters – the name of the SQL translation profile (parameter 1), the original statement (parameter 2), and the transformed statement (parameter 3). In our case, we transform two statements by referencing the sysdate and systimestamp functions...

Simplifying time zone management shift using SYSDATE_AT_DBTIMEZONE

As mentioned, another parameter has been introduced recently – SYSDATE_AT_DBTIMEZONE – which provides the same functionality as the previously described translation profiles. It is associated with the session and converts sysdate references to local client date and time values by specifying the precise difference between the database and client time zones. The following commands show the principles and results:

select dbtimezone from dual;
--> +09:00
alter session set SYSDATE_AT_DBTIMEZONE=false;
select sysdate, current_date from dual;
--> SYSDATE                CURRENT_DATE
--> 08.02.2022 14:56:00    08.02.2022 14:56:00
alter session set SYSDATE_AT_DBTIMEZONE=true;
select sysdate, current_date from dual;
--> SYSDATE            ...

Summary

Global events must take into account different time zone regions to ensure synchronization. Date and time data types can reflect time zones, delimited by the name or hour and minute offset.

Databases are commonly set to UTC using value normalization. However, many systems do not respect these principles and developers need to adjust the values to ensure synchronization. As a result, methods in the original code need to be transformed from the server perspective to the client perspective. This chapter dealt with the date and time value transformation provided by SQL translation profiles, which was explained in the first part of the chapter. This transformation of the original statement to the new one is applied automatically after the user specifies the query.

An alternative solution that provides the same results as using SQL translation profiles is using the SYSDATE_AT_DBTIMEZONE session parameter, which is, however, only concerned with the date and time perspective...

Questions

1. Which command correctly invalidates the translation profile called PROF1 for the session?
   1. alter session set sql_translation_profile=null;
   2. set sql_translation_profile=null;
   3. unset sql_translation_profile=PROF1;
   4. alter session deregister sql_translation_profile= PROF1;
2. What will the output of the last query in the following block be if the SYSDATE_AT_DBTIMEZONE parameter value is enabled?

     alter database set time_zone='-06:00';
       -- database system restart
     alter session set time_zone='+10:00';
     alter session set SYSDATE_AT_DBTIMEZONE=true;
   select (sysdate - current_date)*24 from dual;

   1. 0
   2. -10
   3. -16
   4. 16

Further reading

Now that we have reached the end of the book, we would like to direct you to further resources to continue your study of the development of data-driven applications. The Oracle Cloud environment offers an ideal solution through the Oracle Application Express (APEX) technology. It is a low-code development platform offering the ability to create scalable, robust, secure, and reliable solutions that are deployable in the cloud, making it accessible worldwide. Four books are listed here that will guide you through the world of application development in the Oracle APEX environment:

• Oracle APEX Cookbook by Marcel van der Plas and Michel van Zoest. This book shows you how to create web applications by focusing on the concepts, principles, and tips to develop the solution quickly.
• Extending Oracle Application Express with Oracle Cloud Features: A Guide to Enhancing APEX Web Applications with Cloud-Native and Machine Learning Technologies by Adrian Png and Heli...