SQL Performance Tuning [Electronic resources] نسخه متنی

Temporals

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.

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.

Notes on Table 7-4:

DATE Min column

Shows the minimum date allowed by the DBMS in DATE, TIMESTAMP, or equivalent columns.

MySQL's TIMESTAMP has a minimum date portion of '1970-01-01'. MySQL's DATETIME has a minimum date portion of '0001-01-01'.

DATE Max column

Shows the maximum date allowed by the DBMS in DATE, TIMESTAMP, or equivalent columns.

MySQL's TIMESTAMP has a maximum date portion of '2037-12-31'. MySQL's DATETIME has a maximum date portion of '9999-12-31'.

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.

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.