Home / SQL Statements / Question No: 224

Temporal values include dates, times, and datetime values that have both a date and time. More specialized temporal types are timestamp (commonly used for recording "current date and time") and year (for temporal values that require a resolution only to year units).

Direct use of temporal values in expressions occurs primarily in comparison operations, or in arithmetic operations that add an interval to or subtract an interval from a temporal value. Otherwise, most temporal value operations are performed by using functions.

The usual comparison operators apply to temporal values (=, <>, <, BETWEEN, and so forth).  To perform interval arithmetic, use the INTERVAL keyword and a unit value:

mysql> SELECT '2010-01-01' + INTERVAL 10 DAY, INTERVAL 10 DAY + '2010-01-01';
+--------------------------------+--------------------------------+
| '2010-01-01' + INTERVAL 10 DAY | INTERVAL 10 DAY + '2010-01-01' |
+--------------------------------+--------------------------------+
| 2010-01-11                     | 2010-01-11                     |
+--------------------------------+--------------------------------+

For addition of temporal and interval values, you can write the operands in either order, as just shown. To subtract an interval from a temporal value, the interval value must be second (it doesn't make sense to subtract a temporal value from an interval):

mysql> SELECT '2010-01-01' - INTERVAL 10 DAY;
+--------------------------------+
| '2010-01-01' - INTERVAL 10 DAY |
+--------------------------------+
| 2009-12-22                     |
+--------------------------------+

Intervals can be specified in units such as SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR.  Several functions take temporal arguments or return temporal values. Some types of operations these functions can perform are to extract parts of a value, convert a value to seconds or days, or reformat values.