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

Product type Book

Published in May 2023

Publisher Packt

ISBN-13 9781804611869

Pages 464 pages

Edition 1st Edition

Languages

Concepts

Databases

Author (1):

Michal Kvet

Table of Contents (26) Chapters

Preface

Part 1: Discovering Oracle Cloud

Chapter 1: Oracle Cloud Fundamentals

Chapter 2: Data Loading and Migration Perspectives

Part 2: Understanding the Roots of Date and Time

Chapter 3: Date and Time Standardization Principles

Chapter 4: Concepts of Temporality

Part 3: Modeling, Storing, and Managing Date and Time

Chapter 5: Modeling and Storage Principles

Chapter 6: Conversion Functions and Element Extraction

Chapter 7: Date and Time Management Functions

Chapter 8: Delving into National Language Support Parameters

Part 4: Modeling Validity Intervals

Chapter 9: Duration Modeling and Calculations

Chapter 10: Interval Representation and Type Relationships

Chapter 11: Temporal Database Concepts

Chapter 12: Building Month Calendars Using SQL and PL/SQL

Part 5: Building Robust and Secure Temporal Solutions

Chapter 13: Flashback Management for Reconstructing the Database Image

Chapter 14: Building Reliable Solutions to Avoid SQL Injection

Part 6: Expanding a Business Worldwide Using Oracle Cloud

Chapter 15: Timestamp Enhancements

Chapter 16: Oracle Cloud Time Zone Reflection

Assessments

Index

Why subscribe?

Other Books You May Enjoy

Date and Time Management Functions

Date and time arithmetic is not suitable for ensuring the complexity of element management and arithmetic shift. Specifically, although it is possible to add or remove a defined number of days (or hours) for a specific date, processing at a monthly level would be quite complicated. If we want to reference an event that takes place in a month, it is not enough to add 30 days, because some months have 31 days, and February only 28 or 29. Also, changing the value of the month element does not provide a relevant solution. For example, if it is January 31, adding one month to the month element would result in February 31, which does not exist. Moreover, transitioning through the years should be emphasized.

This chapter deals with the available functions related to date and time management. The first function is ADD_MONTHS, which accurately solves the described problems. A similar problem is then related to the number of months that have elapsed between...

Getting to know the ADD_MONTHS function

Mathematical arithmetical operations that are done on the DATE value express the day granularity. Thus, adding a value of 1 to the DATE value results in tomorrow being reflected while the original time values remain the same.

The ADD_MONTHS function has two parameters – the date value (date_val) and the number of months (number_months) to be added or subtracted. The result is the input date with processed months. Such functionality can also be done explicitly by parsing the DATE attribute. However, the transition between years and months must be handled explicitly.

The syntax of the ADD_MONTHS function is stated in the following line:

ADD_MONTHS(<date_val>, <number_months>)

The following code snippet highlights its usage. The shift of the month, denoted by the second parameter of the ADD_MONTH function, can be positive as well as negative, expressing adding or subtracting months. The first statement adds 7 months...

Identifying the number of days in a month using LAST_DAY

The LAST_DAY function returns the last day of the month based on the input date value (date_val). This is easy, right? January has 31 days and December has 31 days. However, what about February? Refer to the leap year. Thus, as is evident, this particular function is really useful and it must be available.

The syntax of this function can be seen in the following code block:

LAST_DAY(<date_val>)

The following statement provides you with the last day of October:

select LAST_DAY(TO_DATE('10.1.2022', 'DD.MM.YYYY'))
  from dual;
--> 31.01.2022

Naturally, it also manages leap years. The year 2023 is not a leap year, but the year 2024 is a leap year:

select LAST_DAY(TO_DATE('15.2.2023', 'DD.MM.YYYY'))
  from dual;
--> 28.2.2023
select LAST_DAY(TO_DATE('15.2.2024', 'DD.MM.YYYY'))
  from dual;
--> 29.2.2024

When...

Understanding the usage of the MONTHS_BETWEEN function

As the function’s name indicates, the MONTHS_BETWEEN function returns the number of months between two defined dates. The syntax of the MONTHS_BETWEEN function is stated in the following block:

MONTHS_BETWEEN(date_val1, date_val2)

Let’s look at the results. To get a positive value, the first parameter value (date_val1) must be greater than the second parameter (date_val2). If not, a negative result will be provided:

select MONTHS_BETWEEN(TO_DATE ('15.12.2022', 'DD.MM.YYYY'),
                      TO_DATE ('15.2.2022', 'DD.MM.YYYY'))
  from dual;
