Date and Time Functions

NOW():
- NOW() returns the current date and time.
Example:
```
sqlCopy code
SELECT NOW() AS current_datetime;
```
Output: The current date and time.
CURDATE():
- CURDATE() returns the current date without the time portion.
Example:
```
sqlCopy code
SELECT CURDATE() AS current_date;
```
Output: The current date.
CURTIME():
- CURTIME() returns the current time without the date portion.
Example:
```
sqlCopy code
SELECT CURTIME() AS current_time;
```
Output: The current time.
DATE_FORMAT():
- DATE_FORMAT() is used to format a date or time value according to a specified format.
Example:
```
sqlCopy code
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_datetime;
```
Output: The current date and time in a custom format.
DATE_ADD() and DATE_SUB():
- DATE_ADD() adds a specified time interval to a date, while DATE_SUB() subtracts it.
Example:
```
sqlCopy code
SELECT DATE_ADD(NOW(), INTERVAL 3 DAY) AS future_date, DATE_SUB(NOW(), INTERVAL 2 HOUR) AS past_time;
```
Output: The date and time three days from now, and two hours ago.
DATEDIFF():
- DATEDIFF() calculates the difference in days between two dates.
Example:
```
sqlCopy code
SELECT DATEDIFF('2023-09-30', '2023-09-20') AS date_difference;
```
Output: 10 days.
TIMESTAMPDIFF():
- TIMESTAMPDIFF() calculates the difference between two date or time values in a specified unit (e.g., seconds, minutes, hours).
Example:
```
sqlCopy code
SELECT TIMESTAMPDIFF(MINUTE, '2023-09-20 10:00:00', '2023-09-20 11:30:00') AS minutes_difference;
```
Output: 90 minutes.
DAY(), MONTH(), YEAR():
- These functions extract the day, month, or year part from a date.
Example:
```
sqlCopy code
SELECT DAY('2023-09-20') AS day, MONTH('2023-09-20') AS month, YEAR('2023-09-20') AS year;
```
Output: Day: 20, Month: 9, Year: 2023
STR_TO_DATE():
- STR_TO_DATE() converts a string into a date based on a specified format.
Example:
```
sqlCopy code
SELECT STR_TO_DATE('2023-09-20', '%Y-%m-%d') AS converted_date;
```
Output: The date 2023-09-20.
NOW() - INTERVAL x UNIT:
- You can subtract an interval (e.g., days, hours) from the current date and time using NOW() - INTERVAL.
Example:
```
sqlCopy code
SELECT NOW() - INTERVAL 3 DAY AS three_days_ago, NOW() - INTERVAL 2 HOUR AS two_hours_ago;
```
Output: The date and time three days ago and two hours ago.

These date and time functions in MySQL are essential for working with date and time values, performing date calculations, and formatting date and time data as needed in your SQL queries.