You're reading from Developing Robust Date and Time Oriented Applications in Oracle Cloud

Product typeBook

Published inMay 2023

PublisherPackt

ISBN-139781804611869

Edition1st Edition

Tools

Oracle Database

Concepts

Databases

Author (1)

Michal Kvet

Timestamp Enhancements

One could say that life would be better and more secure if we had everything under control. In the past, businesses were entirely managed locally. Servers were placed in a local server room directly in the company, supervised by the system and database administrators. Employees knew administrators and technicians personally, mostly through filing complaints if something was not working as expected. Individual systems were locally managed and people were able to meet physically, with meetings coordinated by the locally defined time. Simply put, there was no need to deal with date and time synchronization.

With the globalization of business, multiple branch offices began to be created, opened, and managed, resulting in sharing systems and responsibilities. Customers started to spread across the world. This resulted in the necessity to coordinate activities by date and time. To ensure correctness and robustness, data needed to be managed not just locally in one...

Applying time zones for the server and client sides

It wasn’t that long ago when all applications and systems were stored locally. Offices usually included a computer that formed the central storage with a local network, often without direct access to the internet. Later, as these local devices got bigger, louder, and produced too much heat, local servers were moved to the server room of the company. Technicians and administrators were responsible for the proper functioning of individual hardware components, accessibility, and availability. Soon it became clear that one room was not enough and the data must be geographically distributed to ensure robustness and, more importantly, security. The idea was that even if any disaster or accident occurred, such as a fire, flood, or earthquake, there would always be at least one surviving repository that could handle requests and access the systems and data. However, the systems became increasingly complex, and ensuring all the features...

TIMESTAMP and transformation across time zones

When dealing with higher-value precision, the TIMESTAMP data type is available to serve up to nanosecond precision. There are also two functions for getting the current date and time representation: systemtimestamp and current_timestamp. The systemtimestamp function is part of the STANDARD package owned by the SYS user. The full name (owner.package_name.function_name) does not need to be specified since the public synonym is already specified. Thus, just using the name in the direct reference is sufficient. It provides the time zone on the server (database) side. The following code shows the systimestamp function call:

select SYS.STANDARD.systimestamp from dual;
select systimestamp from dual;
--> 08.03.22 13:53:10,324000000 +00:00

The reference of the local timestamp of the session can be obtained by a current_timestamp function call, which is part of the STANDARD package owned by the SYS user as well:

select SYS.STANDARD.current_timestamp...

Transforming DATE values across multiple time zones using the NEW_TIME function

The NEW_TIME function transforms the DATE value using input_timezone (time zone of the source) and output_timezone (time zone of the destination). It returns the DATE value, regardless of the implicit conversion of the input. All parameters need to be specified. If the original DATE value provided is NULL, then the output is NULL as well:

NEW_TIME(<p_date>, <input_time_zone>, <output_timezone>)

The limitation of the NEW_TIME method is associated with the list of available time zones that can be referenced. The following table shows a list of available values:

TIMESTAMP normalization

In the previous section, there was an analysis of time zone management by transforming values across multiple zones or by adding a time zone definition from the pure TIMESTAMP value origin.

The world is divided into several time zones that differ by hour or half an hour intervals. Standard time is calculated by the number of hours offset from UTC. UTC provides the precise time secured by multiple laboratories (such as the US Naval Observatory) to serve as a referential value. UTC is a successor of GMT, in that it has taken over as the international standard time where GMT was previously used. Thus, UTC is a referential value to which individual regions apply defined time zones.

There are some examples of shift in the following table. Besides the time zone management itself, a difference related to summer and winter time can be identified as well:

Time zone value reference	Meaning
AST, ADT	Atlantic Standard or Daylight Time
BST, BDT	Bering Standard or Daylight Time
...

...

Local value reflection using TIMESTAMP data type extensions

To cover the complexity of time zone management, local timestamp reflection should also be highlighted. This section introduces data types forming the TIMESTAMP type extension. In principle, three TIMESTAMP data types are available:

TIMESTAMP does not cover time zone elements. It is mostly used for backward compatibility or values that do not need to reference time zones at all.
TIMESTAMP WITH TIME ZONE maps the time zone to the value by the offset extension. Using this data type, particular values can be time zone-shifted and coordinated across regions. Thus, it is normalized across the database.
TIMESTAMP WITH LOCAL TIME ZONE is a specific type that does not state the time zone explicitly. Instead, the transformation is applied directly. Thus, the original value of the hour and minute elements is recalculated and provided to the user depending on their time zone.

Let’s create a simple table...

Local versus global expansion

