-- Adds 31 days to 2008-05-22
SELECT ADDDATE('2008-05-22', 31);

/*
Add time to time value and return. Returned value is 
accurate up to microsecond - one thousandth of a millisecond.
*/
SELECT ADDTIME('16:00:00.567', '02:00:00.123');

-- Add time to date and time value
SELECT ADDTIME('2008-05-22 23:59:59.999999', '1:1:1.000002');

/*
Convert the given date and time from GMT time
to Europe/Moscow time.
*/
SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','Europe/Moscow');

/*
Convert the given date and time value from
time zone GMT+07:00 to time zone GMT+08:00 
*/
SELECT CONVERT_TZ('2005-06-06 2:00:00','+7:00','+8:00');

/*
The given date and time value falls out of 
the supported range of the TIMESTAMP type, 
no conversion occurs.
 
In this example, 1969-02-23 12:00:00 falls 
out of UTC timestamp range which is 
'1970-01-01 00:00:01' UTC to the year 2038.
*/
SELECT CONVERT_TZ('1969-02-23 12:00:00','GMT','Europe/Moscow');

/*
Use CURDATE in string context and return 
current date in YYYY-MM-DD format.
*/
SELECT CURDATE();

/*
Use CURDATE in numeric context and 
return current date in YYYYMMDD format.
*/
SELECT CURDATE() + 5;

/*
Use CURTIME in string context and return 
current date in HH:MM:SS format.
*/
SELECT CURTIME();

/*
Use CURTIME in string context and return 
current date in HHMMSS.uuuuuu format.
*/
SELECT CURTIME() + 0;

-- Add 1 microsecond
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL 1 MICROSECOND) AS new_date;

-- Add 1 second
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL 1 SECOND) AS new_date;

-- Add 12 minutes
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL 12 MINUTE) AS new_date;

-- Add 3 hours
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL 3 HOUR) AS new_date;

-- Add 5 days
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL 5 DAY) AS new_date;

-- Add 5 weeks
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL 5 WEEK) AS new_date;

-- Add 5 months
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL 5 MONTH) AS new_date;

-- Add 2 quarters
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL 2 QUARTER) AS new_date;

-- Add 3 years
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL 3 YEAR) AS new_date;

-- Add 1 second and 123 microseconds 
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1.000123' SECOND_MICROSECOND) AS new_date;

-- Add 1 minute, 1 second, and 123 microseconds 
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1.1.000123' MINUTE_MICROSECOND) AS new_date;

-- Add 1 minute and 15 seconds 
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1:15' MINUTE_SECOND) AS new_date;

-- Add 1 hour, 1 minute, 1 second, and 123 microseconds 
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1.1.1.000123' HOUR_MICROSECOND) AS new_date;

-- Add 5 hours, 21 minutes, and 58 seconds
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '5:21:58' HOUR_SECOND) AS new_date;

-- Add 5 hours, 21 minutes
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '5:21' HOUR_MINUTE) AS new_date;

-- Add 1 day, 1 hour, 1 minute, 1 second, and 123 microseconds
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1.1.1.1.000123' DAY_MICROSECOND) AS new_date;

-- Add 1 day, 2 hours, 5 minutes, and 27 seconds 
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1 2:05:27' DAY_SECOND) AS new_date;

-- Add 1 day, 2 hours, and 5 minutes 
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1 2:05' DAY_MINUTE) AS new_date;

-- Add 1 day and 2 hours
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1 2' DAY_HOUR) AS new_date;

-- Add 1 year and 5 months 
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1 5' YEAR_MONTH) AS new_date;

/*
Query 2,3, and 4 return the same result as Query 1. This 
is because the query does not include all the interval 
parts that would be expected from the unit keyword.
 
For example, when you specify MINUTE_MICROSECOND in Query 2, 
the value of expr is expected to have minutes, seconds, 
and microseconds part. Because you specify a value '1.000123', 
MySQL assumes that the minutes part is missing and the value 
actually only represents seconds and microseconds.
 
In other words, '1.000123' MINUTE_MICROSECOND is interpreted in 
such a way that it is equivalent to '1.000123' SECOND_MICROSECOND. 
*/
 
-- Query 1
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1.000123' SECOND_MICROSECOND) AS new_date;
 
-- Query 2
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1.000123' MINUTE_MICROSECOND) AS new_date;
 
-- Query 3
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1.000123' HOUR_MICROSECOND) AS new_date;
 
-- Query 4
SELECT DATE_ADD('1992-12-31 23:59:59.000002', 
    INTERVAL '1.000123' DAY_MICROSECOND) AS new_date;

-- Add 1 second by using + operator
SELECT '1992-12-31 23:59:59.000002' 
    + INTERVAL 1 SECOND AS new_result;

-- Minus 1 second by using - operator
SELECT '1992-12-31 23:59:59.000002' 
    - INTERVAL 1 SECOND AS new_result;