MySQL Date & Time Functions

Working with date and time is a crucial aspect of database management, and MySQL offers a rich set of built-in functions to handle date and time data types efficiently. Whether you need to extract a specific part of the date, perform calculations, or format data in a particular way, MySQL has you covered. In this blog post, we’ll explore all the essential date and time functions in MySQL, along with their syntax and practical examples to make your database management smoother.

---

1. NOW() Function

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

Syntax:

SELECT NOW();

Example:

SELECT NOW();

Output:

2024-10-17 10:30:45

---

2. CURDATE() Function

The CURDATE() function returns the current date in the format YYYY-MM-DD.

Syntax:

SELECT CURDATE();

Example:

SELECT CURDATE();

Output:

2024-10-17

---

3. CURTIME() Function

The CURTIME() function returns the current time in the format HH:MM:SS.

Syntax:

SELECT CURTIME();

Example:

SELECT CURTIME();

Output:

10:30:45

---

4. DATE() Function

The DATE() function extracts the date part from a DATETIME or TIMESTAMP value.

Syntax:

SELECT DATE('2024-10-17 10:30:45');

Example:

SELECT DATE('2024-10-17 10:30:45');

Output:

2024-10-17

---

5. TIME() Function

The TIME() function extracts the time part from a DATETIME or TIMESTAMP value.

Syntax:

SELECT TIME('2024-10-17 10:30:45');

Example:

SELECT TIME('2024-10-17 10:30:45');

Output:

10:30:45

---

6. YEAR(), MONTH(), DAY() Functions

These functions extract the year, month, or day part from a date.

Syntax:

SELECT YEAR('2024-10-17'), MONTH('2024-10-17'), DAY('2024-10-17');

Example:

SELECT YEAR('2024-10-17'), MONTH('2024-10-17'), DAY('2024-10-17');

Output:

2024 | 10 | 17

---

7. HOUR(), MINUTE(), SECOND() Functions

These functions extract the hour, minute, or second part from a time or DATETIME.

Syntax:

SELECT HOUR('10:30:45'), MINUTE('10:30:45'), SECOND('10:30:45');

Example:

SELECT HOUR('10:30:45'), MINUTE('10:30:45'), SECOND('10:30:45');

Output:

10 | 30 | 45

---

8. DATE_FORMAT() Function

The DATE_FORMAT() function formats the date or time value based on the specified format string.

Syntax:

SELECT DATE_FORMAT(date, format);

Example:

SELECT DATE_FORMAT('2024-10-17', '%W, %M %d, %Y');

Output:

Thursday, October 17, 2024

---

9. STR_TO_DATE() Function

The STR_TO_DATE() function converts a string into a date using the specified format.

Syntax:

SELECT STR_TO_DATE(string, format);

Example:

SELECT STR_TO_DATE('17-10-2024', '%d-%m-%Y');

Output:

2024-10-17

---

10. DATE_ADD() Function

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

Syntax:

SELECT DATE_ADD(date, INTERVAL value unit);

Example:

SELECT DATE_ADD('2024-10-17', INTERVAL 10 DAY);

Output:

2024-10-27

---

11. DATE_SUB() Function

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

Syntax:

SELECT DATE_SUB(date, INTERVAL value unit);

Example:

SELECT DATE_SUB('2024-10-17', INTERVAL 5 DAY);

Output:

2024-10-12

---

12. DATEDIFF() Function

The DATEDIFF() function returns the difference in days between two dates.

Syntax:

SELECT DATEDIFF(date1, date2);

Example:

SELECT DATEDIFF('2024-10-17', '2024-10-10');

Output:

7

---

13. TIMEDIFF() Function

The TIMEDIFF() function returns the difference between two time values.

Syntax:

SELECT TIMEDIFF(time1, time2);

Example:

SELECT TIMEDIFF('10:30:45', '08:00:00');

Output:

02:30:45

---

14. LAST_DAY() Function

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

Syntax:

SELECT LAST_DAY(date);

Example:

SELECT LAST_DAY('2024-10-17');

Output:

2024-10-31

---

15. WEEKDAY() Function

The WEEKDAY() function returns the index of the weekday for a date (0 for Monday, 6 for Sunday).

Syntax:

SELECT WEEKDAY(date);

Example:

SELECT WEEKDAY('2024-10-17');

Output:

3

---

Conclusion

MySQL offers a powerful suite of date and time functions that make working with temporal data easy and efficient. Whether you need to extract specific parts of a date, perform calculations, or format it in a user-friendly way, these functions will help you manage your data seamlessly.

At ITxperts, we aim to simplify your learning process by providing clear, concise, and practical guides. We hope this article helps you master MySQL’s date and time functions!

Stay tuned for more tutorials and tips from ITxperts!