Date Operators


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 Times

A 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.

Date and Time

You can have both date and time components in a single value, represented in the format YYYY-MM-DD HH:MM:SS.


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 Data

Even 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.

Date Columns

There is actually no date logic at the database level. The only restriction is that the value must be in one of the recognized formats. Therefore, it is entirely possible for a date such as February 30 to be stored in a MySQL date column.


 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 Arithmetic

To 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) 


Intervals

In these examples, the intervals used were 7 DAY and 2 HOUR rather than 7 DAYS and 2 HOURS. MySQL requires the unit keyword to be singular, regardless of English grammar rules.


Table 8.1 lists the unit values that can be used with the INTERVAL keyword.

Table 8.1. INTERVAL Unit Keywords for Date Arithmetic

Keyword

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR


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.




Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net