Modeling and Storage Principles

In the past, conventional database principles were used, meaning only currently valid states were in operation and manipulated with no specification of their period of validity. Therefore, date and time values were significantly limited. Nowadays, the situation is different. One aspect of current information systems is state monitoring over time, and another is the length of time that the state is valid. For example, an employment contract specifies the time frame that it is valid, within which the employee is expected to be available and attend to their duties, as well as noting their activities, responsibilities, and so on. If the employee leaves the company, they should automatically lose access to the information systems or data storage repositories. However, data about the employee cannot be removed as many references to it exist. The history of the assignments and responsibilities must be stored. Another example is webshop registration. Even if...

Exploring data types

ISO 8601 proposes three characteristics related to date and time models:

• The DATE data type is used to deal with the granularities of the year, month, and day
• The TIME data type deals with the hour, minute, and second elements
• By combining those two types, the DATETIME data type is introduced

Typically, the precision is the second element itself, which can also be expanded to cover fractions of a second. This principle is used, for example, in the MySQL database system. Oracle Database has not adopted the preceding three types. It uses the DATE data type, which deals with the date and time elements up to second granularity. Thus, it is impossible to deal with the year, month, and day elements without the time elements. However, to avoid using the time elements, some time elements have the option to hold a 0 value. However, all those elements are always present, stored, and referenceable. As a result, if the solution does not require time...

Exploring constructor functions

How do you construct a DATE value? There are always two streams that can be used. The first type relates to the already described ANSI standard being adopted. The second stream uses explicit field mapping forming constructor functions. To ensure the processing is correct irrespective of the system and session format configuration, it is strongly recommended to always cover the mapping, that is, ensuring there is an unambiguous expression of the meaning of individual elements during the value construction. The DATE value cannot be treated as a string and individual values must be assigned to the elements, ensuring the correct meaning, representation, and management (mapping to the individual elements). For example, the value 12 can express the day, the month, or any time element (hour, minute, or second). Thus, without knowing the meaning of the values, two issues can be present. The first is that an exception will be raised if it is improperly specified...

Storing the date as an INTEGER value

In some cases, I have seen date modeling implemented as an integer in business applications. It is mostly the result of formal date management in Oracle RDBMS, where time elements do not need to be considered. It is assumed that the storage requirements can be lowered by using appropriate numeric value sizing. Moreover, if function-based indexes are applied to numeric date modeling, then the performance of the element extraction can be ensured.

On the other hand, there can be significant issues related to the processing. Firstly, the representation strongly depends on the positional element models. Typically, when represented by the DATE value, separators (mappers) are present, dividing individual elements. However, let’s look at a simple numerical value passing the format DDMMYYYY. The previously stated separator management principle cannot be used, resulting in a possible loss of reliability. Let’s consider the example of 1st...

Getting the actual date and time values

In the previous sections, we dealt with the date and time value composition from the character string format, providing examples of mappings to ensure the reliability and correctness of the processing. There are, however, two functions for each data type (DATE and TIMESTAMP) producing the actual date and time values.

We will intentionally not use the keyword current in this section, as current is a function name used in database processing. It reflects the client site by emphasizing region and time zone processing. The term actual is not used as a keyword by the client or server site and represents states or date and time values that are in effect at this moment.

The sysdate and current_date functions can be used when dealing with the DATE data type. sysdate is a SQL function that gets the actual date and time elements referencing the operating system of the database server. Thus particular values are normalized based on the server parameters...

Getting to know DATE arithmetic

Adding or subtracting a numerical value from the DATE value expresses the number of days (or a part of a day, for example, 0.5 expressing 12 hours):

select TO_DATE ('15.02.2022', 'DD.MM.YYYY') + 1 from dual;
--> 16.02.2022 00:00:00

Just to remind you, the TO_DATE conversion function in the preceding example deals only with the day, month, and year elements. In that case, the conversion causes undefined components (hours, minutes, and seconds) to be replaced with zero values.

Addition or subtraction automatically reflects the value consistency. Thus, if we’re looking at the last day of the month, adding 1 day produces the first day of the consecutive month, as expressed in the following code:

select TO_DATE ('28.02.2022', 'DD.MM.YYYY') + 1 from dual;
--> 1.3.2022 00:00:00
select TO_DATE ('31.12.2022', 'DD.MM.YYYY') + 1 from dual;
--> 1.1.2023 00:00:00

The decimal...

Understanding the INTERVAL data type

INTERVAL data types are available to model the duration. There are two types based on granularity precision: dealing with the year and month precision (the first type) or digging deeper into time elements (the second type).

INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH consists of no more than two elements describing the year and month delimited by a dash (-). After the definition, the format is specified, with the optional specification of precision. Figure 5.4 shows the syntax in graphical form. The first format element is mandatory, and the second is optional. Note that the default precision is 2, if not specified explicitly.

Figure 5.4 – INTERVAL YEAR TO MONTH

Table 5.2 shows examples of INTERVAL YEAR TO MONTH usage. The definition can be used independently or associated with the DATE or TIMESTAMP value, typically expressing the beginning point of the validity. The general definition consists of year...

TIMESTAMP arithmetic

The TIMESTAMP data type and related arithmetic work differently from how it may seem at first glance. Let’s take the TIMESTAMP value and add 1 day, represented by adding the value 1 to the particular value. However, what about the output? Will it even work? The following statement provides you with the answer:

select localtimestamp, localtimestamp + 1 from dual;

In addition, the following table shows the results:

LOCALTIMESTAMP	LOCALTIMESTAMP + 1
13.06.22 07:08:49,847000000	14.06.2022 07:08:49

