Date and time functions in MySQL

The date and time functions are used to add date and time values in valid formats. The main functions and their descriptions are listed below:

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

The ADDDATE() function adds the interval of days to the date in the first argument.

SELECT ADDDATE('2008-01-28', INTERVAL 7 DAY);

SELECT ADDDATE('2008-01-28', 7);

The example from above displays the output of '2008-02-04'.

ADDTIME(expr1,expr2)

The ADDTIME() function adds the time in 'expr2' to the datetime or time in 'expr1'. The example below shows how:

SELECT ADDTIME('2015-3-31 12:59:59.999999', '1:1:1.000002');

This example displays output as '2015-03-31 14:01:01.000001'.

SELECT ADDTIME('12:59:59.999999', '1:1:1.000002');

While this one displays output as '14:01:01.000001'.

The functions CURRENT_DATE() and CURDATE(), if in a string format, will display the current date as YYYY-MM-DD, else if it is in a numeric format it will display it as YYYYMMDD. The example shows how:

SELECT CURRENT_DATE();

The example above produces output as '2015-04-03'.

SELECT CURRENT_DATE() + 0;

While this one produces it as '20150403'.

CURRENT_TIME() or CURTIME()

The functions CURRENT_TIME() and CURTIME(), if written in a string format, will display the current time as HH:MM:SS, otherwise if written in a numeric format it gets displayed as HHMMSS. The example that follows shows how:

SELECT CURRENT_TIME();

This example gives output '19:14:49'.

SELECT CURRENT_TIME() + 0;

And this one gives output 191449.

DATE_FORMAT(date, format)

The DATE_FORMAT function formats date values according to the format argument. Below are listed a few examples of format specifiers:

%a -Abbreviated weekday name (Sun..Sat)
%b - Abbreviated month name (Jan..Dec)
%c - Month, numeric (0..12)
%D - Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...
%d - Day of the month, numeric (00..31)
%e - Day of the month, numeric (0..31)

SELECT DATE_FORMAT('2015-1-4 22:23:00', '%D %a %Y');

This example gives the output as '4th Sun 2015'.

DATE(expr)

The DATA() function extracts the date part of the datetime expression 'expr'. The example below shows how:

SELECT DATE('2015-3-31 12:59:59.999999');

This example gives the output as '2015-3-31'.

DAY(date) or DAYOFMONTH()

The DATE() function returns the day of the month for the date in range of 1 to 31. In the example that follows we see how:

SELECT DAY('2015-3-31');

This examples produces the output of '31'.

MINUTE(time)

The MINUTE() function returns the minute of the time in range of 0 to 59. The example below show how:

SELECT MINUTE('12:59:59.999999');

The example produces the output of '59'.

MONTH(date)

The MONTH() function returns month for the date in range of 1 to 12. The example shows how:

SELECT MONTH('2015-3-31');

The example produces the output of '3'.

NOW()

The NOW() function show the current date and time; if in a string format the current date and time is displayed as YYYY-MM-DD HH:MM:SS, else if it is in a numeric format it is displayed as YYYYMMDDHHMMSS. The follow up example shows us how it's done:

SELECT NOW();

This will output, at the moment of writing, '2015-04-03 19:40:48';

SELECT NOW() + 0;

This example will output (at the moment of writing)'20150403194109';

SECOND(time)

The SECOND() function returns the second of the time in range of 0 to 59. Example:

SELECT MINUTE('12:59:59.999999');

This example produces the output of '59'.

STR_TO_DATE(str, format)

The STR_TO_DATE() funciton produces the inverse result of the DATE_FORMAT(). It takes the string and formats it as arguments and outputs the DATETIME value according to the format string. The example show it:

SELECT STR_TO_DATE('01,3,2015','%d,%m,%Y');

The example above gives output as '2015-03-01;

TIME(expr)

The TIME() function extracts time or datetime expression in 'expr' and returns it as a string. The following example presents it:

SELECT TIME('2015-3-31 12:59:59');

This example gives output as '12:59:59'.

TIMESTAMP(expr) or TIMESTAMP(expr1, expr2)

The function TIMESTAMP(), with a single argument, returns the date or datetime expression 'expr' as a datetime value, while with two arguments, it adds the time expression 'expr2' to the date or datetime expression 'expr1' and returns the result as a datetime value. The following example shows us how:

SELECT TIMESTAMP('2015-3-31');

This example gives output as '2015-03-31 00:00:00'.

SELECT TIMESTAMP('2015-3-31', '01:09:34');

While this one gives output as '2015-03-31 01:09:34'.

YEAR(date)

The YEAR() function returns year for the given date. The example shows how:

SELECT YEAR('2015-3-31');

The result if above example will be '2015'.