SQL Pocket Guide, 3rd Edition

Datetime Functions: MySQL

MySQL implements the following functions to return the current date and time:

CURDATE() or CURRENT_DATE

Returns the current date as a string ('YYYY-MM-DD') or a number (YYYYMMDD), depending on the context.

CURTIME() or CURRENT_TIME

Returns the current time as a string ('HH:MI:SS') or a number (HHMISS), depending on the context.

NOW(), SYSDATE(), or CURRENT_TIMESTAMP

Returns the current date and time as a string ('YYYY-MM-DD HH:MI:SS') or a number (YYYYMMDDHHMISS), depending on the context.

UNIX_TIMESTAMP

Returns the number of seconds since the beginning of 1-Jan-1970 as an integer.

MySQL also implements the following functions for adding and subtracting intervals from dates.

DATE_ADD(date , INTERVAL value units)

Adds value number of units to the date. You can use ADDDATE as a synonym for DATE_ADD.

DATE_SUB(date , INTERVAL value units)

Subtracts value number of units from the date. You can use SUBDATE as a synonym for DATE_SUB.

For example, to add one month to the current date:

SELECT DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH);

Or, to subtract one year and two months:

SELECT DATE_SUB(CURRENT_DATE,
                INTERVAL '1-2' YEAR_MONTH);

Valid interval keywords for numeric intervals include SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR. You can also use the string-based formats shown in Table 1-5.

Table 1-5. MySQL string-based interval formats

Keyword	Format
DAY_HOUR	'dd hh'
DAY_MINUTE	'dd hh:mi'
DAY_SECOND	'dd hh:mi:ss'
HOUR_MINUTE	'HH:MI'
HOUR_SECOND	'hh:mi:ss'
MINUTE_SECOND	'MI:SS'
YEAR_MONTH	'yy-mm'