Durations using INTERVAL
The INTERVAL data type stores a duration, that is, a length
of time. The difference between two DATETIME values is an INTERVAL,
which represents the span of time that separates them. The following
examples might help to clarify the differences:
- An employee began working on January 21, 1997 (either a DATE or a DATETIME).
- She has worked for 254 days (an INTERVAL value, the difference between the TODAY function and the starting DATE or DATETIME value).
- She begins work each day at 0900 hours (a DATETIME value).
- She works 8 hours (an INTERVAL value) with 45 minutes for lunch (another INTERVAL value).
- Her quitting time is 1745 hours (the sum of the DATETIME when she begins work and the two INTERVALs).
Like DATETIME, INTERVAL is a family of data types with
different precisions. An INTERVAL value can represent a count of years
and months, or it can represent a count of days, hours, minutes, seconds,
or fractions of seconds; 18 precisions are possible. The size of an
INTERVAL value ranges from 2 to 12 bytes, depending on the formulas
that Table 1 shows.
| Precision | Size (Round a fractional size to the next full byte) | Precision | Size (Round a fractional size to the next full byte) |
|---|---|---|---|
| year(p) to year | 1 + p/2 | hour(p) to minute | 2 + p/2 |
| year(p) to month | 2 + p/2 | hour(p) to second | 3 + p/2 |
| month(p) to month | 1 + p/2 | hour(p) to fraction(f) | 4 + (p + f)/2 |
| day(p) to day | 1 + p/2 | minute(p) to minute | 1 + p/2 |
| day(p) to hour | 2 + p/2 | minute(p) to second | 2 + p/2 |
| day(p) to minute | 3 + p/2 | minute(p) to fraction(f) | 3 + (p + f)/2 |
| day(p) to second | 4 + p/2 | second(p) to second | 1 + p/2 |
| day(p) to fraction(f) | 5 + (p + f)/2 | second(p) to fraction(f) | 2 + (p + f)/2 |
| hour(p) to hour | 1 + p/2 | fraction to fraction(f) | 1 + f/2 |
INTERVAL values can be negative or positive. You can add or subtract them, and you can scale them by multiplying or dividing by a number. This is not true of either DATE or DATETIME. You can reasonably ask, “What is one-half the number of days until April 23?” but not, “What is one-half of April 23?”