MySQL : Working With Dates

Working with dates in MySQL involves various functions and operators to manipulate and extract information from date and time values. Here are some common tasks and corresponding MySQL functions:

1. Current Date and Time:

•    NOW(): Returns the current date and time.
•    CURDATE(): Returns the current date.
•    CURTIME(): Returns the current time.

2. Date Arithmetic:

•    DATE_ADD(date, INTERVAL value unit): Adds a specified time interval to a date.
•    DATE_SUB(date, INTERVAL value unit): Subtracts a specified time interval from a date.
•    Arithmetic operations directly on date columns.

3. Date Formatting:

•    DATE_FORMAT(date, format): Formats a date as per the specified format.

   
      Example:
    
      SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted_date;
      

4. Extracting Parts of a Date:

•    YEAR(date): Extracts the year from a date.
•    MONTH(date): Extracts the month from a date.
•    DAY(date): Extracts the day from a date.
•    HOUR(time): Extracts the hour from a time.
•    MINUTE(time): Extracts the minute from a time.
•    SECOND(time): Extracts the second from a time.

   
      Example:
     
      SELECT YEAR(date_column) AS year FROM table_name;
   

5. Date Comparison:

•    Comparison operators (=, <>, >, <, >=, <=) can be used to compare date values.
•    DATEDIFF(date1, date2): Returns the difference in days between two dates.
•    TIMESTAMPDIFF(unit, datetime1, datetime2): Returns the difference between two date or datetime expressions based on a specified unit (e.g., DAY, HOUR, MINUTE).

6. Date Conversion:

•    STR_TO_DATE(str, format): Converts a string into a date.
•    DATE_FORMAT(date, format): Formats a date into a string.

These functions allow you to perform various operations with dates and times in MySQL, such as calculating durations, formatting dates for display, extracting date components, and more. They are essential for handling date-related data effectively in your database queries.