--> 10
select MONTHS_BETWEEN(TO_DATE ('15.2.2022', 'DD.MM.YYYY'),
                    ...

Exploring the NEXT_DAY and NEXT_DATE functions

It often happens that you need to find the next free day in your calendar, the date of the next Wednesday, or the date of the second Sunday of the month when system updates are to be performed. In this section, we will take a closer look at two functions. The first of them is directly available within the database system. It refers to the NEXT_DAY function. However, as we will show, its evaluation and processing depend on several factors, specifically NLS parameters. Therefore, in the second part, we will propose an own function implementation that is resistant to changes in terms of the format and local parameters.

Exploring the principles of the NEXT_DAY function

The output of the NEXT_DAY function is the first weekday greater than the defined input date (date_val):

NEXT_DAY(<date_val>, <weekday>)

The following table shows the mapping principles.

Investigating the TRUNC function

The TRUNC function, for dealing with DATE type values, removes all parts with smaller granularity than defined. It consists of one obligatory and one optional parameter. The first one is the DATE attribute value (date_val), while the second reflects the granularity (format). If the second parameter is not defined, the default value for the day is used (the time element value will be 00 in that case). Thus, smaller granularity values (day, hours, minutes, and seconds) are removed if the second parameter defines a month. The second parameter value can be DD, MM, YY, HH, or MI. The format unit (the second parameter) can also be Q (quarter), W (week of the month), or WW (week in the year). The syntax of the function is stated in the following code block:

TRUNC(<date_val>, [<format>])

Let’s assume this is the actual time to be set by this value:

The following code shows examples of the TRUNC function’...

Understanding the usage of the ROUND function

The ROUND function returns a DATE value rounded to a specific granularity. It takes two parameters. The first parameter (input_date) is mandatory and specifies the DATE value to be rounded. The rounded unit is defined by the second parameter (format). The second parameter is optional. The default value has day precision. Thus, if the value for the second parameter is not stated, the input date is rounded to the nearest day.

The syntax of the ROUND function is as follows:

ROUND (<input_date> [, <format>])

The following code snippets show examples of the ROUND function’s usage related to the DATE value’s management. The value is rounded down by the day precision specified. As it reflects the morning hours, the returned value is 26.01.2022 00:00:00:

select ROUND(TO_DATE('26.1.2022 10:15:22',
                 ...

Understanding the PERSONAL_ID concept and birthday management

PERSONAL_ID refers to a specific value assigned to people in Slovakia and the Czech Republic. It is a unique personal identification number formed of the person’s date of birth, gender, and a terminal number, which is a differentiating number for people born on the same day. In total, it consists of six digits followed by the slash symbol and another four digits.

The first two digits of PERSONAL_ID are the last two digits of the person’s year of birth. The second two digits express the numeric designation of the person’s month of birth (the value is increased by 50 for women). The third two-digit set represents the numerical designation of the person’s day of birth. This is an example of a man and woman:

--> 90 06 23 / 1234				90 56 23 / 1239
--> MAN						WOMAN
--> Year = 90					Year = 90
--> Month = 6					Month = 6
--> Day = 23					Day = 23

The whole personal identifier...

Generating random dates

When dealing with date management and testing, a crucial task is associated with generating dates. Based on my experience in business, it is inevitable to focus on relevance and correctness. Each date consists of day, month, and year elements. There is also time denotation, but it can be ignored in this phase.

I often encounter my students modeling individual elements separately, and then the resulting value is achieved by putting the elements together. However, how do you generate and process it? No problem – the year can be defined by the range to be generated. The same approach can be applied to month reflection. However, to ensure overall consistency, the day elements must be emphasized. Namely, what about the right border for day processing? Some months have 31 days. The rest have only 30 days. Moreover, there is a specific month (February) with a different number of days. It consists of either 28 or 29 days (if it’s a leap year). The processing...

Summary

In this chapter, you got familiar with the existing functionalities in DBS Oracle, mostly related to day and month granularity. We emphasized the ADD_MONTHS and MONTH_BETWEEN functions for dealing with month precision.

Mathematical arithmetic applies a day precision shift. Therefore, this chapter referenced the LAST_DAY function for providing the last day of the month and the NEXT_DAY function, which provides the closest larger DATE value according to the specified weekday. The weekday value is set by the string abbreviation; alternatively, the serial number of the day within the week is used. However, as we have shown, these values depend on the region, language, or territory. Therefore, our own implementation for getting the next day was proposed and discussed.

In addition, we learned the principles of DATE value rounding and truncating. Then, the PERSONAL_ID value was introduced, which can be used as the primary key for a person table, providing composite information...

Questions

Answer the following questions to test your knowledge of this chapter:

What is the output of the following statement?

select ADD_MONTHS(TO_DATE('31.1.2023',
                          'DD.MM.YYYY'),
                  1)
  from dual;

February 28, 2023
February 29, 2023
February 31, 2023
An exception is raised

Select the right statement:
1. The MONTHS_BETWEEN function can provide a negative value if the second parameter value is greater than the first
2. The MONTHS_BETWEEN function provides only an integer value, not the numerical representation, generally
3. If the output value of the MONTHS_BETWEEN function is negative, an exception is raised
4. The internal implementation of the MONTHS_BETWEEN function takes both values and swaps them...

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.

See other products by Michal Kvet