Let’s use the example of sales management to highlight the importance of proper time zone application. A shop in a small town has just one cash desk consisting of a computer, which is also the central data repository. Trade prospers, sales increase, and the business needs to be expanded. The owner rents another space, and the number of cash desks has increased. So, the local computer is no longer enough.

The owner needs a server to be located in their office. However, the business is still thriving, and therefore the owner opens more and more shops in the city. Over time, operations have sprung up across the country. Although the server is still located in a local office room, it is clear that such a solution is not robust, and the approach must be changed. As a result, data is migrated to the local server room operated by an external company supervising the architecture, accessibility, and hardware. This decision does not bring any significant...

Summary

The topics covered in this chapter are particularly important in the context of business expansion, in which it is necessary to synchronize events across multiple regions and, in general, the entire world. In this chapter, you learned about data types and synchronization techniques. You also learned about the complexity of the local and database date and time, focusing on time zone shift.

Additionally, in this chapter, the TIMESTAMP data type extensions were highlighted, preceded by the FROM_TZ and NEW_TIME functions. Time zone reflection and shift processing are critical. You probably wouldn’t like to arrive at an online meeting an hour after it ended, right? So, now, you are prepared to trade worldwide and offer your solutions anywhere, as well as organize webinars and meetings with participants from any region.

In this chapter, session and database date and time were discussed, dealing with the sysdate and current_date differences, as well as systimestamp and...

Questions

How do we get the client time zone? Which function can be used?
1. The SESSIONTIMEZONE function of the STANDARD package
2. The MYTIMEZONE function of the STANDARD package
3. The UTC_SHIFT function of the STANDARD package
4. The DBTIMEZONE function of the STANDARD package
Choose the best option specifying the database time zone:
1. TZH only
2. TZH:TZM only
3. Name representation only
4. TZH:TZM and name representation
What data type is used for the column of the following select statement?
```
select FROM_TZ(cast(sysdate as TIMESTAMP), '5:00')
               AT TIME ZONE 'Europe/Brussels'
  from dual;
```
1. DATE
2. TIMESTAMP
3. TIMESTAMP WITH TIME ZONE
4. TIMESTAMP WITH LOCAL TIME ZONE
Which function takes three parameters – input timestamp values and the original and destination time zone to be calculated – applying the time shift for the input value?
1. FROM_TZ
2. NEW_TIME
3. TIMESTAMP
4. AT TIME ZONE
Which...

Refer to DB System Time Zone in the Oracle documentation. It focuses on the time zone options in OCI. It also provides a step-by-step guide to changing the time zone after database provisioning. It is available through this link: https://docs.oracle.com/en-us/iaas/Content/Database/References/timezones.htm.

Check out Date and time localization by Shanika Wickramasinghe. This paper provides a date and time localization guide in various languages, including Python, Java, and JavaScript. It is recommended to gain insights into the principles and usage in other systems: https://lokalise.com/blog/date-time-localization/.

The rest of the chapter is locked

You have been reading a chapter from

Developing Robust Date and Time Oriented Applications in Oracle Cloud

Published in: May 2023Publisher: PacktISBN-13: 9781804611869

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Author (1)

Michal Kvet

Michal Kvet is a researcher, educator, and database expert at the University of Žilina in Slovakia. His primary focus areas are databases, analytics, performance, and cloud computing. He works closely with Oracle and Oracle Academy. He is the co-author of multiple textbooks (a SQL and PL/SQL cookbook, a book on APEX application development, a book on temporal databases, and a MySQL cookbook), coordinates multiple Erasmus+ projects and co-organizes several research conferences and database workshops. Besides this, he supervises engineering projects and bachelor's, master's, and doctoral theses. Over the years, his research has been associated with date and time management and temporal databases. He has Oracle's SQL, PL/SQL, Cloud, Analytics, and Administration certifications. His core knowledge of temporality is provided to you in this book.
Read more about Michal Kvet

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages

Type	Representation

You're reading from Developing Robust Date and Time Oriented Applications in Oracle Cloud

Timestamp Enhancements

Applying time zones for the server and client sides

TIMESTAMP and transformation across time zones

Transforming DATE values across multiple time zones using the NEW_TIME function

TIMESTAMP normalization

Local value reflection using TIMESTAMP data type extensions

Local versus global expansion

Summary

Questions

Further reading

Unlock this book and the full library FREE for 7 days

Author (1)

Et al.

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

Mastering Tableau 2023

Building AI Applications with ChatGPT APIs

Building AI Applications with ChatGPT APIs

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

Modern Data Architecture on AWS

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

TinyML Cookbook