Temporal Expressions

Temporal values include dates, times, and date/time 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.




Temporal data may be generated via any of the following means:


􀂃 Copying data from an existing date, datetime, or time column


􀂃 Executing a built-in function that returns a date, datetime, or time column


􀂃 Building a string representation of the temporal data to be evaluated by the server


Date Components:


Type                       Default Format
DATE                        YYYY-MM-DD
TIME                          HH:MM:SS
DATETIME               YYYY-MM-DD HH:MI:SS
TIMESTAMP            YYYY-MM-DD HH:MI:SS
YEAR                         YYYY


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, 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                                       |
+-----------------------------------------------+