Date and Time Functions | Mastering MySQL 4

You use date and time functions when working with time, such as when returning the current time in a certain format or seeing how many days until a certain date. In most cases, values of type date are stored as YYYY-MM-DD (for example, 2002-12-25), and values of type time are stored as hh:mm:ss (for example, 11:23:43). There is also the datetime type, stored as YYYY-MM-DD hh:mm:ss. Most functions that accept dates or times will accept the datetime format and ignore the part they don't need. Similarly, if you are short of values (when asked for a hh:mm:ss, you only enter the mm:ss portion), MySQL will assume the rest are zeros and perform the calculation correctly. You can use any delimiter in the date and time strings, rather than colons (:) and dashes (–) as long as you are consistent.

Certain functions make use of a specific date type (for example, DATE_ADD(), which needs a type interval to perform its calculation).

The following are the date and time types:

SECOND
MINUTE
HOUR
DAY
MONTH
YEAR
MINUTE_SECOND: "mm:ss" (for example, "41:23")
HOUR_MINUTE: "hh:mm" (for example, "12:23")
DAY_HOUR: "DD hh" (for example, "11 09")
YEAR_MONTH: "YYYY-MM" (for example, "2002-12")
HOUR_SECOND: "hh:mm:ss" (for example, "11:24:36")
DAY_MINUTE: "DD hh:mm" (for example, "09 11:31")
DAY_SECOND: "DD hh:mm:ss" (for example, "09 11:31:21")

To perform date calculations, you can also use the usual operators (+, –, and so on) rather than the date functions. MySQL also correctly converts between units. When, for example, you add 1 month to month 12, MySQL will increment the year and correctly calculate the months.

ADDDATE

ADDDATE(date,INTERVAL expression type)

A synonym for DATE_ADD().

CURDATE

CURDATE()

A synonym for the CURRENT_DATE() function.

CURRENT_DATE

CURRENT_DATE()

Returns the current system date as either the string YYYY-MM-DD or the numeric YYYYMMDD depending on the context.

For example:

mysql> SELECT CURRENT_DATE(); +----------------+ | CURRENT_DATE() | +----------------+ | 2002-09-10     | +----------------+ mysql> SELECT CURRENT_DATE()+1; +------------------+ | CURRENT_DATE()+1 | +------------------+ |         20020911 | +------------------+

CURRENT_TIME

CURRENT_TIME()

Returns the current system time as either the string hh:mm:ss or the number hhmmss, depending on the context of the function.

For example:

mysql> SELECT CURRENT_TIME(); +----------------+ | CURRENT_TIME() | +----------------+ | 23:53:15       | +----------------+ mysql> SELECT CURRENT_TIME() + 1; +--------------------+ | CURRENT_TIME() + 1 | +--------------------+ |             235434 | +--------------------+

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

This function is a synonym for the NOW() function.

CURTIME

CURTIME()

A synonym for the CURRENT_TIME() function.

DATE_ADD

DATE_ADD(date,INTERVAL expression type)

Adds a certain time period to the specified date. You can use a negative value for the expression, in which case it will be subtracted. The type must be one of those listed at the beginning of this section ("Date and Time Functions"), and the expression must match the type.

For example:

mysql> SELECT DATE_ADD('2002-12-25',INTERVAL 1 MONTH); +-----------------------------------------+ | DATE_ADD('2002-12-25',INTERVAL 1 MONTH) | +-----------------------------------------+ | 2003-01-25                              | +-----------------------------------------+ mysql> SELECT DATE_ADD('2002-12-25 13:00:00',INTERVAL -14 HOUR); +---------------------------------------------------+ | DATE_ADD('2002-12-25 13:00:00',INTERVAL -14 HOUR) | +---------------------------------------------------+ | 2002-12-24 23:00:00                               | +---------------------------------------------------+

DATE_FORMAT

DATE_FORMAT(date,format_string)

Formats the specified date based upon the format string, which can consist of the specifiers shown in Table B.2.

