Other Improvements in MySQL 5.0 - MySQL

There are some additional new features in MySQL 5.0 that you may find useful. These are detailed in the next few subsections.

New SELECT INTO Syntax

We’ve already seen how this can be used with cursors in a stored procedure. You can also use it to assign values to user variables in a regular SELECT query, e.g., SELECT name INTO @prodname FROM products WHERE prodid = 327; . Note that the SELECT must return a single row for this to work. We show an example here:

New Timestamp Functions

The TIMESTAMPADD() and TIMESTAMPDIFF() functions are introduced in MySQL 5.0.0. Here are the definitions for each:

TIMESTAMPADD(interval, expr, datetime) TIMESTAMPDIFF(interval, datetime1, datetime2)

TIMESTAMPADD() adds expr interval intervals to datetime and returns the result, where expr is an integer value. TIMESTAMPDIFF() returns the integer difference between datetime1 and datetime2 in terms of the unit supplied as interval .

In both cases, the interval argument may be any one of the values FRAC_SECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR , and any of these may optionally be prefixed with SQL_TSI_ . The datetime expressions may be any valid DATE or DATETIME values.

A few examples, as shown here, may help to make clear how these functions are used:

Notice that negative values are possible both for the value of expr input to the TIMESTAMPADD() function as well as for the result returned by TIMESTAMPDIFF() .