Date and Time Functions

Here is an example that uses date functions. The following query selects all rows with a date_col value from within the last 30 days:

mysql> SELECT something FROM tbl_name

    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

Some date functions can be used with "zero" dates or incomplete dates such as '2001-11-00', whereas others cannot. Functions that extract parts of dates typically work with incomplete dates. For example:

mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');

        -> 0, 0

Other functions expect complete dates and return NULL for incomplete dates. These include functions that perform date arithmetic or that map parts of dates to names. For example:

mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);

        -> NULL

mysql> SELECT DAYNAME('2006-05-00');

        -> NULL

• ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().

mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);

        -> '1998-02-02'

mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);

        -> '1998-02-02'

When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be added to expr.

mysql> SELECT ADDDATE('1998-01-02', 31);

        -> '1998-02-02'

• ADDTIME(expr1,expr2)

ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.

mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999',

    ->                '1 1:1:1.000002');

        -> '1998-01-02 01:01:01.000001'

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

        -> '03:00:01.999997'

• CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value.

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

        -> '2004-01-01 13:00:00'

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');

        -> '2004-01-01 22:00:00'

• CURDATE()

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

mysql> SELECT CURDATE();

        -> '1997-12-15'

Next