What are the different types of date and time functions in MySQL? — MySQL Interview Question #28

MySQL provides various date and time functions to manipulate and format date and time values. These functions can be used to perform various computations on date and time values, such as formatting date and time strings, extracting parts of date and time values, performing date and time arithmetic, and converting date and time values between different formats.

Some of the commonly used date and time functions are:

1. ‘NOW()‘: This function returns the current date and time in the format ‘YYYY-MM-DD HH:MM:SS‘.

Example:

SELECT NOW();

Output:
2021-11-30 10:15:30

2. ‘DATE()‘: This function extracts the date part from a date or datetime value.

Example:

SELECT DATE('2021-11-30 10:15:30');

Output:
2021-11-30

3. ‘TIME()‘: This function extracts the time part from a date or datetime value.

Example:

SELECT TIME('2021-11-30 10:15:30');

Output:
10:15:30

4. ‘YEAR()‘: This function extracts the year part from a date or datetime value.

Example:

SELECT YEAR('2021-11-30');

Output:
2021

5. ‘MONTH()‘: This function extracts the month part from a date or datetime value.

Example:

SELECT MONTH('2021-11-30');

Output:
11

6. ‘DAY()‘: This function extracts the day part from a date or datetime value.

Example:

SELECT DAY('2021-11-30');

Output:
30

7. ‘HOUR()‘: This function extracts the hour part from a time or datetime value.

Example:

SELECT HOUR('10:15:30');

Output:
10

8. ‘MINUTE()‘: This function extracts the minute part from a time or datetime value.

Example:

SELECT MINUTE('10:15:30');

Output:
15

9. ‘SECOND()‘: This function extracts the second part from a time or datetime value.

Example:

SELECT SECOND('10:15:30');

Output:
30

10. ‘DATEDIFF()‘: This function calculates the difference between two dates in days.

Example:

SELECT DATEDIFF('2021-11-30', '2021-11-25');

Output:
5

11. ‘DATE_ADD()‘: This function adds a specified interval to a date or datetime value.

Example:

SELECT DATE_ADD('2021-11-30', INTERVAL 1 MONTH);

Output:
2021-12-30

12. ‘DATE_SUB()‘: This function subtracts a specified interval from a date or datetime value.

Example:

SELECT DATE_SUB('2021-11-30', INTERVAL 1 MONTH);

Output:
2021-10-30

13. ‘STR_TO_DATE()‘: This function converts a string to a datetime value based on a specified format.

Example:

SELECT STR_TO_DATE('30/11/2021', '%d/%m/%Y');

Output:
2021-11-30

These are some of the commonly used date and time functions in MySQL. There are many more functions available for performing various date and time operations in MySQL.