In the previous lessons, you saw how various operators work with numeric and string values. Although you can use the comparison operators with date values in the same way as numbers and strings, you must use a special syntax to perform arithmetic on date values. Representing Dates and TimesA date value in MySQL takes the format YYYY-MM-DD or YYYYMMDD. Times take the format HH:MM:SS or HHMMSS. Until stored to the database or passed as an argument to a date function, however, this value is simply a string representation of a given date.
This format places the most significant value first and the least significant last. Consequently, comparisons between dates take place in a logical way. In the following example, MySQL is simply comparing two strings or two numbers: mysql> SELECT '2005-31-12' < '2006-01-01', 20053112 < 20060101; +--------------------------------+------------------------+ | '2005-31-12' < '2006-01-01' | 20053112 < 20060101 | +--------------------------------+------------------------+ | 1 | 1 | +--------------------------------+------------------------+ However, using the addition and subtraction operators on a date represented in a string can cause unpredictable results. Remember from the previous lesson that MySQL tries to perform numeric operations on a string by taking only the first numbers that appear. mysql> SELECT '2005-06-01' + 10; +-------------------+ | '2005-06-01' + 10 | +-------------------+ | 2015 | +-------------------+ 1 row in set (0.00 sec) Even using the numeric format with no hyphens, the results are far from perfect. In the following example, adding 7 days to March 30 should return April 6. Instead, the result looks like an invalid date in March. mysql> SELECT 20050330 + 7; +--------------+ | 20050330 + 7 | +--------------+ | 20050337 | +--------------+ 1 row in set (0.00 sec) Date and Time DataEven when values are stored to a database column that is declared as one of the date and time data types that you will learn about in Lesson 14, "Creating and Modifying Tables," you cannot perform direct arithmetic on those values. In the sample database, the order_date column in the orders table is a date type column. The following example shows that arithmetic on those date values can still produce dates that do not exist.
mysql> SELECT customer_code, order_date, order_date + 10 -> FROM orders -> WHERE customer_code = 'SCICORP'; +---------------+------------+-----------------+ | customer_code | order_date | order_date + 10 | +---------------+------------+-----------------+ | SCICORP | 2006-01-23 | 20060133 | | SCICORP | 2006-02-02 | 20060212 | | SCICORP | 2006-02-05 | 20060215 | +---------------+------------+-----------------+ 3 rows in set (0.00 sec) Date ArithmeticTo instruct MySQL to perform date arithmetic using the addition and subtraction operators, you must use the INTERVAL keyword along with a unit of time. The following example adds 7 days to December 30, 2005, using date arithmetic: mysql> SELECT '2005-12-30' + INTERVAL 7 DAY; +-------------------------------+ | '2005-12-30' + INTERVAL 7 DAY | +-------------------------------+ | 2006-01-06 | +-------------------------------+ 1 row in set (0.00 sec) As you can see in this example, the value returned is the correct date after adding 7 days to December 29, 2005the year, month, and date values have all been affected. Changing the INTERVAL value provides an easy way to add different units of time to a date value. For instance, without needing to know how many days are in a given month, you can add 1 month to the date as follows: mysql> SELECT '2005-12-29' + INTERVAL 1 MONTH; +---------------------------------+ | '2005-12-29' + INTERVAL 1 MONTH | +---------------------------------+ | 2006-01-29 | +---------------------------------+ 1 row in set (0.00 sec) Time intervals can also be used. The following example shows that when the time is incremented past midnight, the day value is also incremented: mysql> SELECT '2005-12-31 22:30' + INTERVAL 2 HOUR; +--------------------------------------+ | '2005-12-31 22:30' + INTERVAL 2 HOUR | +--------------------------------------+ | 2006-01-01 00:30:00 | +--------------------------------------+ 1 row in set (0.00 sec)
Table 8.1 lists the unit values that can be used with the INTERVAL keyword.
There are a number of compound INTERVAL unit keywords, made up of two of the keywords from Table 8.1 separated by an underscore. The following example adds 2 hours and 30 minutes to a date using the HOUR_MINUTE unit: mysql> SELECT '2005-12-31 22:30' + INTERVAL '2 30' HOUR_MINUTE; +--------------------------------------------------+ | '2005-12-31 22:30' + INTERVAL '2 30' HOUR_MINUTE | +--------------------------------------------------+ | 2006-01-01 01:00:00 | +--------------------------------------------------+ 1 row in set (0.00 sec) Note that when using a compound unit, the two unit values must be enclosed in single quotes and separated using a space character. |