Date Time Functions in mysql

Date/Time Functions

ADDDATE( ) and DATE_ADD( ) functions

The following SELECT statement uses the ADDDATE( ) function to add 10 hours and 20 minutes to the specified date/time value:
SELECT ADDDATE(‘2004-10-31 13:39:59’, INTERVAL ‘10:20’ HOUR_MINUTE);

This form of the ADDDATE() syntax allows you to specify a date value as the first argument and a number of days as the second argument. These are the number of days that are to be added to the specified date. 

For example, the following SELECT statement adds 31 days to the date in the first argument:
SELECT ADDDATE(‘2004-11-30 23:59:59’, 31);

The statement returns a result of 2004-12-31 23:59:59, which is 31 days after the original date.
Notice that the time value remains the same.

In addition to being able to add to a date, you can also subtract from a date by using the SUBDATE() or DATE_SUB() functions

SELECT SUBDATE(‘2004-10-31 23:59:59’, INTERVAL ‘12:10’ HOUR_MINUTE);

The SUBDATE() function also includes a second form, which allows you to subtract a specified number of days from a date:
For example, the following SELECT statement subtracts 31 days from the specified date:

SELECT SUBDATE(‘2004-12-31 23:59:59’, 31);
the following SELECT statement extracts the year and month from the specified date:

SELECT EXTRACT(YEAR_MONTH FROM ‘2004-12-31 23:59:59’);
The EXTRACT() function in this statement includes the type YEAR_MONTH. 

As a result, the year (2004) and month (12) are extracted from the original value and returned as 200412.

EXTRACT() function is handy if you have an application that must display only part of a date.

 For example, suppose that a table in your database includes a TIMESTAMP column. Your application should display only the date portion of the column value. You can use the EXTRACT() function to retrieve only the date portion of the value and use that date portion in your application.

CURDATE(),  CURRENT_DATE(), CURTIME(),  CURRENT_TIME(),  CURRENT_TIMESTAMP(),  and NOW() Functions

For example, if you want to retrieve the current date, you can use the following SELECT statement:
SELECT CURDATE();
The statement retrieves a value similar to 2004-09-08

You can retrieve the current time by using the CURTIME() or CURRENT_TIME functions, which are also synonymous:  CURTIME()
retrieving the date and the time, you can retrieve both in one value by using the
NOW() or CURRENT_TIMESTAMP() functions, which are also synonymous: NOW()

DATE(), MONTH(), MONTHNAME(), and YEAR() Functions

SELECT DATE(‘2004-12-31 23:59:59’);
This statement retrieves the full date value of 2004-12-31.

SELECT MONTH(‘2004-12-31 23:59:59’);
SELECT MONTHNAME(‘2004-12-31 23:59:59’);

Now your result is the value December, instead of 12

SELECT YEAR(‘2004-12-31 23:59:59’);
The statement returns the value 2004.

DATEDIFF() and TIMEDIFF() Functions

For example, the following SELECT statement specifies two dates that are exactly one year apart:
SELECT DATEDIFF(‘2004-12-31 23:59:59’, ‘2003-12-31 23:59:59’);

The statement returns a value of 366 (because 2004 is a leap year). Notice that the most recent date is specified first. You can specify them in any order, but if the less recent date is specified first, your results are a negative number because of the way dates are compared.

SELECT TIMEDIFF(‘2004-12-31 23:59:59’, ‘2004-12-30 23:59:59’);
This time, the time difference is returned as 24:00:00, indicating that the time difference between the two  is exactly 24 hours.

DAY(), DAYOFMONTH(),   DAYNAME(),  DAYOFWEEK(),and DAYOFYEAR() Functions,   SECOND(), MINUTE(), HOUR(), and TIME() Functions

SELECT DAY(‘2004-12-31 23:59:59’); The day in this case is 31,
SELECT DAYNAME(‘2004-12-31 23:59:59’); returned is Friday

The function returns a value from 1 through 7, with Sunday being 1. For example, the following SELECT statement calculates the day of the week for December 31, 2004.

SELECT DAYOFWEEK(‘2004-12-31 23:59:59’);
The day in this case is Friday. Because Friday is the sixth day, the statement returns a value of 6.

SELECT DAYOFYEAR(‘2004-12-31 23:59:59’);
Because 2004 is a leap year, the statement returns the value 366.


SELECT statement extracts 59 from the specified value.
SELECT SECOND(‘2004-12-31 23:59:59’);


statement would extract 59 from the time value:
SELECT MINUTE(‘2004-12-31 23:59:59’);


SELECT HOUR(‘2004-12-31 23:59:59’);
SELECT TIME(‘2004-12-31 23:59:59’);
In this case, the function returns the value 23:59:59.

SELECT DVDID, DateOut, DateDue, DATEDIFF(DateDue, DateOut) AS TotalDays
FROM Transactions
WHERE TransID=11;
------------------------------------------------------------------------------------------------------------------

SELECT DVDID, DateDue, DAYNAME(DateDue) AS DayDue
FROM Transactions
WHERE TransID=11;

-------------------------------------------------------------------------------------------------------------------

SELECT TransID, YEAR(DateOut) AS YearOut
FROM Transactions
WHERE TransID>15
ORDER BY TransID;

---------------------------------------------------------------------------------------------------------------------

SELECT TransID, DateDue, DATE_ADD(DateDue, INTERVAL 4 DAY) AS Add4Days
FROM Transactions
WHERE TransID=11;

------------------------------------------------------------------------------------------------------

SELECT last_name, first_name, birth
FROM president WHERE MONTHNAME(birth) = 'March';

----------------------------------------------------------------------------------------------------------

SELECT last_name, first_name, birth
FROM president WHERE MONTH(birth) = 3 AND DAYOFMONTH(birth) = 29;

----------------------------------------------------------------------------------------------------------------

SELECT last_name, first_name, birth, death,
TIMESTAMPDIFF(YEAR, birth, death) AS age
FROM president WHERE death IS NOT NULL
ORDER BY age DESC LIMIT 5;

-------------------------------------------------------------------------------------------

SELECT last_name, first_name, expiration FROM member
WHERE (TO_DAYS(expiration) - TO_DAYS(CURDATE())) < 60;

------------------------------------------------------------------------------------------------

SELECT last_name, first_name, expiration FROM member
WHERE TIMESTAMPDIFF(DAY, CURDATE(), expiration) < 60;
---------------------------------------------------------------------------------------------------


SELECT DATE_ADD('1970-1-1', INTERVAL 10 YEAR);
SELECT DATE_SUB('1970-1-1', INTERVAL 10 YEAR);

-----------------------------------------------------------------------------------------------------

SELECT last_name, first_name, death
FROM president
WHERE death >= '1970-1-1'
AND death < DATE_ADD('1970-1-1', INTERVAL 10 YEAR);