Table B.2: Date Format Specifiers
Specifier	Description
%a	Abbreviation of the day name (SunSat)
%b	Abbreviation of the month name (JanDec)
%c	Numeric month from 112
%D	Numeric day of the month with English suffix (1st, 2nd, and so on)
%d	Numeric day of the month with two digits, from 0031
%e	Numeric day of the month with one or two digits, from 031
%H	Hour from 00 to 23
%h	Hour from 0112
%I	Minutes from 0059
%I	Hour from 0112
%j	Day of the year from 001366
%k	Hour with one or two digits, from 023
%l	Hour with one digit, from 112
%M	Month name from JanuaryDecember
%m	Numeric month from 0112
%p	a.m. or p.m.
%r	12-hour time, hh:mm:ss a.m. or p.m.
%S	Seconds from 0059
%s	Seconds from 0059
%T	24 hour time, hh:mm:ss
%U	Week from 0053, with Sunday being the first day of the week
%u	Week from 0053 with Monday being the first day of the week
%V	Week from 0153 with Sunday being the first day of the week
%v	Week from 0153 with Monday being the first day of the week
%W	Name of the day in the week from SundaySaturday
%w	Day of the week from 0 for Sunday, to 6 for Saturday
%X	Four-digit numeric year for the week with Sunday being the first day of the week
%x	Four-digit numeric year for the week with Monday being the first day of the week
%Y	Four-digit numeric year
%y	Two-digit numeric year
%%	Escaped percentage sign

For example:

mysql> SELECT DATE_FORMAT('1999-03-02','%c %M'); +-----------------------------------+ | DATE_FORMAT('1999-03-02','%c %M') | +-----------------------------------+ | 3 March                           | +-----------------------------------+

DATE_SUB

DATE_SUB(date,INTERVAL expression type)

Subtracts a certain time period from the specified date. You can use a negative value for the expression, in which case it will be added. The type must be one of those listed at the beginning of this section ("Date and Time Functions"), and the expression must match the type.

For example:

mysql> SELECT DATE_SUB('2002-12-25 13:00:00',INTERVAL "14:13" MINUTE_SECOND); +----------------------------------------------------------------+ | DATE_SUB('2002-12-25 13:00:00',INTERVAL "14:13" MINUTE_SECOND) | +----------------------------------------------------------------+ | 2002-12-25 12:45:47                                            | +----------------------------------------------------------------+

DAYNAME

DAYNAME(date)

Returns the name of the day for the specified date.

For example:

mysql> SELECT DAYNAME('2000-12-25'); +-----------------------+ | DAYNAME('2000-12-25') | +-----------------------+ | Monday                | +-----------------------+

DAYOFMONTH

DAYOFMONTH(date)

Returns the day of the month for the supplied date as a number from 1 to 31.

For example:

mysql> SELECT DAYOFMONTH('2000-01-01'); +--------------------------+ | DAYOFMONTH('2000-01-01') | +--------------------------+ |                        1 | +--------------------------+

DAYOFWEEK

DAYOFWEEK(date)

Returns the day of the week for the supplied date as a number from 1 for Sunday to 7 for Saturday, which is the Open Database Connectivity (ODBC) standard.

For example:

mysql> SELECT DAYOFWEEK('2000-01-01'); +-------------------------+ | DAYOFWEEK('2000-01-01') | +-------------------------+ |                       7 | +-------------------------+

Use WEEKDAY() to return the day index from 0–6, Monday to Sunday.

DAYOFYEAR

DAYOFYEAR(date)

Returns the day of the year for the supplied date as a number from 1 to 366.

For example:

mysql> SELECT DAYOFYEAR('2000-12-25'); +-------------------------+ | DAYOFYEAR('2000-12-25') | +-------------------------+ |                     360 | +-------------------------+

EXTRACT

EXTRACT(date_type FROM date)

Uses the specified date type to return the portion of the date. See the list of date types before the start of the date functions.

For example:

mysql> SELECT EXTRACT(YEAR FROM '2002-02-03'); +---------------------------------+ | EXTRACT(YEAR FROM '2002-02-03') | +---------------------------------+ |                            2002 | +---------------------------------+ mysql> SELECT EXTRACT(MINUTE_SECOND FROM '2002-02-03 12:32:45'); +---------------------------------------------------+ | EXTRACT(MINUTE_SECOND FROM '2002-02-03 12:32:45') | +---------------------------------------------------+ |                                              3245 | +---------------------------------------------------+

FROM_DAYS

FROM_DAYS(number)

Converts the specified number into a date based on the number of days since Jan 1, year 0, and returns the result. Does not take the days lost in the change to the Gregorian calendar into account.

For example:

mysql> SELECT FROM_DAYS(731574); +-------------------+ | FROM_DAYS(731574) | +-------------------+ | 2002-12-25        | +-------------------+

