MySQL - Date & Time Library Function

ADDDATE(): The ADDDATE function in MySQL adds a specified number of days to a date. 

SELECT ADDDATE('2022-03-10', INTERVAL 7 DAY);

This query will return the date '2022-03-17', which is 7 days after the date '2022-03-10'.

 

ADDTIME(): The ADDTIME function in MySQL adds a specified time interval to a time value. 

SELECT ADDTIME('12:30:00', '02:15:30');

This query will return the time value '14:45:30', which is 2 hours and 15 minutes and 30 seconds after the time value '12:30:00'.

 

CURDATE(): The CURDATE function in MySQL returns the current date. 

SELECT CURDATE();

This query will return the current date in the format 'YYYY-MM-DD'.

 

CURRENT_DATE(): The CURRENT_DATE function in MySQL also returns the current date. 

SELECT CURRENT_DATE();

This query will return the current date in the format 'YYYY-MM-DD'.

 

CURRENT_TIME(): The CURRENT_TIME function in MySQL returns the current time. 

SELECT CURRENT_TIME();

This query will return the current time in the format 'HH:MM:SS'.

 

DATE(): The DATE function in MySQL extracts the date part of a date or datetime expression. 

SELECT DATE('2022-03-10 12:34:56');

This query will return the date '2022-03-10'.

 

DATEDIFF(): The DATEDIFF function in MySQL calculates the number of days between two dates. 

SELECT DATEDIFF('2022-03-15', '2022-03-10');

This query will return 5, which is the number of days between the two dates.

DATE_ADD(): The DATE_ADD function in MySQL adds a specified time interval to a date. 

SELECT DATE_ADD('2022-03-10', INTERVAL 1 MONTH);

This query will return the date '2022-04-10', which is one month after the date '2022-03-10'.

 

DATE_SUB(): The DATE_SUB function in MySQL subtracts a specified time interval from a date. 

SELECT DATE_SUB('2022-03-10', INTERVAL 1 WEEK);

This query will return the date '2022-03-03', which is one week before the date '2022-03-10'.

 

DAY(): The DAY function in MySQL extracts the day of the month from a date. 

SELECT DAY('2022-03-10');

This query will return 10, which is the day of the month for the date '2022-03-10'.

 

DAYNAME(): The DAYNAME function in MySQL returns the name of the day of the week for a given date. 

SELECT DAYNAME('2022-03-10');

This query will return 'Thursday', which is the day of the week for the date '2022-03-10'.

 

DAYOFMONTH(): The DAYOFMONTH function in MySQL returns the day of the month for a given date. 

SELECT DAYOFMONTH('2022-03-10');

This query will return 10, which is the day of the month for the date '2022-03-10'.

 

DAYOFWEEK(): The DAYOFWEEK function in MySQL returns the day of the week as a number for a given date. 

SELECT DAYOFWEEK('2022-03-10');

This query will return 5, which represents Thursday, the day of the week for the date '2022-03-10'.

 

DAYOFYEAR(): The DAYOFYEAR function in MySQL returns the day of the year for a given date. 

SELECT DAYOFYEAR('2022-03-10');

This query will return 69, which is the day of the year for the date '2022-03-10'.

 

EXTRACT(): The EXTRACT function in MySQL extracts a specific part of a date or time value, such as the year, month, or day. 

SELECT EXTRACT(YEAR FROM '2022-03-10');

This query will return 2022, which is the year part of the date '2022-03-10'. You can replace YEAR with MONTH or DAY to extract the corresponding part of the date.

 

FROM_DAYS(): The FROM_DAYS function in MySQL converts a number of days since 0000-00-00 to a date. 

SELECT FROM_DAYS(737989);

This query will return the date '2022-03-10', which is 737989 days after 0000-00-00.

 

HOUR(): The HOUR function in MySQL returns the hour of a given time. 

SELECT HOUR('12:34:56');

This query will return 12, which is the hour part of the time '12:34:56'.

 

LAST_DAY(): The LAST_DAY function in MySQL returns the last day of the month for a given date. 

