Date and Time Functions

A list of the more useful date and time functions is shown in Table 8.5. Because of MySQL's data warehousing heritage, it has a very extensive set of time and date functions. The list here is far from complete.

Table 8.5. Date and Time Functions

Function	Purpose
adddate( date , INTERVAL n type ) and subdate( date , INTERVAL n type )	These functions are used to add and subtract dates. Both start from the date supplied in date and add or subtract the period specified after the keyword INTERVAL . You need to specify both a quantity n and the type of that quantity. The type can be SECOND , MINUTE , HOUR , DAY , MONTH , YEAR , MINUTE:SECOND (the format of n should be 'm:s' ), HOUR:MINUTE ( 'h:m' ), DAY_HOUR ( 'd h' ), YEAR_MONTH ( 'y-m' ), HOUR_SECOND ( 'h:m:s' ), DAY_MINUTE ( 'd h:m' ), DAY_SECOND ( 'd h:m:s' ). These functions are really useful, but remembering the data formats is virtually impossible (because they are all different), so you will usually have to look them up.
curdate(), curtime(), now()	These return the current date, the current time, and the current date and time, respectively.
date_format( date, format ) and time_format( time, format )	These are used to reformat dates and times to pretty much any format you like. You do this by supplying a format string, such as date_format(workdate, '%W %D of %M, %Y') . (This gives, for example, 'Monday 16th of June, 2003' ). There is a massive list of formats, so consult the manual for details.
dayname( date )	This returns the name of the day in date (for example, 'Monday' ).
extract( type FROM date )	This returns the value of type in date . For example, if you specify YEAR , it will return the year from date . The types are the same as in adddate() and subdate() .
unix_timestamp( [date] )	This returns the current Unix timestamp. (That's the number of seconds since the first of January 1970.) If called with a date , this returns the timestamp corresponding to that date .

Let's look at an example using adddate() . We will start from the 1st of January 1999 and add 1 year and 6 months to it:

 select adddate("1999-01-01", INTERVAL "1-6" YEAR_MONTH); 

We get the following result:

 +--------------------------------------------------+  adddate("1999-01-01", INTERVAL "1-6" YEAR_MONTH)  +--------------------------------------------------+  2000-07-01                                        +--------------------------------------------------+ 1 row in set (0.41 sec) 

For many applications in which you are formatting output with an external program, this may not be the most useful way to receive the date. Unix timestamps are not humanly readable, but they are very compatible with the APIs that come with other programming languages.

The same query with the unix_timestamp() function

 select unix_timestamp(adddate("1999-01-01", INTERVAL "1-6" YEAR_MONTH)); 

generates output useless to humans but directly usable by other code that is such as the date() function built into PHP:

 +------------------------------------------------------------------+  unix_timestamp(adddate("1999-01-01", INTERVAL "1-6" YEAR_MONTH))  +------------------------------------------------------------------+                                                         962373600  +------------------------------------------------------------------+ 1 row in set (0.01 sec)