| The SQL Standard provides four data types for columns that contain temporal data: DATE, TIME, TIMESTAMP, and INTERVAL. Table 7-3 shows the SQL Standard requirements and the level of support (data type and storage size in bytes) the Big Eight have for these data types, while Table 7-4 shows the minimum and maximum temporal values/value sizes allowed for each data type. Table 7-3. ANSI/DBMS Support for Temporal Data Types | | DATE | TIME | TIMESTAMP | WITH TIME ZONE | INTERVAL | DATETIME | | ANSI SQL | Yes | Yes | Yes | Yes | Yes | No | | IBM | 4 | 3 | 10 | No | No | No | | Informix | 4 | No | No | No | 11 | 11 | | Ingres | 12 | No | No | No | No | No | | InterBase | 8 | 8 | 8 | No | No | No | | Microsoft | No | No | No | No | No | 8 | | MySQL | 3 | 3 | 4 | No | No | 8 | | Oracle | 7 | No | 11 | 13 | 11 | No | | Sybase | No | No | No | No | No | 8 | Notes on Table 7-3: -
Informix calls the TIMESTAMP data type DATETIME and requires precision qualifiers to be added to it, as with INTERVAL. The DATETIME data type takes between 5 and 11 bytes of storage, depending on the qualifier. Informix supports both SQL Standard INTERVAL options. INTERVAL YEAR TO MONTH uses 4 bytes of storage, while INTERVAL DAY TO SECOND requires up to 11 bytes. -
Ingres calls the TIMESTAMP data type DATE. -
Microsoft and Sybase call the TIMESTAMP data type DATETIME. -
Oracle's DATE and TIMESTAMP data types both include a TIME. The TIMESTAMP data type size ranges from 7 to 11 bytes, depending on the precision. If WITH TIME ZONE is added, TIMESTAMP requires 13 bytes of storage. Oracle supports both SQL Standard INTERVAL options. INTERVAL YEAR TO MONTH uses 5 bytes of storage, while INTERVAL DAY TO SECOND requires 11 bytes. Table 7-4. ANSI/DBMS Temporal Ranges | | DATE Min | DATE Max | TIME Precision Default | TIMESTAMP Precision Default | Fractional Seconds Specifiable | | ANSI SQL | 0001-01-01 | 9999-12-31 | N/S | N/S | Yes | | IBM | 0001-01-01 | 9999-12-31 | Second | Microsecond | No | | Informix | 0001-01-01 | 9999-12-31 | Millisecond | Millisecond | Yes | | Ingres | 0001-01-01 | 9999-12-31 | Second | Second | No | | InterBase | 0001-01-01 | 9999-12-31 | Second | Second | No | | Microsoft | 1753-01-01 | 9999-12-31 | Millisecond | Millisecond | No | | MySQL | 0001-01-01 | 9999-12-31 | Second | Second | No | | Oracle | 01-JAN-4712 BC | 31-DEC-4712 AD | Microsecond | Microsecond | Yes | | Sybase | 1753-01-01 | 9999-12-31 | Millisecond | Millisecond | No | Notes on Table 7-4: -
DATE Min column Shows the minimum date allowed by the DBMS in DATE, TIMESTAMP, or equivalent columns. -
DATE Max column Shows the maximum date allowed by the DBMS in DATE, TIMESTAMP, or equivalent columns. -
TIME Precision Default column Shows the default fractional seconds precision supported by the DBMS in TIME, TIMESTAMP, or equivalent columns. This column is "second" if the DBMS doesn't accept a time with a fractional seconds portion, such as TIME '15:30:10.25' . -
TIMESTAMP Precision Default column Shows the default fractional seconds precision supported by the DBMS in TIMESTAMP or equivalent columns. This column is "second" if the DBMS doesn't accept a time with a fractional seconds portion, such as TIMESTAMP '2002-02-10 15:30:10.25' . -
Fractional Seconds Specifiable column This column is "Yes" if the DBMS supports a fractional seconds precision definition for TIME, TIMESTAMP, or equivalent columns, as in: CREATE TABLE Table1 ( column1 TIME(2) ...) which ensures that every column1 value will be a time with a fractional seconds precision of two digitsfor example, TIME '15:30:10.25' . The main SQL Standard data types for temporal information are DATE, TIME, and TIMESTAMP. There is also an INTERVAL data type, supported only by Informix and Oracle; a TIME WITH TIME ZONE data type, supported only by PostgreSQL, and the PostgreSQL makers say you shouldn't use it; and a TIMESTAMP WITH TIME ZONE data type, supported only by Oracle, although IBM has a CURRENT TIMEZONE niladic function: subtracting CURRENT TIMEZONE from a local time converts the local time to Universal Coordinated Time (UTC). So there isn't much diversity here, except for precision and the curious business of the minimum date value. Minimum Date Value Looking at Table 7-4, you'll see that the minimum date value varies a lot. The explanations for the various minimum year figures chosen are: -
"0001" (1 AD) is the first allowable year according to the SQL Standard. -
"1582" is the year the Gregorian calendar was first used in some Catholic countries . -
"1753" is the year the Gregorian calendar became official in England and America. -
"4713 BC" is the year from which we count Julian days. The start date of the Julian period was calculated by Julius Scaliger, who proposed that days should be counted in decimal, without regard to months or years . Astronomers adopted this system and took noon GMT-4712-01-01 Julian (that is, January 1, 4713 BC) as their zero point. (Note that 4713 BC is the year 4712 according to the astronomical year numbering.) In fact, the differences are unimportant, because the DBMSs that support dates such as '0001-01-01' are merely projecting the Gregorian calendar backward in time as if it was in force in 1 AD. This assumption, which is called prolepticism, is falseso calculations that use old dates are specious. | DATE, TIME, and TIMESTAMP are all fixed-size fields so the size factor doesn't really come into play. The only significant decision you need to make for temporal data is whether you should use a combined date plus time data type, such as TIMESTAMPeven if you only need the time, or only need the date. Interestingly, if you use TIMESTAMP instead of DATE or TIME, you often aren't using any additional space because many DBMSs use a combined column internally anyway, as shown in Table 7-3. In addition to the size factor, there are two other main advantages to using TIMESTAMP for temporal data: -
Comparisons are easier to write when everything has the same data type. -
There won't be any erroneous "date minus date" calculations by people who forget to take the time into account. One can always reformat later. (By the way, the same advice applies for any situation where there is a major and a minor component. For example, it's better to have a single "height" column containing 64 instead of a "feet" column containing 5 and an "inches" column containing 4 .) The Bottom Line: Temporals If you use TIMESTAMP instead of DATE or TIME, you often aren't using any additional space because the DBMS uses a combined column for storage, internally. On the other hand, TIMESTAMPs sometimes take more space to store, partly because the default precision of a TIMESTAMP column includes a fractional seconds portion; information that is rarely necessary. Searches for the date component of a TIMESTAMP will be slow. The optimizer won't see that particular date values occur frequently because the time is stored along with it. Dates and times are just different magnitudes of the same propertyand we don't use two different columns to store the "integer" and "decimal" parts of a number, do we? Temporal comparisons and transfers are easier if there is only one data type. Furthermore, a TIME doesn't really mean anything unless the date is known as well. TIMESTAMP (or its non-standard SQL-extension equivalent, DATETIME) is supported by more DBMSs than the other temporal data types. Recommendation: Prefer TIMESTAMP for temporal data. |