Table 5.4 – Timestamp arithmetic

Yes, it took the localtimestamp value and added 1 day. The result is, however, a bit strange, isn’t it? Namely, the second fractions are lost. What has happened? Well, particular addition and subtraction cannot be applied to the TIMESTAMP value. To prevent...

Summary

In this chapter, you learned about the date and time data types supported by RDBMS Oracle, focusing on the differences between the ANSI standard and practical usage. Unlike other database systems, such as MySQL, Oracle does not support time management in separate data types. Additionally, year, month, and day elements cannot be treated without the time elements (hour, minute and second). DATE and TIMESTAMP values can be created by the ANSI constructors focusing on the specific format. It takes the finer element granularities in the right part of the definition. Thus, the year element is preceded by the month, which is preceded by the day reference. Another solution is defined by the explicit constructor function from the string, referenced by the TO_DATE and TO_TIMESTAMP functions.

To ensure the reliability and correctness of the results, a discussion related to implicit conversions was presented. Storing date or time elements as a string or integer does not provide sufficient...

Questions

1. What is the default precision for TIMESTAMP second fractions?
   1. 0 decimal places
   2. 3 decimal places
   3. 6 decimal places
   4. 9 decimal places
2. What is the finest precision for the DATE value definition?
   1. Day
   2. Second
   3. Microsecond
   4. Nanosecond
3. Which data type can hold the value 2022-06-13 6:46:12.576000 GMT?
   1. DATE
   2. TIMESTAMP
   3. TIMESTAMP WITH TIME ZONE
   4. TIMESTAMP WITH LOCAL TIME ZONE
4. What is the right syntax for the ANSI TIMESTAMP constructor?
   1. TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'
   2. DATE 'YYYY-MM-DD HH24:MI:SS.FF'
   3. TIMESTAMP 'YYYY-MM-DD'
   4. TIMESTAMP 'HH24:MI:SS.FF YYYY-MM-DD'
5. Which function produces a local client TIMESTAMP value?
   1. sysdate
   2. systimestamp
   3. current_date
   4. localtimestamp
6. What is the output of the subtraction of two DATE values?
   1. Two DATE values cannot be subtracted
   2. Number of seconds elapsed
   3. Number of hours elapsed
   4. Number of days elapsed
7. What is the data type of the output of the subtraction of two TIMESTAMP values?
   1. INTERVAL DAY TO SECOND
   2. INTERVAL YEAR TO...

Further reading

• Managing Time in Relational Databases: How to Design, Update and Query Temporal Data by Tom Johnston and Randall Weis. It provides a complex practical guide to the data modeling and query management of date and time-delimited tuples.
• Time and Relational Theory: Temporal Databases in the Relational Model and SQL by C. J. Date, Hugh Darwen, and Nikos A. Lorentzos. This book discusses the SQL:2011 standard focusing on temporal modeling and temporal management in the SQL language. It also focuses on temporal references and proper state coverage.

The rest of the chapter is locked

Summary

In this chapter, you learned about the date and time data types supported by RDBMS Oracle, focusing on the differences between the ANSI standard and practical usage. Unlike other database systems, such as MySQL, Oracle does not support time management in separate data types. Additionally, year, month, and day elements cannot be treated without the time elements (hour, minute and second). DATE and TIMESTAMP values can be created by the ANSI constructors focusing on the specific format. It takes the finer element granularities in the right part of the definition. Thus, the year element is preceded by the month, which is preceded by the day reference. Another solution is defined by the explicit constructor function from the string, referenced by the TO_DATE and TO_TIMESTAMP functions.

To ensure the reliability and correctness of the results, a discussion related to implicit conversions was presented. Storing date or time elements as a string or integer does not provide sufficient...

Questions

1. What is the default precision for TIMESTAMP second fractions?
   1. 0 decimal places
   2. 3 decimal places
   3. 6 decimal places
   4. 9 decimal places
2. What is the finest precision for the DATE value definition?
   1. Day
   2. Second
   3. Microsecond
   4. Nanosecond
3. Which data type can hold the value 2022-06-13 6:46:12.576000 GMT?
   1. DATE
   2. TIMESTAMP
   3. TIMESTAMP WITH TIME ZONE
   4. TIMESTAMP WITH LOCAL TIME ZONE
4. What is the right syntax for the ANSI TIMESTAMP constructor?
   1. TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'
   2. DATE 'YYYY-MM-DD HH24:MI:SS.FF'
   3. TIMESTAMP 'YYYY-MM-DD'
   4. TIMESTAMP 'HH24:MI:SS.FF YYYY-MM-DD'
5. Which function produces a local client TIMESTAMP value?
   1. sysdate
   2. systimestamp
   3. current_date
   4. localtimestamp
6. What is the output of the subtraction of two DATE values?
   1. Two DATE values cannot be subtracted
   2. Number of seconds elapsed
   3. Number of hours elapsed
   4. Number of days elapsed
7. What is the data type of the output of the subtraction of two TIMESTAMP values?
   1. INTERVAL DAY TO SECOND
   2. INTERVAL YEAR TO...

Further reading

• Managing Time in Relational Databases: How to Design, Update and Query Temporal Data by Tom Johnston and Randall Weis. It provides a complex practical guide to the data modeling and query management of date and time-delimited tuples.
• Time and Relational Theory: Temporal Databases in the Relational Model and SQL by C. J. Date, Hugh Darwen, and Nikos A. Lorentzos. This book discusses the SQL:2011 standard focusing on temporal modeling and temporal management in the SQL language. It also focuses on temporal references and proper state coverage.