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 Precisions for the INTERVAL data type. shows.

Table 1. Precisions for the INTERVAL data type.

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