FROM_UNIXTIME

FROM_UNIXTIME(unix_timestamp [, format_string])

Converts the specified timestamp into a date and returns the result. The returned date will be formatted if there is a format string supplied. The format string can be any of those from the DATE_FORMAT() function.

For example:

mysql> SELECT FROM_UNIXTIME(100); +---------------------+ | FROM_UNIXTIME(100)  | +---------------------+ | 1970-01-01 00:01:40 | +---------------------+ mysql> SELECT FROM_UNIXTIME(1031621727,'%c %M'); +-----------------------------------+ | FROM_UNIXTIME(1031621727,'%c %M') | +-----------------------------------+ | 9 September                       | +-----------------------------------+

HOUR

HOUR(time)

Returns the hour for the specified time, from 0 to 23.

For example:

mysql> SELECT HOUR('06:59:03'); +------------------+ | HOUR('06:59:03') | +------------------+ |                6 | +------------------+

MINUTE

MINUTE(time)

Returns the minutes for the specified time, from 0 to 59.

For example:

mysql> SELECT MINUTE('00:01:03'); +--------------------+ | MINUTE('00:01:03') | +--------------------+ |                  1 | +--------------------+

MONTH

MONTH(date)

Returns the month for the specified date, from 1 to 12.

For example:

mysql> SELECT MONTH('2000-12-25'); +---------------------+ | MONTH('2000-12-25') | +---------------------+ |                  12 | +---------------------+

MONTHNAME

MONTHNAME(date)

Returns the name of the month for the specified date. For example:

mysql> SELECT MONTHNAME('2000-12-25'); +-------------------------+ | MONTHNAME('2000-12-25') | +-------------------------+ | December                | +-------------------------+

NOW

NOW()

Returns the current timestamp (date and time in the format YYYY-MM-DD hh:mm:ss), either as a string or numeric depending on the context. The function will return the same result for multiple calls on a single query.

For example:

mysql> SELECT NOW(); +---------------------+ | NOW()               | +---------------------+ | 2002-09-10 00:58:06 | +---------------------+

A synonym for the CURRENT_TIMESTAMP() and SYSDATE() functions.

PERIOD_ADD

PERIOD_ADD(period,months)

Adds the months to the period (specified as either YYMM or YYYYMM) and returns the result as YYYYMM.

For example:

mysql> SELECT PERIOD_ADD(200205,3); +----------------------+ | PERIOD_ADD(200205,3) | +----------------------+ |               200208 | +----------------------+ mysql> SELECT PERIOD_ADD(200205,-42); +------------------------+ | PERIOD_ADD(200205,-42) | +------------------------+ |                 199811 | +------------------------+

PERIOD_DIFF

PERIOD_DIFF(period1,period2)

Returns the number of months between period and period2 (which are specified in the format YYMM or YYYYMM).

For example:

mysql> SELECT PERIOD_DIFF(200212,200001); +----------------------------+ | PERIOD_DIFF(200212,200001) | +----------------------------+ |                         35 | +----------------------------+ mysql> SELECT PERIOD_DIFF(199903,199904); +----------------------------+ | PERIOD_DIFF(199903,199904) | +----------------------------+ |                         -1 | +----------------------------+

QUARTER

QUARTER(date)

Returns the quarter of the specified date, from 1 to 4.

For example:

mysql> SELECT QUARTER('2002-06-30'); +-----------------------+ | QUARTER('2002-06-30') | +-----------------------+ |                     2 | +-----------------------+

SEC_TO_TIME

SEC_TO_TIME(seconds)

Converts the seconds to time, returning either a string (hh:mm:ss) or numeric (hhmmss) depending on the context.

For example:

mysql> SELECT SEC_TO_TIME(1000); +-------------------+ | SEC_TO_TIME(1000) | +-------------------+ | 00:16:40          | +-------------------+ mysql> SELECT SEC_TO_TIME(-10000); +---------------------+ | SEC_TO_TIME(-10000) | +---------------------+ | -02:46:40           | +---------------------+

SECOND

SECOND(time)

Returns the seconds for the specified time, from 0 to 58.

For example:

mysql> SELECT SECOND('00:01:03'); +--------------------+ | SECOND('00:01:03') | +--------------------+ |                  3 | +--------------------+

SUBDATE

SUBDATE(date,INTERVAL expression type)