SELECT LAST_DAY('2022-03-10');

This query will return the date '2022-03-31', which is the last day of the month for the date '2022-03-10'.

 

MINUTE(): The MINUTE function in MySQL returns the minute of a given time. 

SELECT MINUTE('12:34:56');

This query will return 34, which is the minute part of the time '12:34:56'.

 

MONTH(): The MONTH function in MySQL returns the month of a given date. 

SELECT MONTH('2022-03-10');

This query will return 3, which is the month part of the date '2022-03-10'.

 

MONTHNAME(): The MONTHNAME function in MySQL returns the name of the month for a given date. 

SELECT MONTHNAME('2022-03-10');

This query will return 'March', which is the name of the month for the date '2022-03-10'.

 

NOW(): The NOW function in MySQL returns the current date and time. 

SELECT NOW();

This query will return the current date and time in the format 'YYYY-MM-DD HH:MM:SS'.

 

PERIOD_ADD(): The PERIOD_ADD function in MySQL adds a number of months to a period value in the format 'YYYYMM'. 

SELECT PERIOD_ADD(202203, 3);

This query will return 202206, which is the period value '202203' with 3 months added to it.

 

PERIOD_DIFF(): The PERIOD_DIFF function in MySQL returns the number of months between two period values in the format 'YYYYMM'. 

SELECT PERIOD_DIFF(202203, 202201);

This query will return 2, which is the number of months between the two period values '202203' and '202201'.

 

QUARTER(): The QUARTER function in MySQL returns the quarter of a given date. 

SELECT QUARTER('2022-03-10');

This query will return 1, which is the first quarter of the year for the date '2022-03-10'.

 

SYSDATE(): The SYSDATE function in MySQL returns the current date and time. 

SELECT SYSDATE();

This query will return the current date and time in the format 'YYYY-MM-DD HH:MM:SS'.

 

TIME(): The TIME function in MySQL extracts the time part of a given date and time.

SELECT TIME('2022-03-10 12:34:56');

This query will return the time part of the date and time '2022-03-10 12:34:56', which is '12:34:56'.

 

TIME_FORMAT(): The TIME_FORMAT function in MySQL formats a given time according to a specified format. 

SELECT TIME_FORMAT('12:34:56', '%h:%i %p');

This query will return '12:34 PM', which is the time '12:34:56' formatted according to the format string '%h:%i %p'.

 

TIMEDIFF(): The TIMEDIFF function in MySQL calculates the difference between two given times. 

SELECT TIMEDIFF('12:34:56', '10:23:45');

This query will return '02:11:11', which is the difference between the two times '12:34:56' and '10:23:45'.

 

TIMESTAMP(): The TIMESTAMP function in MySQL converts a given date and time to a timestamp value. 

SELECT TIMESTAMP('2022-03-10 12:34:56');

This query will return the timestamp value for the date and time '2022-03-10 12:34:56'.

 

TO_DAYS(): The TO_DAYS function in MySQL converts a given date to the number of days since year 0. 

SELECT TO_DAYS('2022-03-10');

This query will return the number of days since year 0 for the date '2022-03-10'.

 

WEEK(): The WEEK function in MySQL returns the week number for a given date. 

SELECT WEEK('2022-03-10');

This query will return the week number for the date '2022-03-10'.

 

WEEKDAY(): The WEEKDAY function in MySQL returns the weekday index for a given date (0 = Monday, 1 = Tuesday, ..., 6 = Sunday). 

SELECT WEEKDAY('2022-03-10');

This query will return 3, which is the weekday index for Thursday, March 10th, 2022.

 

WEEKOFYEAR(): The WEEKOFYEAR function in MySQL returns the week number for a given date. 

SELECT WEEKOFYEAR('2022-03-10');

This query will return the week number for the date '2022-03-10'.

 

YEAR(): The YEAR function in MySQL returns the year for a given date. 

SELECT YEAR('2022-03-10');

This query will return 2022, which is the year for the date '2022-03-10'.