MySQL Date & Time

The MySQL can work with date and time data using various data types and functions. Here are some of the commonly used date and time data types in MySQL:

1. DATE: The DATE data type is used to store date values in the format ‘YYYY-MM-DD’. For example, ‘2023-01-15’.
2. TIME: The TIME data type is used to store time values in the format ‘HH:MM:SS’. For example, ’14:30:45′.
3. DATETIME: The DATETIME data type is used to store date and time values in the format ‘YYYY-MM-DD HH:MM:SS’. For example, ‘2023-01-15 14:30:45’.
4. TIMESTAMP: The TIMESTAMP data type is similar to DATETIME but is automatically updated when a row is inserted or updated. It is often used to record when a record was created or last modified.
5. YEAR: The YEAR data type is used to store 4-digit year values, such as ‘2023’.

Here are some common date and time functions in MySQL:

1. NOW(): Returns the current date and time.

   SELECT NOW();

2. CURDATE(): Returns the current date.

   SELECT CURDATE();

3. CURTIME(): Returns the current time.

   SELECT CURTIME();

4. DATE_FORMAT(): Formats a date or time value into a specific format. For example:

   SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

5. DATE_ADD(): Adds a specified interval to a date or datetime value.

   SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);

6. DATE_SUB(): Subtracts a specified interval from a date or datetime value.

   SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

7. DATEDIFF(): Calculates the number of days between two dates.

   SELECT DATEDIFF('2023-01-15', '2023-01-01');

8. TIMEDIFF(): Calculates the time difference between two time values.

   SELECT TIMEDIFF('14:30:45', '12:00:00');

These are just a few examples of date and time functions in MySQL. You can use these functions to manipulate and work with date and time data in your MySQL queries and applications.