MySQL provides a comprehensive set of date and time functions for manipulating and extracting information from date and time values. These functions are essential for various data manipulation tasks, including retrieving current dates and times, calculating date differences, formatting date and time values, and extracting specific components from date and time expressions.
Here’s a breakdown of some of the key MySQL date functions:
| Function | Description |
|---|---|
| NOW | Returns the current date and time. |
| DATE_FORMAT | Formats a date as specified by the format string. |
| DATEDIFF | Returns the difference in days between two dates. |
| CURDATE | Returns the current date. |
| ADDDATE | Adds a specified interval to a date. |
| ADDTIME | Adds a specified time to a datetime value. |
| CONVERT_TZ | Converts a datetime value from one time zone to another. |
| CURRENT_DATE | Returns the current date. |
| CURRENT_TIME | Returns the current time. |
| CURRENT_TIMESTAMP | Returns the current date and time in the YYYY-MM-DD HH:MM:SS format. |
| CURTIME | Returns the current time in the HH:MM:SS format. |
| DATE | Extracts the date part from a datetime or timestamp value. |
| DATE_ADD | Adds a time interval to a date. |
| DATE_SUB | Subtracts a time interval from a date. |
| DAY | Returns the day of the month for a given date. |
| EXTRACT | Extracts a specific part of a date (e.g., year, month, day). |
| DAYNAME | Returns the name of the day of the week for a given date. |
| HOUR | Returns the hour from a given time. |
| LAST_DAY | Returns the last day of the month for a given date. |
| MINUTE | Returns the minute of the hour for a datetime value. |
| MONTH | Returns the month of the year for a date. |
| SECOND | Returns the seconds component of a datetime value. |
| SYSDATE | Returns the current date and time as a timestamp. |
| TIME | Extracts the time portion from a datetime value. |
| TIMESTAMP | Creates a timestamp from a combination of date and time values. |
| WEEK | Retrieves the week number of the year for a date. |
| WEEKDAY | Returns the day of the week index for a date (0-6, Sunday-Saturday). |
| YEAR | Returns the year component of a date. |
MySQL’s DATE functions provide a powerful and versatile set of tools for working with dates and times in your database applications. These functions can be used to manipulate date values, extract specific date components, compare dates, perform date arithmetic, and format dates for display.