Date and Time Standardization Principles

Currently, businesses focus on intelligent information systems supported by machine learning techniques, decision support systems, and prognosis management. Conventional principles are not suitable. The reference path over time needs to be covered. To cover the evolution over time, each state needs to be extended by the validity frames, represented by the date and time values, and even extended with multiple time zones and synchronization across regions. Unfortunately, there were no common standards and rules applicable in the first phases of development. Therefore, individual database system vendors decided to solve the problem in their own ways without creating a complex solution that would be generally applicable. Nevertheless, although individual systems do not apply the rules of international standard (ISO) standardization that were accepted later, we think it is very important to get a comprehensive overview of standardization, focusing...

Understanding relational database integrity

Relational databases were introduced in the 1960s, formed by the entities and relationships between them and influenced by relational algebra. The transaction support covered security, consistency, and integrity, ensuring atomicity, consistency, isolation, and durability.

In the field of databases, the concept of integrity is understood in terms of accuracy and correctness by ensuring data consistency. Moreover, it is often associated with data confidentiality. The integrity problem is, therefore, associated with ensuring any changes made to the database are correct. Errors and consequent data inconsistencies can be caused by data entry, operator errors, program errors, or even intentional database corruption attempts.

Integrity can be divided into the following categories:

• Domain integrity: This represents a set of rules shared by all column attributes assigned to a particular domain. In simpler terms, it refers to the data...

The ISO 8601 standard

ISO 8601 covers date and time-related data, including its exchange, transfer, and representation. It was first published in 1988, followed by multiple updates in 1991, 2000, 2004, and 2019. It aims to provide a system for the management of date and time data and the representation of individual elements to avoid numerical dates and times being misinterpreted, focusing on data transfer across countries and regions and emphasizing time zones and conventions that are specific to certain regions.

ISO 8601 divides the management into separate date and time models. The Date value is based on the Gregorian calendar (an international standard for civil use), while the 24-hour timekeeping system expresses the Time. It uses the principle that the greatest temporal granularity element should be the left-most one. Each following lesser element comes to the right of the previous one. For example, the year must precede the month, the month definition must precede the day...

Date value definition

Based on the standards approved by the American National Standards Institute (ANSI), values for the date are typically defined by the year, month, and day elements, ranging from 0001-01-01 through 9999-12-31, delimited by a specific symbol, such as a dot, comma, semicolon, or slash. Thus, we know that the value 2021-05-01 represents 1st May, not 5th January. The format for the Date value representation is YYYY-MM-DD, expressing the year (YYYY), month (MM), and day (DD) elements. To ensure correctness across the centuries, this standard requires a 4-digit year format to be applied precisely.

The available year definition ranges from 0000 to 9999, with 0000 being equal to 1 BCE (Before Common Era) and all subsequent values referring to CE (Common Era). ISO 8601 permits value management lower than 0000 if accepted by the sender and recipient, in which case, an expanded year definition with the + or – sign is present. Thus, +0000 represents 1 BCE while ...

Time element modeling and management

Generally, representations of the date cover the year, month, and day elements. Some of them can be omitted, or a transformation can be made to express the relevant value. This standard proposes a definition for time elements as well. The principles and model properties are similar. It uses a 24-hour clock by pointing to the hour (HH), minute (MI), and second (SS) elements. The time value is prefixed with T. The basic format does not use padding; for example, T152217. Instead, each element is composed of 2 digits. The extended format is more user-friendly, splitting individual parts into categories – for example, T15:22:17. In both models, the character T expresses the time representation but can be omitted for extended representation by applying a colon delimiter.

The hour value ranges from 00 to 24. The minute value ranges from 00 to 59, and finally, for seconds, the available range is from 00 to 60 (due to the leap second discussed...

Composite date and time value

In the preceding sections, date and time values were treated separately. Some database systems, such as MySQL, use this approach by defining two data types—Date and Time data types. A DateTime data type is a combination of both values in a common block. The Date values are placed in the left-most part up to the second fractions. The predefined format is YYYY-MM-DDTHH:MI:SS[.FF]. Second fractions are optional. The T symbol is used to split the date and time values. Version 2019 allows you to omit the delimiter symbol, T. So, all of the following values would be the same: 2022-12-30T15:26:01, 2022-12-3015:26:01, 2022-12-30T15:26:01.00, and 2022-12-3015:26:01.00.

Each day starts and ends at midnight, covered by the values 00:00 and 24:00 to distinguish the timeline positions (start of the day or end of the day). However, the value 24:00 is the same as 00:00 for the consecutive day. Thus, both of the following values express the same point in time...

Periods of time

The last section described date or time point value representation principles. Periods of time can be modeled by two time points, expressing the beginning and end points of the validity, represented in different ways, delimiting whether the time point is exclusive or inclusive. The second principle is associated with just one attribute, commonly pointing to the beginning point, followed by the time interval duration (period). There are several designations to represent particular values. The general format for the period is P[n]Y[n]M[n]DT[n]H[n]M[n]S, where [n] is replaced by the numerical value of the physical element. P, Y, M, D, T, H, M, and S are the delimiters for individual element reflection, signifying the following:

• P expresses the period:
  • Y: Number of years
  • M: Number of months
  • D: Number of days
• T refers to the time components:
  • H: The hour value
  • M: The minute value
  • S: The second value

As stated, a common representation of periods is formed by the...

Summary

In this chapter, you learned the fundamental facts related to standardization. It is always good to understand these to start.

ISO 8601 was approved in 1988, and due to various consecutive enhancements, it provides a relevant summary of date and time management in information technology.

First, you learned about the Date value, formed by the day, month, and year elements, followed by the Time representation. After that, you learned how these values can be grouped into one specific DateTime reference, expressing one point in time. Afterward, solutions for modeling periods of time were proposed, focusing on durations and validity intervals. Until version 2019, there was no ability to model an unlimited validity interval. This was left to the user to manage and implement.

By accepting the roots defined by ISO 8601, the user gets an overview of the definition, management, and representation complexity, and is also aware of the limitations.

Although the Oracle database...

Questions

1. Which of the following is the correct ISO format for specifying the date?
   1. YYYY-MM-DD
   2. MM-DD-YYYY
   3. DD-MM-YYYY
   4. YYYY-MM-DD HH:MI:SS
2. Which character prefixes Time elements?
   1. T
   2. D
   3. C
   4. A space
3. Which calendar is primarily used for the ISO standardization reference?
   1. Roman calendar
   2. Julian calendar
   3. Gregorian calendar
   4. ISO calendar
4. Which of the following values references unlimited interval periodicity?
   1. R5/2022-02-01T15:00:00/P1M
   2. R0/2022-02-01T15:00:00/P1M
   3. R/2022-02-01T15:00:00/P1M
   4. R-1/2022-02-01T15:00:00/P1M

Further reading

A full description of the ISO 8601 standard is available at https://www.iso.org/obp/ui/#iso:std:iso:8601:-1:ed-1:v1:en.