MySQL providse a rich set of functions and syntaxes for handling date and time data, which is crucial for many applications. This guide covers common scenarios for querying, manipulating, and converting date and time values.

Querying Based on Date and Time Criteria

When your data is stored in a datetime type field, like publish\_time, you can filter records based on specific dates or times. - To query records for a specific date when the publish\_time might contain both date and time components (e.g., '2004-02-04 10:30:00'), use the DATE() function: WHERE DATE(publish_time) = '2004-02-04'

• Similarly, if you only need to match the time portion (e.g., '08:08:08'), you can use DATE() or TIME() depending on your exact needs. However, comparing directly to a time string after extracting it is common: ``` WHERE TIME(publish_time) = '08:08:08'

• To match an exact datetime value, you can use TIMESTAMP() or compare directly: ``` WHERE publish_time = '2004-02-04 08:08:08'

  
   or ```
  WHERE TIMESTAMP(publish_time) = '2004-02-04 08:08:08'
  

Common Date and Time Functions

MySQL offers several built-in functions to extract specific parts of a date or calculate time differences. - TO_DAYS(date): Returns the number of days since year 1. This is useful for calculating date differences. To find records with in the last 10 days: WHERE TO_DAYS(NOW()) - TO_DAYS(publish_time) <= 10

• DAYOFWEEK(date): Returns the weekday index, where 1 represents Sunday, 2 for Monday, ..., and 7 for Saturday.
• WEEKDAY(date): Returns the weekday endex, where 0 represents Sunday, 1 for Monday, ..., and 6 for Saturday.
• DAYOFMONTH(date): Returns the day of the month (1-31).
• DAYOFYEAR(date): Returns the day of the year (1-366).
• MONTH(date): Returns the month of the year (1-12).
• DAYNAME(date): Returns the full English name of the weekday.
• MONTHNAME(date): Returns the full English name of the month.
• QUARTER(date): Returns the quarter of the year (1-4).

Converting Integers to Dates

When dealing with Unix timestamps (often stored as integers), you can convert them into readable date and time formats. - Using FROM_UNIXTIME(unix_timestamp[, format_specifier]): SELECT DATE_FORMAT(FROM_UNIXTIME(pubdate), '%Y-%m-%d %H:%i:%s') FROM your_table;

 ```
SELECT FROM_UNIXTIME(pubtime, '%Y-%m-%d %H:%i:%s') FROM your_table;
```

Date Manipulation Functions

MySQL provides functions to add or subtract time intervals from existing dates. - ADDTIME(datetime_expr, time_interval): Adds a time interval to a datetime expression. The time interval can be in various formats, including seconds. SELECT ADDTIME(NOW(), '1:0:0'); -- Adds 1 hour to the current datetime

 ```
SELECT ADDTIME(NOW(), '0:0:30'); -- Adds 30 seconds to the current datetime
```

• DATE_ADD(date, INTERVAL expr unit): Adds a time interval to a date. This function strictly uses the INTERVAL keyword. ``` SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- Adds 1 day
  SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR); -- Adds 2 hours
  SELECT DATE_ADD(NOW(), INTERVAL 15 MINUTE); -- Adds 15 minutes
  SELECT DATE_ADD(NOW(), INTERVAL 30 SECOND); -- Adds 30 seconds
  SELECT DATE_ADD(NOW(), INTERVAL 1 MICROSECOND); -- Adds 1 microsecond
  SELECT DATE_ADD(NOW(), INTERVAL 1 WEEK); -- Adds 1 week
  SELECT DATE_ADD(NOW(), INTERVAL 3 MONTH); -- Adds 3 months
  SELECT DATE_ADD(NOW(), INTERVAL 2 QUARTER); -- Adds 2 quarters
  SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR); -- Adds 1 year

• ADDDATE(date, expr): This function has two forms. If the second argument is a numeric value, it's treated as days. If it's an INTERVAL expression, it behaves like DATE\_ADD. ``` SELECT ADDDATE(NOW(), 1); -- Adds 1 day
  SELECT ADDDATE(NOW(), INTERVAL 1 DAY); -- Adds 1 day
  SELECT ADDDATE(NOW(), INTERVAL 1 HOUR); -- Adds 1 hour
  SELECT ADDDATE(NOW(), INTERVAL 1 MINUTE); -- Adds 1 minute
  SELECT ADDDATE(NOW(), INTERVAL 1 SECOND); -- Adds 1 second
  SELECT ADDDATE(NOW(), INTERVAL 1 MICROSECOND); -- Adds 1 microsecond
  SELECT ADDDATE(NOW(), INTERVAL 1 WEEK); -- Adds 1 week
  SELECT ADDDATE(NOW(), INTERVAL 1 MONTH); -- Adds 1 month
  SELECT ADDDATE(NOW(), INTERVAL 1 QUARTER); -- Adds 1 quarter
  SELECT ADDDATE(NOW(), INTERVAL 1 YEAR); -- Adds 1 year

• SUBTIME(datetime_expr, time_interval): Subtracts a time interval from a datetime expression. ``` SELECT SUBTIME(NOW(), '0:0:1'); -- Subtracts 1 second

• DATE_SUB(date, INTERVAL expr unit): Subtracts a time interval from a date, using the INTERVAL keyword. ``` SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); -- Subtracts 1 day
  SELECT DATE_SUB(NOW(), INTERVAL 3 HOUR); -- Subtracts 3 hours
  SELECT DATE_SUB(NOW(), INTERVAL 20 MINUTE); -- Subtracts 20 minutes
  SELECT DATE_SUB(NOW(), INTERVAL 45 SECOND); -- Subtracts 45 seconds
  SELECT DATE_SUB(NOW(), INTERVAL 5 MICROSECOND); -- Subtracts 5 microseconds
  SELECT DATE_SUB(NOW(), INTERVAL 2 WEEK); -- Subtracts 2 weeks
  SELECT DATE_SUB(NOW(), INTERVAL 6 MONTH); -- Subtracts 6 months
  SELECT DATE_SUB(NOW(), INTERVAL 3 QUARTER); -- Subtracts 3 quarters
  SELECT DATE_SUB(NOW(), INTERVAL 2 YEAR); -- Subtracts 2 years

• SUBDATE(date, expr): Similar to ADDDATE, this function subtracts time. If the second argument is numeric, it's treated as days. If it's an INTERVAL expression, it behaves like DATE\_SUB. ``` SELECT SUBDATE(NOW(), 5); -- Subtracts 5 days
  SELECT SUBDATE(NOW(), INTERVAL 1 DAY); -- Subtracts 1 day
  SELECT SUBDATE(NOW(), INTERVAL 2 HOUR); -- Subtracts 2 hours
  SELECT SUBDATE(NOW(), INTERVAL 10 MINUTE); -- Subtracts 10 minutes
  SELECT SUBDATE(NOW(), INTERVAL 5 SECOND); -- Subtracts 5 seconds
  SELECT SUBDATE(NOW(), INTERVAL 2 MICROSECOND); -- Subtracts 2 microseconds
  SELECT SUBDATE(NOW(), INTERVAL 3 WEEK); -- Subtracts 3 weeks
  SELECT SUBDATE(NOW(), INTERVAL 4 MONTH); -- Subtracts 4 months
  SELECT SUBDATE(NOW(), INTERVAL 1 QUARTER); -- Subtracts 1 quarter
  SELECT SUBDATE(NOW(), INTERVAL 5 YEAR); -- Subtracts 5 years