A synonym for DATE_SUB().

SYSDATE

SYSDATE()

A synonym for the NOW() function.

TIME_FORMAT

TIME_FORMAT(time,format)

Identical to DATE_FORMAT() except that you can only use the subset of formats dealing with time (or else you'll return NULL).

TIME_TO_SEC

TIME_TO_SEC(time)

Converts the time to seconds and returns the result. For example:

mysql> SELECT TIME_TO_SEC('00:01:03'); +-------------------------+ | TIME_TO_SEC('00:01:03') | +-------------------------+ |                      63 | +-------------------------+

TO_DAYS

TO_DAYS(date)

Returns the number of days since Jan 1 year 0 for the specified date. Does not take the days lost in the change to the Gregorian calendar into account.

For example:

mysql> SELECT TO_DAYS('2000-01-01'); +-----------------------+ | TO_DAYS('2000-01-01') | +-----------------------+ |                730485 | +-----------------------+

UNIX_TIMESTAMP

UNIX_TIMESTAMP([date])

Returns an unsigned integer representing the Unix timestamp (the seconds since midnight, Jan. 1, 1970) of either the current system time (if called without a parameter) or the specified date.

For example:

mysql> SELECT UNIX_TIMESTAMP(); +------------------+ | UNIX_TIMESTAMP() | +------------------+ |       1031621727 | +------------------+ mysql> SELECT UNIX_TIMESTAMP('1970-01-01 00:01:40'); +---------------------------------------+ | UNIX_TIMESTAMP('1970-01-01 00:01:40') | +---------------------------------------+ |                                   100 | +---------------------------------------+

WEEK

WEEK(date [,week_start])

Returns the week in a given year for the specified date, from 0 to 53. The week is assumed to start on Sunday, unless the optional week_start argument is set to 1, in which case the week is assumed to start on Monday. It can also explicitly set to 0 for Sunday starts. The function will return 0 for dates before the first Sunday (or Monday) of the year.

For example:

mysql> SELECT WEEK('2002-06-31'); +--------------------+ | WEEK('2002-06-31') | +--------------------+ |                 26 | +--------------------+ mysql> SELECT WEEK('2002-06-31',1); +----------------------+ | WEEK('2002-06-31',1) | +----------------------+ |                   27 | +----------------------+ mysql> SELECT WEEK('1998-12-31',1); +----------------------+ | WEEK('1998-12-31',1) | +----------------------+ |                   53 | +----------------------+ mysql> SELECT WEEK('1998-01-01'); +--------------------+ | WEEK('1998-01-01') | +--------------------+ |                  0 | +--------------------+

Use the YEARWEEK() function to roll the week over from the previous year if the date is before the first Sunday (or Monday) of the year.

WEEKDAY

WEEKDAY(date)

Returns the day of the week for the supplied date as a number from 0 for Monday to 6 for Sunday.

For example:

mysql> SELECT WEEKDAY('2000-01-01'); +-----------------------+ | WEEKDAY('2000-01-01') | +-----------------------+ |                     5 | +-----------------------+

Use DAYOFWEEK() to return the day index according to the ODBC standard (1–7, Sunday– Saturday).

YEAR

YEAR(date)

Returns the year for the specified date, from 1000 to 9999.

For example:

 mysql> SELECT YEAR('2002-06-30'); +--------------------+ | YEAR('2002-06-30') | +--------------------+ |               2002 | +--------------------+

YEARWEEK

YEARWEEK(date [,week_start])

Returns a combination of year and week for the specified date. The week is assumed to start on Sunday, unless the optional week_start argument is set to 1, in which case the week is assumed to start on Monday. It can also explicitly set to 0 for Sunday starts. The year could be the previous year to the date for dates before the first Sunday (or Monday) in the year or in the following year.

For example:

mysql> SELECT YEARWEEK('2002-12-25'); +------------------------+ | YEARWEEK('2002-12-25') | +------------------------+ |                 200251 | +------------------------+ mysql> SELECT YEARWEEK('1998-12-31',1); +--------------------------+ | YEARWEEK('1998-12-31',1) | +--------------------------+ |                   199853 | +--------------------------+ mysql> SELECT YEARWEEK('1998-01-01'); +------------------------+ | YEARWEEK('1998-01-01') | +------------------------+ |                 199752 | +------------------------+

Use the WEEK() function to return the week in a given year.