Chapter 41 - Temporal Data

Abstract

This chapter focuses on the temporal data types used in Standard SQL. Standard SQL has rules for converting the numeric and character strings into these data types and there is a schema information table for global time zone information to make sure that these data types are synchronized. As an international standard, Standard SQL has to handle time for the whole world. All SQL implementations have a DATE data type, whereas most have a separate TIME and a TIMESTAMP data type. These values are drawn from the system clock and are therefore local to the host machine. They are based on what is now called the Common Era calendar, which many people would still call the Gregorian or Christian calendar. Standard SQL has a set of date and time and INTERVAL data types. Both of these groups are temporal data types, but datetimes represent points in the time line, while the interval data types are durations of time. Standard SQL also has a full set of operators for these data types. There are two ways of representing temporal data internally. The “Unix representation” is based on keeping a single long integer or a word of 64 or more bits that counts the computer clock ticks from a base starting date and time. The other representation is called the “COBOL method,” which uses separate fields for the year, month, day, hours, minutes, and seconds.