Time Data Types

DATE and DATETIME data values represent zero-dimensional points in time; INTERVAL data values represent 1-dimensional spans of time, with positive or negative values. DATE precision is always an integer count of days, but various field qualifiers can define the DATETIME and INTERVAL precision. You can use DATE, DATETIME, and INTERVAL data in arithmetic and relational expressions. You can manipulate a DATETIME value with another DATETIME value, an INTERVAL value, the current time (specified by the keyword CURRENT), or some unit of time (using the keyword UNITS).

You can use a DATE value in most contexts where a DATETIME value is valid, and vice versa. You also can use an INTERVAL operand in arithmetic operations where a DATETIME value is valid. In addition, you can add two INTERVAL values and multiply or divide an INTERVAL value by a number.

An INTERVAL column can hold a value that represents the difference between two DATETIME values or the difference between (or sum of) two INTERVAL values. In either case, the result is a span of time, which is an INTERVAL value. Conversely, if you add or subtract an INTERVAL from a DATETIME value, another DATETIME value is produced, because the result is a specific time.

Arithmetic Operations on DATE, DATETIME, and INTERVAL Values lists the binary arithmetic operations that you can perform on DATE, DATETIME, and INTERVAL operands, and the data type that is returned by the arithmetic expression.
Table 1. Arithmetic Operations on DATE, DATETIME, and INTERVAL Values
Operand 1 Operator Operand 2 Result
DATE - DATETIME INTERVAL
DATETIME - DATE INTERVAL
DATE + or - INTERVAL DATETIME
DATETIME - DATETIME INTERVAL
DATETIME + or - INTERVAL DATETIME
INTERVAL + DATETIME DATETIME
INTERVAL + or - INTERVAL INTERVAL
DATETIME - CURRENT INTERVAL
CURRENT - DATETIME INTERVAL
INTERVAL + CURRENT DATETIME
CURRENT + or - INTERVAL DATETIME
DATETIME + or - UNITS DATETIME
INTERVAL + or - UNITS INTERVAL
INTERVAL * or / NUMBER INTERVAL

No other combinations are allowed. You cannot add two DATETIME values because this operation does not produce either a specific time or a span of time. For example, you cannot add December 25 and January 1, but you can subtract one from the other to find the time span between them.